A look at OBSERVE's SQL-like log query engine: filtering, field extraction, and aggregation, with ready-to-use query examples that turn log spelunking into a single query.
The most common log-troubleshooting scenario is "I roughly know what happened, but I need to pick the key lines out of a pile of results." Full-text search can match keywords, but it can't combine conditions across fields or do aggregation. OBSERVE's query engine brings SQL-like syntax to logs, so ops and developers can use familiar WHERE and GROUP BY for both filtering and aggregation instead of learning yet another query language. The payoff is real: the same person who writes SQL for the business database can now write it against logs without a day of ramp-up.
When a log line arrives, the ingestion pipeline parses it against the configured format — JSON keys, key-value pairs, or a custom pattern — and materializes each parsed element as a typed column: level, service, host, trace_id, and a timestamp. This is what makes SQL meaningful; unparsed text can only be searched, not filtered by type or grouped. If a field you need isn't parsed yet, you can add an extraction rule without touching application code.
With fields in place, you can query directly:
SELECT * FROM logs
WHERE service = 'order-service'
AND level = 'ERROR'
AND timestamp > now() - interval '15 minute'Operators include =, !=, LIKE, IN, BETWEEN, and REGEXP, and multiple conditions combine with AND/OR. Field values are case-sensitive and string literals need single quotes. Time arithmetic such as now() - interval '15 minute' works throughout, so most queries can be copy-pasted between dashboards without editing hard-coded timestamps.
To find where errors concentrate across services and hosts, use GROUP BY:
SELECT service, count(*) AS cnt
FROM logs
WHERE level = 'ERROR'
AND timestamp > now() - interval '1 hour'
GROUP BY service
ORDER BY cnt DESC
LIMIT 10This is far faster than eyeballing hundreds of lines, and it is also the first step in alert deduplication: aggregate to find hotspots, then drill down into specific traces. The same pattern extends to percentiles — approx_percentile(duration_ms, 0.99) gives you a P99 over logs without shipping them to a metrics pipeline first.
Beyond filtering, the engine ships the usual SQL functions so you can reshape results without leaving the console. lower(service), substring(message, 1, 200), and date functions like date_trunc('hour', timestamp) all work. Derived columns are especially handy for bucketing: CASE WHEN duration_ms > 1000 THEN 'slow' ELSE 'ok' END AS bucket turns a raw number into a label you can group on directly. Combining these with aggregation means most one-off questions — "which endpoints have the most slow requests by hour" — can be answered in a single query rather than an export-and-pivot routine in a spreadsheet.
Keyword search still works — message LIKE '%timeout%' or plain full-text. What is more useful is extracting fields at query time, turning key values from semi-structured logs into filter conditions:
SELECT * FROM logs
WHERE service = 'payment-service'
AND message REGEXP 'card_no=[0-9]{4}'With automatic masking, sensitive fields are redacted in the results while searchability is unaffected, which matters when several teams share the same log store.
The engine uses a columnar layout under the hood, so filtering on a few fields over millions of lines stays fast; cost is dominated by the number of matched rows, not total table size. For very hot queries, add a time range even when you don't think you need one — a WHERE timestamp > now() - interval '1 day' guard prevents an accidental full scan from stalling the cluster.
Save your frequent queries as views or quick searches, grouped by "top error services", "top slow endpoints", and "all logs for a trace". New team members don't have to write SQL from scratch — one click on a template gets them to the latest anomaly. Add a HAVING cnt > 5 to GROUP BY results to filter out noise and keep only the hotspots worth looking at.