Query Logs Like SQL: The JJHub OBSERVE Log Search Engine

Why we still grep logs with regex

Most of the time ops teams spend on incident response goes into finding the right log lines. grep over big files, fiddling with regex, waiting for tail to scroll — these all break down when you have hundreds of machines and billions of log lines per day. The point of a log platform isn't "can we store it" but "can we find it fast and precisely".

JJHub OBSERVE's log search doesn't follow the usual keyword-plus-filter path. Instead it ships a SQL-like query language. You don't need to learn a vendor-specific DSL; if you know SQL you're already productive, and for anyone comfortable with MySQL or ClickHouse the learning curve is close to zero.

What the SQL-like syntax looks like

Take Nginx access logs, where each structured line has status, upstream_time, request_uri and so on. Counting the last hour by status code:

SELECT status, count(*) AS cnt
FROM nginx_access
WHERE time >= now() - interval '1 hour'
GROUP BY status
ORDER BY cnt DESC

Or the 20 slowest endpoints:

SELECT request_uri, max(upstream_time) AS slowest
FROM nginx_access
WHERE upstream_time > 2
GROUP BY request_uri
ORDER BY slowest DESC
LIMIT 20

The grammar covers SELECT, WHERE, GROUP BY, ORDER BY and LIMIT, plus count, sum, avg, max, min, percentile and other aggregates. To get the P99 of endpoint latency, just write percentile(upstream_time, 99) — no need to pull data locally and compute it yourself.

Subqueries and JOIN are available too. To correlate access logs with application logs across streams, join them on the request ID:

SELECT a.request_id, b.user_id, a.upstream_time
FROM nginx_access a
JOIN app_log b ON a.request_id = b.request_id
WHERE a.status >= 500
  AND a.time >= now() - interval '10 minute'

Where the fields come from

SQL queries need structured fields. OBSERVE ships a Pipeline parser at the collection layer supporting JSON, regex, delimiter and key-value parsing. For a Go service, one collection rule splits stdout JSON logs into separate fields:

input:
  tail:
    paths: ["/var/log/app/*.log"]
pipeline:
  - json:
      source: message
  - geoip:
      source: remote_ip

Parsed fields go into the inverted index while the raw message is preserved for context. Field types — string, number, ip, boolean — are inferred automatically: "200" becomes a number, "10.0.0.1" becomes an ip. That's what lets numeric fields participate in sorting and aggregation, and ip fields filter by subnet. If inference gets it wrong, you can override a field's type on the field management page.

Compared with grep and ES DSL

Engineers coming from grep notice two things at first: results are ordered by time descending rather than by file order, and queries have a timeout (30 seconds by default) — a bare LIKE '%x%' over billions of lines gets rejected outright. That isn't a restriction, it's protection: it forces you to think about the time range and keywords before you query.

If you're migrating from Elasticsearch's Query DSL, the cost is low too. The query panel switches between SQL mode and a visual builder with one click: click fields and conditions and it generates the SQL for you, and that generated SQL can be copied straight into an alert rule.

Notes on query performance

To keep SQL fast over billions of lines, the search layer does three things: time-partitioned columnar storage, per-field inverted indexes, and pushing query plans down to shards. In practice, aggregating an hour of logs by status returns with P95 latency under one second.

A few practical tips:

• Always put a time range in the WHERE clause to shrink the scan window.
• Avoid prefix wildcards like LIKE '%keyword%'; use inverted-index keyword matching instead.
• Keep aggregates within a single log stream, since cross-stream JOINs trigger heavier computation.
• Enable field index acceleration for high-frequency lookup fields like request_id and user_id. It costs a little write throughput and buys an order of magnitude faster queries.
• For dashboards that refresh on a fixed window, pin the time range in the saved query so it doesn't drift with the global time picker.

The same search engine is reused by the alerting and tracing modules, so the filter expressions you write in alert rules use the same grammar as search — one language, not two.