Query Logs with SQL: Inside the OBSERVE Unified Query Engine

Why SQL Instead of grep

For most operators, log troubleshooting starts with grep -E "pattern" app.log, followed by tail -f, awk '{print $NF}', and sort | uniq -c. That toolbox served us well when logs lived on a single host in a handful of files. The moment logs are centralized and spread across dozens of indexes and hundreds of services, grep starts to buckle: cross-service correlation becomes guesswork, aggregations get painfully slow, and filtering a structured field means hand-writing a regex half the team can't read a week later. It gets worse in shops where metrics live in Prometheus, logs in Elasticsearch, and traces in Jaeger — three query languages that don't talk to each other, so a cross-service incident means flipping between three consoles.

The OBSERVE unified query engine collapses the query interfaces for logs, metrics, and traces into a single SQL-like syntax. You don't learn three DSLs. One SELECT ... FROM logs WHERE ... covers filtering, aggregation, and sorting, and every result row can drill straight into the raw log lines that produced it. The syntax extends standard SQL with time-window functions, so anyone who knows basic SQL is productive in ten minutes — no dedicated query-language specialist required.

Common Queries Worth Keeping

Find the upstream services returning the most 502s in the last five minutes:

SELECT upstream, COUNT(*) AS cnt
FROM logs
WHERE status = 502 AND time > now() - 5m
GROUP BY upstream
ORDER BY cnt DESC
LIMIT 10

Per-minute QPS and P99 latency for a single service:

SELECT time_bucket(1m, ts) AS bucket,
       COUNT(*) AS qps,
       quantile(0.99, latency_ms) AS p99
FROM logs
WHERE service = 'order-api'
GROUP BY bucket
ORDER BY bucket

Compute per-service error rate over 15 minutes and surface the first one that breaks:

SELECT service,
       SUM(CASE WHEN status >= 500 THEN 1 ELSE 0 END) / COUNT(*) AS err_rate
FROM logs
WHERE time > now() - 15m
GROUP BY service
HAVING err_rate > 0.01
ORDER BY err_rate DESC

Use a window function to rank slow-request volume per service over time:

SELECT service, time_bucket(1m, ts) AS bucket,
       RANK() OVER (PARTITION BY service ORDER BY COUNT(*) DESC) AS rk
FROM logs
WHERE latency_ms > 500 AND time > now() - 1h
GROUP BY service, bucket

Full-text search hides a classic trap: WHERE message LIKE '%timeout%' silently degrades into a full scan. Write WHERE message MATCH 'timeout' instead, which hits the inverted index and runs one to two orders of magnitude faster on a multi-million-row index. The same discipline applies to numbers — WHERE latency_ms > 500 uses the numeric index, while WHERE latency_ms > '500' silently falls back to a string comparison.

How Logs, Metrics, and Traces Connect

The trace_id carried through trace data is the join key that links the three pillars. A typical incident flow becomes: pull the full call chain for a slow trace, find the span that took the longest, run WHERE trace_id = 'xxx' to fetch every log line that request produced across services, then overlay the metric curves for the same window to see whether CPU, GC, or connection pools spiked at the same moment. All three moves happen in one dialect, in one query panel. That's the real payoff of a unified query: you skip the "guess which pillar the problem lives in" step and read all three signals in a single statement.

Why the Engine Is Fast

A unified query is not just syntactic sugar layered on three engines. Under the hood, logs use columnar storage plus an inverted index, metrics use pre-aggregation and downsampling, and traces use a graph index. A single SQL statement is parsed, split, and routed to the matching storage, then the results are merged. So SELECT COUNT(*) FROM logs WHERE status=502 reads index statistics instead of scanning entire pages of logs. That's why it holds up under interactive querying on large indexes, rather than degrading into grep-style linear scans.

Performance Tuning That Actually Matters

• Index the fields you filter on most, and keep WHERE clauses on indexed columns. Read the query plan first; if it flags a full scan, rephrase before it runs.
• High-cardinality fields are the enemy of GROUP BY. Grouping by user_id over a day of traffic will blow up memory; bound it with a time window or a LIMIT first. The engine auto-truncates over-limit aggregations and tells you it did.
• Enforce limits globally. Set query timeouts and result-row caps at the cluster level so one bad query from one engineer can't freeze the query node for everyone else. A 30-second timeout and a 10,000-row cap are sensible defaults.
• Prefer time_bucket over GROUP BY ts; the latter produces a huge number of tiny groups and runs far slower.

A Complete Debugging Walkthrough

A user reports that "orders occasionally stall for three seconds." The error-rate query shows nothing unusual, but the per-service P99 chart shows order-api spiking to 3s at a few moments. Extracting the trace_ids of slow requests with WHERE service='order-api' AND latency_ms > 2000, then WHERE trace_id IN (...) to pull the full call chains, reveals a synchronous balance-check SQL inside the payment callback running a full table scan. The entire investigation happened without switching pages or query languages once — about ten minutes from alert to root cause.

One last tip: save your recurring incident queries as favorites or dashboard panels, so next time you click instead of writing SQL from scratch.