Query Logs Like a Database: OBSERVE SQL Search, from Basics to Tuning

Why run SQL over logs

Keyword search finds things—it locates the log lines that contain a given term. But most questions in troubleshooting and operations are not "is it there" but "how many, how distributed, what trend": what share of today's requests are 5xx errors? Which endpoint has the highest P99 latency? Why did traffic drop by half between 2 and 3 a.m. yesterday? No single log line answers these; the answers live in aggregates over millions of lines. Exporting them and crunching numbers in a separate script is slow and strains storage. With SQL in the search box, one statement returns both raw lines and statistics.

OBSERVE parses common fields at ingest time—timestamp, level, service name, trace_id, HTTP status, latency, source IP—into structured fields. The search box supports a standard SQL subset, so you can filter, group, aggregate, and sort over log data directly. The practical difference from a generic search bar is that every query can have two halves: an aggregate half that answers "what happened," and a detail half that answers "which lines prove it." You rarely want one without the other.

Basic syntax: raw lines and stats in one query

The syntax follows standard SQL; the clauses you'll use most are SELECT, WHERE, GROUP BY, ORDER BY, and LIMIT. The time range is controlled by the picker in the top-right corner and can also be set explicitly. Three examples:

-- Top 10 services by error count in the last hour
SELECT service, count(*) AS err_count
FROM log
WHERE level = 'ERROR'
GROUP BY service
ORDER BY err_count DESC
LIMIT 10;

-- Share of each HTTP status code, as a percentage
SELECT status, count(*) * 100.0 / sum(count(*)) OVER () AS pct
FROM log
WHERE status >= 400
GROUP BY status;

-- Top 5 endpoints by P99 latency
SELECT api, approx_percentile(duration_ms, 0.99) AS p99
FROM log
WHERE duration_ms IS NOT NULL
GROUP BY api
ORDER BY p99 DESC
LIMIT 5;

Besides count, sum, avg, min, and max, the engine provides approximate functions such as approx_percentile and approx_distinct for high-cardinality workloads—bounded error, an order of magnitude faster. For a percentile query over millions of rows, the exact variant is often impractical; the approximate version returns in well under a second while keeping error below 1% at typical cardinalities. Keep the exact functions for small, well-filtered result sets.

One nuance worth internalizing: the time window is a first-class part of the query, not an afterthought. Flip the picker from "last 1 hour" to "last 24 hours" and the same statement silently rescans that window. This is exactly why the tuning rules below matter.

Copy-paste query templates

Slow endpoint hunting: aggregate average and P99 latency per endpoint to spot the lagging service. Error-rate spikes: count errors per minute, watch the time-series chart for the inflection point, then cross-reference your deploy history. Per-user analysis: group by source IP or user ID to tell an isolated user problem from a global outage. Trace correlation: group logs carrying a trace_id to reconstruct one request's full call chain. One more that pays off during on-call: group by version or image tag to see whether a release correlates with the anomaly.

These templates share one pattern—aggregate first, then drill down. Narrow the scope with aggregates, then fetch raw lines with those filters (a specific endpoint, a time window) to avoid full scans. The result set you drill into is usually a few hundred lines, not millions.

Performance and best practices

SQL search is flexible, but poorly written queries are slow. Five rules of thumb: first, filter on fields instead of full-text wildcards—status >= 400 is far faster than message LIKE '%error%' because structured fields use indexes. Second, keep the time range tight; for aggregate queries, the window size determines scan volume. Third, add LIMIT to aggregations so you never pull hundreds of thousands of rows at once. Fourth, route large cross-day or full-dataset aggregations through pre-aggregated metrics instead of recomputing every time. Fifth, lean on sampling: for share- and trend-type questions, a 10% sample is usually enough to see the answer and runs ten times faster.

Finally, once a query keeps proving useful, stop typing it by hand. Save it as a saved search, pin it to a dashboard, or attach it to an alert rule so the same aggregation runs on a schedule and pages you when the number crosses a threshold. Turning logs from searchable into computable—and then into automatically monitored—is the single biggest efficiency gain a logging platform can deliver.