SQL console

Use the SQL console to ask your logs, traces and metrics anything in SQL. It is read-only, takes parameters, can chart a result, saves statements and lists your slowest queries. The same page also runs KQL, and Analyze pivots your data without writing a query.

Open the SQL console#

Open SQL in the app. You need the member role or higher. Lower roles see "SQL needs the member role".

The page has a SQL / KQL switch, an editor, a schema browser (use Find a table or column, click a name to insert it), the time range picker and a results area. Before your first run, the results area offers examples under Start from an example. Slow queries opens the list of your slowest queries.

1. Write a statement, or pick one from Samples.
2. Choose a time range with the picker. The default is the last hour.
3. Click Run or press Ctrl/Cmd+Enter.

The results show as a Table. Select a cell to see its full value, and copy it.

The rules#

• Read-only. One statement that reads your own data. Writes and schema changes are rejected.
• Always time-bounded. The picker's range applies to every run: each table holds only that range, even when the statement does not mention it. Filtering with {from:DateTime64(3)} and {to:DateTime64(3)} on each table's time column (below) gives the same answer and reads less. Name tables as obs.logs, obs.spans and so on; a bare logs is refused with that hint.
• No SETTINGS or FORMAT clauses. Results always come back as columns and rows.
• Up to 20,000 characters per statement and 30 parameters besides from and to.
• Range limit: the range can reach back as far as your workspace's data retention, not further.
• Row limit: the page shows the first 10,000 rows and says so when it truncated the result. Narrow the statement, or use Export for everything (see Export).
• Internal columns and internal attribute keys are never returned.
• A statement that reads too much data is stopped ("The statement reads too much data"): narrow the range, filter earlier or select fewer columns. A statement that runs too long is stopped too. Too many statements at once for one workspace answer "Retry shortly".

Tables you can query#

The schema browser shows every table's columns and its time column. SeverityNumber follows OpenTelemetry: 17 and above is error or fatal.

Columns use the OpenTelemetry names above, not the short names from Loki or the Explore language: Timestamp rather than ts or time, SeverityText rather than level, Body rather than message, ServiceName rather than service. A statement with an unknown column or function says which one and the nearest real one, for example "Unknown column ts in obs.logs. Did you mean Timestamp?", with the line and column when it can be placed.

Cookbook#

Each example below is checked against a real database by a test, so the syntax is right. Press Run, then change the names to your own.

1. Filter and sort#

The newest error events of one service. {service:String} becomes an input box under the editor.

SELECT Timestamp, ServiceName, SeverityText, Body
FROM obs.logs
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
  AND TimestampTime >= toDateTime({from:DateTime64(3)}) AND TimestampTime <= toDateTime({to:DateTime64(3)})
  AND ServiceName = {service:String}
  AND SeverityNumber >= 17
ORDER BY Timestamp DESC
LIMIT 100

2. Read an attribute#

LogAttributes['key'] reads one attribute, and mapContains keeps events that have it. A missing key reads as an empty string.

SELECT Timestamp, ServiceName, LogAttributes[{key:String}] AS value, Body
FROM obs.logs
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
  AND TimestampTime >= toDateTime({from:DateTime64(3)}) AND TimestampTime <= toDateTime({to:DateTime64(3)})
  AND mapContains(LogAttributes, {key:String})
ORDER BY Timestamp DESC
LIMIT 100

3. Error rate by service#

SELECT ServiceName,
  count() AS events,
  countIf(SeverityNumber >= 17) AS errors,
  round(100 * errors / events, 2) AS error_percent
FROM obs.logs
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
  AND TimestampTime >= toDateTime({from:DateTime64(3)}) AND TimestampTime <= toDateTime({to:DateTime64(3)})
GROUP BY ServiceName
ORDER BY error_percent DESC
LIMIT 50

4. Slow spans and latency percentiles#

Duration is in nanoseconds, so divide by 1e6 for milliseconds.

SELECT ServiceName, SpanName, count() AS calls,
  countIf(StatusCode = 'Error') AS errors,
  quantiles(0.5, 0.95, 0.99)(Duration) / 1e6 AS p50_p95_p99_ms
FROM obs.spans
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
GROUP BY ServiceName, SpanName
ORDER BY calls DESC
LIMIT 50

5. Join logs and spans on the trace#

Logs and spans share TraceId and SpanId, so you can see the error logs of the slowest requests next to their spans. Filter each table on its own time column.

SELECT l.Timestamp, l.ServiceName, l.Body, s.SpanName, toFloat64(s.Duration) / 1e6 AS span_ms, l.TraceId
FROM obs.logs AS l
INNER JOIN obs.spans AS s ON l.TraceId = s.TraceId AND l.SpanId = s.SpanId
WHERE l.Timestamp >= {from:DateTime64(3)} AND l.Timestamp < {to:DateTime64(3)}
  AND l.TimestampTime >= toDateTime({from:DateTime64(3)}) AND l.TimestampTime <= toDateTime({to:DateTime64(3)})
  AND s.Timestamp >= {from:DateTime64(3)} AND s.Timestamp < {to:DateTime64(3)}
  AND l.SeverityNumber >= 17
  AND l.TraceId != ''
ORDER BY span_ms DESC
LIMIT 100

Logs only carry a TraceId when your app sends it. See Correlate logs and traces.

6. Traces with an error#

SELECT TraceId, min(Timestamp) AS started, count() AS spans, countIf(StatusCode = 'Error') AS errors
FROM obs.spans
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
GROUP BY TraceId
HAVING errors > 0
ORDER BY started DESC
LIMIT 100

7. A metric over time#

Metric times have whole-second precision, so filter on TimeUnix with toDateTime(...).

SELECT toStartOfMinute(TimeUnix) AS minute, avg(Value) AS value
FROM obs.metrics_gauge
WHERE TimeUnix >= toDateTime({from:DateTime64(3)}) AND TimeUnix < toDateTime({to:DateTime64(3)})
  AND MetricName = {metric:String}
GROUP BY minute
ORDER BY minute

For counters, use the Metrics page: it handles resets and delta or cumulative values, and an alert computes the same number. A hand-written sum(Value) over a cumulative counter adds up running totals.

Use parameters#

Write other values as {name:Type} placeholders, for example ServiceName = {service:String}. Each one becomes an input box under the editor, so you never need to quote the value.

• Names use lowercase letters, digits and _ (up to 64 characters). from and to are reserved for the time range.
• At most 30 parameters besides the time range; each text value is at most 4,096 characters.
• Numeric and Bool types are sent as numbers and booleans.

Chart a result#

Click Chart above the results. A result can be charted when it has a time column (a DateTime) and at least one numeric column; otherwise the button is disabled. Choose Line or bar, and add a text column to split the series (up to 6 series, 12 groups).

SELECT toStartOfInterval(Timestamp, INTERVAL 5 MINUTE) AS bucket, ServiceName, countIf(SeverityNumber >= 17) AS errors
FROM obs.logs
WHERE Timestamp >= {from:DateTime64(3)} AND Timestamp < {to:DateTime64(3)}
  AND TimestampTime >= toDateTime({from:DateTime64(3)}) AND TimestampTime <= toDateTime({to:DateTime64(3)})
GROUP BY bucket, ServiceName
ORDER BY bucket

The time range is bound with {from:DateTime64(3)} and {to:DateTime64(3)}, so the same statement follows whatever range you pick, here and on a dashboard.

Add a statement to a dashboard#

Click Add to dashboard, then choose an existing dashboard or a new one. The panel keeps the statement and its parameter values and runs for the dashboard's time range. If the last result could be charted, the panel is a time series; otherwise it is a table. Edit the statement later in the panel's SQL query. See Dashboards.

Save a statement#

1. Click Save, enter a Name (up to 120 characters) and choose Who can see it: Only me or Everyone in the workspace.
2. Click Save.

A saved statement keeps the SQL and its parameter values, not the time range. Open Saved to load or delete one. Under Recent in this browser you also see your last statements; they stay in your browser only. Saved statements remember the language, so a KQL query reopens as KQL.

Find slow queries#

Click Slow queries on the SQL page. It lists the queries of your workspace that took at least the time you choose (any time, 100 ms up to 10 s) in the last hour, 24 hours, 7 days or 30 days, newest first. Queries are kept for 30 days.

• Each row shows when it finished, how long it took, the language (SQL or KQL), what kind of data it read, rows and bytes read, rows returned and memory used. A failed query says why (not valid, read too much, took too long).
• Show: Queries you wrote lists your SQL and KQL from the SQL page, Explore, dashboard SQL panels and the MCP run_sql tool, with the query text. Open loads it into the editor.
• Also those Logthread runs for you adds the queries Logthread builds for Explore, Traces, Metrics, dashboards and alerts, labelled by the data they read, never the text.
• To speed a query up: narrow the time range, filter earlier, select fewer columns, or summarise before you sort.

You need the member role or higher.

Who can do what#

Each run is recorded in the audit log as a short fingerprint of the statement, not its text.

Next: pivot the same data without a query in Analyze, then put it on a dashboard.