Query Logs Like a Database with SQL

Why keyword search is not enough

When something breaks in production, the first reflex is usually 'let me grep it'. Keyword search, however, hits three walls. First, you cannot filter by field: if you want requests with status >= 500 that took longer than 2 seconds, keywords only match the few words that happen to appear in the line, so you get both false negatives and false positives. Second, you cannot aggregate: asking how 5xx errors are distributed by service or endpoint over the past hour is simply not answerable with keyword matching. Third, multi-condition queries force you into brittle regular expressions or endless manual paging — slow and easy to get wrong. The real cost shows up under pressure: during an outage, an engineer is hand-crafting regexes while the error rate keeps climbing, and a query that matches an unrelated line sends the whole team down a false trail.

Logs are actually structured. Timestamp, level, service name, trace_id, status code, latency — these fields already sit in every line; they just have not been split apart. Split them, and logs become queryable like a database table. Your troubleshooting mindset shifts from 'find one line' to 'write one query'.

Field extraction: turning logs into tables

Jujing OBSERVE does two things at ingestion. It parses common formats — JSON, Nginx, Spring Boot default layouts — and extracts fields automatically. For non-standard formats, it supports custom Grok or regex parse rules. Here is a parse example for an Nginx access log:

log_format main '$remote_addr [$time_local] $request $status $body_bytes_sent $request_time';

After ingestion, each line produces columns such as remote_addr, status, request_time and request. Field types are inferred too: status becomes a number, request_time a float, time_local a timestamp — which is what makes aggregation and range filters meaningful. Without numeric types, a filter like HAVING avg_rt > 2 would compare strings and silently return nonsense. JSON logs are naturally key-value, so they need almost no configuration. For a legacy system's custom text format, one Grok rule is enough — for example, extracting the timestamp inside square brackets as time_local — and that rule can be written once and reused across every service that shares the format.

Real query scenarios

Scenario one: finding slow endpoints. Aggregate average latency per endpoint and keep only the ones above a threshold:

SELECT request, avg(request_time) AS avg_rt, count(*) AS cnt
FROM nginx_access
WHERE time_local >= now() - interval 1 hour
GROUP BY request
HAVING avg_rt > 2
ORDER BY avg_rt DESC
LIMIT 20

Scenario two: tracing one incident end to end. When logs carry a trace_id, you can drill straight into the distributed trace:

SELECT * FROM app_log
WHERE trace_id = 'a1b2c3d4e5f6'
ORDER BY time_local ASC

Scenario three: spotting services whose error rate just spiked over the last ten minutes:

SELECT service, count(*) FILTER (WHERE level = 'ERROR') AS err_cnt
FROM app_log
WHERE time_local >= now() - interval 10 minute
GROUP BY service
ORDER BY err_cnt DESC

From query to alert

A saved SQL query does not have to stay passive. Attach a schedule and a threshold to it and it becomes an alert. Run the 'slow endpoints' query every five minutes and page when any endpoint's average latency crosses 2 seconds; or run the 'errors by service' query every minute and alert when a service exceeds 20 errors in the window. The value is precision: instead of a generic '5xx rate high' alarm, the alert can name the exact endpoint and the exact count, so the on-call engineer opens the alert already knowing where to look.

Two mechanics make this reliable. First, schedule the query over a fixed, rolling window (say the last five minutes) so results are comparable from run to run. Second, treat missing data as its own signal — a query that suddenly returns zero rows can mean the system is down, not that everything is healthy. Wire both the threshold breach and the zero-result case into the alert condition.

Practical advice

First, settle on a log format from day one: emit JSON with fixed field names so you never have to rewrite parse rules later. Second, always persist latency, status code, and trace_id — these three fields are the entry points for most troubleshooting queries. Third, save your common queries so the whole team reuses them instead of rewriting SQL every time. Fourth, always add a time-range filter to avoid full scans that slow down search. Fifth, build a small shared query library — 'slow endpoints', 'errors by service', 'single trace' — and link those queries from your dashboards and alert notifications, so whoever gets paged lands directly on the relevant query instead of starting from scratch.

SQL search does not replace keyword search; it covers the parts keyword search cannot reach: field-level filtering, aggregation, and correlation. Once you start querying logs with SQL, there is no going back to plain grep.