SQL-Style Log Search: Troubleshooting in a Syntax You Already Know

When something breaks, what you usually want isn't to "look at logs"—it's to pull out the handful of lines that actually matter. Traditional log platforms each invent their own query syntax: some use Lucene, others a custom DSL, and you relearn it every time you switch tools. Jujing OBSERVE's search uses SQL-style syntax, which drops the learning curve for ops and developers to nearly zero.

Why SQL-style search

SQL is the most universal language engineers share, so there are no new keywords to memorize. Its WHERE, GROUP BY, and ORDER BY map naturally onto log analysis: filter, aggregate, and sort in one pass. More importantly, field types are explicit—numeric fields compare as numbers, timestamp fields support range queries—rather than everything being coerced to a string. That is what lets the query engine hit an index instead of doing a full scan every time.

This matters more than it first appears. In a string-everything engine, a query like latency_ms > 1000 either fails or silently compares text. In a typed engine it runs as a numeric range, which is both correct and fast. When you're mid-incident at 3 a.m., "write it the way you'd write a query against a database" removes a whole class of mistakes.

Common query examples

Filter by service and time to see only error logs:

SELECT * FROM logs
WHERE service = 'order-service'
  AND level = 'ERROR'
  AND timestamp BETWEEN '2025-08-01 00:00:00' AND '2025-08-01 23:59:59'
ORDER BY timestamp DESC
LIMIT 100;

Compute per-service error rates over the last hour:

SELECT service,
       count_if(level = 'ERROR') AS err_cnt,
       count(*) AS total,
       round(count_if(level = 'ERROR') * 100.0 / count(*), 2) AS err_pct
FROM logs
WHERE timestamp >= now() - interval '1 hour'
GROUP BY service
HAVING count_if(level = 'ERROR') > 0
ORDER BY err_cnt DESC;

Find slow requests and grab their trace_id:

SELECT trace_id, request_path, latency_ms, status_code
FROM logs
WHERE latency_ms > 1000
  AND timestamp >= now() - interval '30 minute'
ORDER BY latency_ms DESC;

That last one is a common incident pattern: pull the slowest requests, then click a trace_id to follow the full distributed trace and see which downstream service actually ate the time.

Field extraction and indexing

Fast SQL-style search depends on structured fields, not raw message text. Extract request_path, status_code, latency_ms, and trace_id explicitly on the collection side using regex or JSON parsing instead of burying them in the message and doing full-text matching afterward. For semi-structured logs, declare field types in your configuration: numeric and boolean fields use a range index in addition to the inverted index, which is what lets range queries stay on the index.

This is a configuration decision worth getting right early. Retrofitting field extraction after months of unstructured logs means reindexing history, and the cost of that scales with retention. Agree on the core field set with your developers before you cut over live traffic.

Here's what a collection-side parse rule looks like for a typical nginx access line: define a regex with named groups, map status to int and request_time to float, and drop the raw message if you don't need it. Once declared, a query like request_time > 1.0 becomes a genuine numeric range scan against the range index, rather than a string comparison that has to read every row. The difference shows up most on high-volume services, where a string scan can take seconds and an indexed range returns in milliseconds.

Performance tips

Three rules that cover most cases. First, always include a time condition to shrink the scan window. Second, avoid wrapping fields in functions inside WHERE (like to_lower(service)), which defeats the index. Third, use pre-aggregation or materialized views for high-frequency queries instead of rescanning raw logs live. If a single query returns more than 10,000 rows, your filter is probably too loose—tighten the constraints before you drill down.

One more pattern that pays off: save the queries you actually run during incidents as named views. Next time an alert fires, the on-call engineer pulls the saved view, swaps in the time window, and has the relevant log set in seconds—no reconstructing the query from memory at 3 a.m. Keep a short, shared list per service (top errors, slow requests, recent deploys) and it doubles as onboarding documentation for new team members.