SQL-Style Log Search: Query Logs Like a Database

SQL-style log search is the feature our users keep coming back to, and for good reason. Most log platforms stop at keyword matching: you can find lines that contain a string, but the moment you need to answer "aggregate 5xx errors by service over the last hour" or "which service is failing the most," you're clicking through pages and pasting numbers into a spreadsheet. OBSERVE takes a different approach — it treats your logs as a queryable table where fields are columns, and it supports SELECT, WHERE, GROUP BY, ORDER BY, and HAVING natively. Ops and developers never have to learn a proprietary query language.

Why SQL instead of plain search

Keyword search answers "does this string exist?" but almost every real troubleshooting question is an aggregation question: which service errors the most, what the status-code distribution looks like, which requests timed out. Those questions map naturally onto SQL — a skill your team already has. There's no new DSL to learn and no special operators to memorize. If you're coming from a Lucene/KQL-based tool, the mental shift is small: status:500 AND service:orders just becomes WHERE status = 500 AND service = 'orders'. The real difference shows up when you need to aggregate or sort — SQL gives you GROUP BY, ORDER BY, and HAVING in one pass, where the Lucene approach usually forces you to export and process the results externally.

Common queries that come up daily

Count errors per service over the last hour:

SELECT service, count(*) AS err_cnt
FROM logs
WHERE level = 'ERROR' AND time >= now() - 1h
GROUP BY service
ORDER BY err_cnt DESC

Find slow requests across all services:

SELECT request_id, latency_ms, status, service
FROM logs
WHERE latency_ms > 3000
ORDER BY latency_ms DESC
LIMIT 50

Status-code distribution for the last day:

SELECT status, count(*) AS cnt
FROM logs
WHERE time >= now() - 1d
GROUP BY status
ORDER BY cnt DESC

You never pre-define indexes or mappings. The system infers field types from the data and indexes them automatically. time, level, and service are built-in fields; your custom fields are flattened straight out of the JSON log lines, so user_id or trace_id is queryable the moment it appears.

Field types and built-in fields

Type inference is automatic: integer-looking values become int64, timestamps become time, quoted strings become string, and nested JSON objects can be flattened on demand. The built-in fields you can always rely on are time (used for partition pruning), level, service (set by the collector or SDK), and message (the raw line, always present). If your logs carry a trace_id, OBSERVE links them to the matching trace automatically — a slow-request query can jump straight to the full request timeline without a second search.

Three tips that make queries faster

1. Prefer regex over substring match. message =~ /timeout|refused/ hits the inverted index and is an order of magnitude faster than message LIKE '%timeout%'. When triaging, use regex to narrow the range first, then LIKE for exact matches.
2. Filter aggregates with HAVING. GROUP BY service HAVING count(*) > 100 surfaces the noisy services directly, so you skip the export-and-re-filter step.
3. Save frequent queries as views. Write a query once, then reference it from alerts and dashboards. Your whole team shares one definition instead of five slightly different ones.

From query to alert, in one step

Any SQL result can be saved as an alert in a couple of clicks. Take the first example and set a threshold: notify on-call when a service logs more than 50 ERRORs in five minutes. Because queries, visualizations, and alerts share the same SQL, what you see while debugging is exactly what fired the alert — no more "the alert fired but I can't reproduce it," and no drift between your dashboard and your pager.

Performance is where the design pays off. We use time-based partition pruning and columnar storage: a query with a time range only scans the relevant partitions, and a single-column query like SELECT service reads only that column. Aggregations run engine-side rather than in the browser, so large GROUP BY results stream back instead of freezing the UI. Query cost scales with the columns you select, not with total volume — pulling one service's ERROR lines for the last hour from a terabyte store reads only a small slice and returns in well under a second.