Query Logs with SQL: Troubleshoot Like Querying a Database

Why Log Search Needs SQL

Keyword search works fine while log volume is small. Once you're ingesting billions of lines a day across hundreds of hosts, the cracks show: you can't aggregate, you can't filter by field, and you can't join across fields. A request like "group the last five minutes of 502 errors by upstream service and return the top 10" is nearly impossible with pure full-text search — you end up writing brittle regexes or exporting data to another tool for a second pass.

JJH OBSERVE turns log search into SQL. Under the hood it's columnar storage with a vectorized execution engine. Log fields — time, level, trace_id, pod, status code — are typed first, then queried with SQL. Ops engineers don't need to learn a new DSL; if you can write a database query, you can search logs. And because it's real SQL semantics, the same query you run interactively can be saved as an alert condition or a dashboard panel without rewriting it.

Common Query Patterns

A few high-frequency scenarios, with the SQL that solves them.

Aggregate errors by status code:

SELECT status, count(*) AS cnt
FROM logs
WHERE status >= 500
  AND time >= now() - interval '5 minute'
GROUP BY status
ORDER BY cnt DESC

Find slow requests by upstream service:

SELECT service, p95(latency) AS p95
FROM logs
WHERE time >= now() - interval '1 hour'
GROUP BY service
HAVING p95 > 500

Correlate with a trace:

SELECT trace_id, span_id, message
FROM logs
WHERE trace_id = 'e4f2...'
ORDER BY time

Join access logs against application logs on a shared field:

SELECT a.request_id, a.status, b.message
FROM access_logs a
LEFT JOIN app_logs b
  ON a.request_id = b.request_id
WHERE a.status >= 500
  AND a.time >= now() - interval '10 minute'

These run directly in the console and can be saved as alert conditions that execute on a schedule. The join example is the one full-text search simply cannot do — correlating two different log streams is exactly where a relational model pays off.

SQL vs. Full-Text Search

Full-text search shines when you don't know the field name and just want to grep a keyword. SQL shines when you know what you need to compute. In a real incident they work as a pair, not as rivals:

• Narrow the scope with a keyword first (search for "timeout" across the last hour)
• Then aggregate, sort, and join with SQL to find the root-cause dimension
• Save the recurring SQL as a view so the next on-call engineer reuses it instead of starting from scratch

A concrete counter-example: "which endpoints have P99 above one second?" With pure keywords you'd pull every matching line and compute locally — slow, and wrong once you hit volume. One SQL statement returns it directly. The reverse also holds: if you don't even know what the fields look like, a quick full-text search first, then SQL, is the smoother path.

The Query Engine Under the Hood

A few implementation details worth knowing, because they shape what you can write. The engine is columnar, so queries that touch a few columns across many rows are cheap, while SELECT * on wide rows is expensive. It supports standard SQL — SELECT, WHERE, GROUP BY, HAVING, ORDER BY, joins, window functions, and a set of time-series helpers like p50/p95/p99, rate, and approx_distinct. Subqueries and CTEs work too.

Log fields are typed at ingest. The platform auto-detects integers, floats, booleans, IP addresses, and timestamps, and you can override a field's type or add an alias from the schema settings. Untyped fields are stored as strings and can't be used in numeric predicates — if a comparison looks wrong, check whether the field is actually typed as a number, not a string. That single check resolves a surprising share of "my query returns nothing" tickets.

Performance and Indexing Tips

SQL is only fast if fields are typed and indexed. A few habits that keep things quick:

1. Mark high-frequency filter fields — time, service, trace_id, status — as indexed columns
2. Always constrain the time field in WHERE; it's the cheapest way to shrink scan range
3. Avoid unbounded fuzzy LIKE on high-cardinality fields like trace_id
4. Prefer approximate functions (approx_distinct) for large aggregations where a few percent of error is acceptable
5. Split very large queries by time shard so no single scan touches too many partitions
6. Push computation down: filter early, aggregate late, and don't SELECT * on wide rows

Follow these and second-level aggregation over a billion log lines is reliably achievable. The main trap we see in the field is people treating the log store like a text archive and then wondering why a cross-field join crawls — type the fields, index the filters, and the same data becomes fast.