Query Logs with SQL: Treat Your Logs Like a Database

Why query logs with SQL

Ops and developers already know SQL. Grep is fine for browsing raw text, but the moment you need to count 5xx errors per service over the last hour, or find every endpoint whose latency exceeded two seconds, a grep-and-awk pipeline grows long and brittle. OBSERVE parses logs into structured fields and lets you query them like a table: filtering, aggregation, sorting, and subqueries all work, with a near-zero learning curve.

The difference shows up in real incidents. With grep you search for a pattern and eyeball the results; with SQL you ask a question — "which service had the biggest error-rate jump in the last fifteen minutes?" — and get a ranked answer in one statement, which you can save and share with the whole team.

Queries you can paste

-- 5xx count per service, last hour
SELECT service, count(*) AS cnt
FROM logs
WHERE status >= 500 AND __time__ > now() - interval '1' hour
GROUP BY service
ORDER BY cnt DESC;

-- endpoints with latency over 2 seconds
SELECT path, max(latency) AS slowest
FROM logs
WHERE latency IS NOT NULL
GROUP BY path
HAVING max(latency) > 2000;

-- top 5 services by error rate, last 24 hours
SELECT service,
       count_if(status >= 500) AS errors,
       count(*) AS total,
       count_if(status >= 500) * 1.0 / count(*) AS err_rate
FROM logs
WHERE __time__ > now() - interval '24' hour
GROUP BY service
HAVING count(*) > 1000
ORDER BY err_rate DESC
LIMIT 5;

Field names follow the parsing rules on the ingestion side. Define status, latency, and path in your Pipeline and the queries reference them directly — no separate schema registry to keep in sync.

Designing fields and indexes

Structured fields are the prerequisite. For nginx logs, use grok to extract remote_addr, status, latency, and path, then write them in. Declare types explicitly: latency as a number and status as an integer — otherwise everything is treated as a string and sorting and comparison silently break. This is the single most common mistake we see, and it shows up as "queries run, but the ordering is wrong."

A few rules of thumb on the query side:

• Index high-frequency fields to cut scan volume;
• Filter first, then aggregate — write a full WHERE clause instead of aggregating and filtering afterwards;
• Always bound large queries with a time range to avoid a full-table scan that stalls the cluster.

How this works in incident response

A typical triage flow has three steps: narrow the window with a time range and a keyword, correlate upstream and downstream services by trace_id or request_id, then locate the anomaly through aggregation. Each step can be saved as a reusable query template, so the on-call engineer pulls up a saved template instead of writing it from scratch at 3 a.m. For multi-service debugging, JOIN trace and log data in a single query and reconstruct the whole call chain with one statement.

Compared with a bespoke query language

Some log platforms ship a proprietary DSL that is hard to remember and worthless the day you change tools. SQL is a portable skill — everyone on the team can write it, and a new hire needs no training. You can use it for a simple keyword filter or a full analysis with subqueries and window functions; one engine covers everything from incident triage to weekly reports.