← Better StackCONTENT HISTORY
WHAT CHANGED
Update to Better Stack
Snapshot Nov 11, 2026 · 06:08 UTC · version 1.1.3
Collection source: downloaded plugin package.
First saved snapshot
No earlier snapshot is available to establish a change.
Save Better Stack and check future changes in Following.
Up to 3 plugins free · no card required.
Compare saved observations
Download comparison JSONFull technical diff · 0 changed fields
Full snapshot data
{
"description": "MUST load BEFORE writing any Better Stack spans/traces SQL query. Contains required syntax for span queries - without this, queries will fail.",
"included_files": [],
"name": "spans-query",
"skill_md_contents": "---\nname: spans-query\ndescription: MUST load BEFORE writing any Better Stack spans/traces SQL query. Contains required syntax for span queries - without this, queries will fail.\n---\n\n# Spans Query Building Guide\n\n## Where the query runs\n\nThese queries read raw events, so they run ad hoc through `query` and `render_chart`; dashboard charts only read the metrics collection (see `dashboard-builder` for log and span volumes there). `query` and `render_chart` take no template variables. First call `query_help` with the source ID: it names the source's collections and the table to pass. Put the collection where `{{source}}` stands (e.g. `remote(t123_my_app_logs)` for the last ~30 minutes, `s3Cluster(primary, t123_my_app_s3)` with its row-type filter for older rows), a bucket such as `toStartOfInterval(dt, INTERVAL 5 MINUTE)` where `{{time}}` stands, and concrete bounds such as `dt > now() - INTERVAL 3 HOUR` for `{{start_time}}` and `{{end_time}}`. Add a `LIMIT`, and call `query_windows` before an `s3Cluster(...)` query over more than a few hours.\n\n## Quick Reference\n\n| Need | Pattern |\n| ---------------- | ----------------------------------------------------------------------------------------------------------------------- |\n| Source | `FROM {{source}}` |\n| Time filter | `WHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}` |\n| Time bucket | `{{time}} AS time` |\n| Service filter | `<service expression> = 'service-name'` |\n| Group by service | `<service expression> AS series` |\n| Extract field | `JSONExtract(raw, 'span', 'field', 'Nullable(String)')` |\n| Trace ID | `JSONExtractString(raw, 'span', 'trace_id')` |\n| Span ID | `JSONExtractString(raw, 'span', 'span_id')` |\n| Duration (ns) | `JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64') - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')` |\n\n## Critical Rules\n\n### 1. Span Data is in `raw` Column (JSON)\n\nSpans follow OpenTelemetry format. All span fields are nested under `raw.span`, resource fields under `raw.resource`.\n\nEnum values are stored as lowercase short names, not the proto constants: `kind` is `server` / `client` / `internal` / `producer` / `consumer` (never `SPAN_KIND_SERVER`), `status.code` is `ok` / `error` / `unset`.\n\n```sql\n-- Span fields (under \"span\" key)\nJSONExtract(raw, 'span', 'trace_id', 'Nullable(String)') AS trace_id\nJSONExtract(raw, 'span', 'span_id', 'Nullable(String)') AS span_id\nJSONExtract(raw, 'span', 'parent_span_id', 'Nullable(String)') AS parent_span_id\nJSONExtract(raw, 'span', 'name', 'Nullable(String)') AS span_name\nJSONExtract(raw, 'span', 'kind', 'Nullable(String)') AS span_kind\nJSONExtract(raw, 'span', 'root', 'Nullable(Boolean)') AS is_root\n\n-- Duration (nanoseconds) - calculate from start/end\nJSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64') AS duration_ns\n\n-- Duration in milliseconds\n(JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')) / 1000000 AS duration_ms\n\n-- Status\nJSONExtract(raw, 'span', 'status', 'code', 'Nullable(String)') AS status_code\n```\n\n### 2. Span Attributes (under `span.attributes`)\n\n```sql\n-- HTTP status\nJSONExtract(raw, 'span', 'attributes', 'http.status_code', 'Nullable(Int64)') AS http_status\n-- Or newer convention:\nJSONExtract(raw, 'span', 'attributes', 'http.response.status_code', 'Nullable(Int64)') AS http_status\n\n-- gRPC status\nJSONExtract(raw, 'span', 'attributes', 'rpc.grpc.status_code', 'Nullable(Int64)') AS grpc_status\n\n-- Database system\nJSONExtractString(raw, 'span', 'attributes', 'db.system') AS db_type\n\n-- Server address/port\nJSONExtractString(raw, 'span', 'attributes', 'server.address') AS server_address\n```\n\n### 3. Resource Attributes (under `resource.attributes`)\n\n```sql\n-- Service name (try both)\nJSONExtractString(raw, 'resource', 'attributes', '_service') AS service\nJSONExtractString(raw, 'resource', 'attributes', 'service.name') AS service\n\n-- Host\nJSONExtractString(raw, 'resource', 'attributes', '_host') AS host\n\n-- Kubernetes\nJSONExtractString(raw, 'resource', 'attributes', 'k8s.namespace.name') AS namespace\nJSONExtractString(raw, 'resource', 'attributes', 'k8s.pod.name') AS pod\n```\n\n**Important:** For service filtering/grouping, use the **service name expression** returned by `source` verbatim; never guess service field paths.\n\n### 4. Use Template Variables\n\n- `{{source}}` - table name (never hardcode)\n- `{{time}}` - time bucket column (NOT `dt`)\n- `{{start_time}}`, `{{end_time}}` - time range bounds\n\nThere is no service template variable: filter or group by service with the service name expression from `source`.\n\n### 5. Error Detection\n\nA span is an error if ANY of these conditions is true:\n\n```sql\nJSONExtract(raw, 'span', 'status', 'code', 'Nullable(String)') = 'error'\nOR JSONExtract(raw, 'span', 'attributes', 'http.status_code', 'Nullable(Int64)') >= 400\nOR JSONExtract(raw, 'span', 'attributes', 'http.response.status_code', 'Nullable(Int64)') >= 400\nOR JSONExtract(raw, 'span', 'attributes', 'rpc.grpc.status_code', 'Nullable(Int64)') > 0\n```\n\n### 6. Standard Aggregations Only\n\nUse `COUNT`, `AVG`, `SUM` - NOT `avgMerge`, `countMerge` (those are for pre-aggregated metrics).\n\n### 7. Use Descriptive Column Aliases\n\nColumn aliases become chart legend labels. Use descriptive names.\n\n### 8. Point Lookups: Add a `raw LIKE` Prefilter\n\nWhen filtering on one specific ID (trace_id, span_id, session ID), add a substring prefilter before the extraction:\n\n```sql\nAND raw LIKE '%YOUR_TRACE_ID%'\nAND JSONExtractString(raw, 'span', 'trace_id') = 'YOUR_TRACE_ID'\n```\n\nThe cheap `LIKE` scan skips JSON-parsing rows that can't match; keep the equality as the authoritative filter. `LIKE` is case-sensitive — use the ID verbatim.\n\n## Examples\n\n**Span count over time:**\n\n```sql\nSELECT {{time}} AS time, COUNT(*) AS \"Span count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time\nORDER BY time\n```\n\n**Span count by service** - `series` uses the service name expression from `source`:\n\n```sql\nSELECT {{time}} AS time, JSONExtractCaseInsensitive(raw, 'service', 'Nullable(String)') AS series, COUNT(*) AS \"Span count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time, series\nORDER BY time\n```\n\n**Error rate (root spans only):**\n\n```sql\nSELECT\n {{time}} AS time,\n countIf(\n JSONExtract(raw, 'span', 'root', 'Nullable(Boolean)') = true AND (\n JSONExtract(raw, 'span', 'status', 'code', 'Nullable(String)') = 'error'\n OR JSONExtract(raw, 'span', 'attributes', 'http.status_code', 'Nullable(Int64)') >= 400\n OR JSONExtract(raw, 'span', 'attributes', 'http.response.status_code', 'Nullable(Int64)') >= 400\n )\n ) / countIf(JSONExtract(raw, 'span', 'root', 'Nullable(Boolean)') = true) AS \"Error rate\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time\nORDER BY time\n```\n\n**Average duration by span name (top 10 slowest):**\n\n```sql\nSELECT\n JSONExtract(raw, 'span', 'name', 'Nullable(String)') AS span_name,\n AVG(\n (JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')) / 1000000\n ) AS \"Avg duration (ms)\",\n COUNT(*) AS \"Count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY span_name\nORDER BY \"Avg duration (ms)\" DESC\nLIMIT 10\n```\n\n**P95 latency over time (root spans):**\n\n```sql\nSELECT\n {{time}} AS time,\n quantile(0.95)(\n (JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')) / 1000000\n ) AS \"P95 latency (ms)\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\n AND JSONExtract(raw, 'span', 'root', 'Nullable(Boolean)') = true\nGROUP BY time\nORDER BY time\n```\n\n**Find all spans for a trace:**\n\n```sql\nSELECT\n JSONExtract(raw, 'span', 'name', 'Nullable(String)') AS span_name,\n JSONExtract(raw, 'span', 'span_id', 'Nullable(String)') AS span_id,\n JSONExtract(raw, 'span', 'parent_span_id', 'Nullable(String)') AS parent_span_id,\n (JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')) / 1000000 AS duration_ms,\n JSONExtract(raw, 'span', 'status', 'code', 'Nullable(String)') AS status\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\n AND raw LIKE '%YOUR_TRACE_ID%'\n AND JSONExtractString(raw, 'span', 'trace_id') = 'YOUR_TRACE_ID'\nORDER BY dt, _insert_index\n```\n\n**Slow database queries:**\n\n```sql\nSELECT\n JSONExtract(raw, 'span', 'name', 'Nullable(String)') AS operation,\n JSONExtractString(raw, 'span', 'attributes', 'db.system') AS db_type,\n (JSONExtract(raw, 'span', 'end_time_unix_nano', 'UInt64')\n - JSONExtract(raw, 'span', 'start_time_unix_nano', 'UInt64')) / 1000000 AS duration_ms\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\n AND JSONExtractString(raw, 'span', 'attributes', 'db.system') != ''\nORDER BY duration_ms DESC\nLIMIT 20\n```\n\n## Common Mistakes\n\n| Mistake | Fix |\n| --------------------------------------- | ----------------------------------------------------------------- |\n| `JSONExtract(raw, 'trace_id', ...)` | `JSONExtract(raw, 'span', 'trace_id', ...)` |\n| `JSONExtract(raw, 'service.name', ...)` | `JSONExtract(raw, 'resource', 'attributes', 'service.name', ...)` |\n| `{{_service}}` or a guessed field | Use the service name expression from `source` |\n| `duration` column | Calculate: `end_time_unix_nano - start_time_unix_nano` |\n| `WHERE dt BETWEEN` | `WHERE {{time}} BETWEEN ...` |\n| `'String'` type | `'Nullable(String)'` - prevents failures on missing fields |\n| ID equality without a prefilter | Add `raw LIKE '%<id>%'` before the extraction (see rule 8) |\n| `avgMerge(...)` on spans | `AVG(...)` - spans are not pre-aggregated |\n| `SELECT *` for raw span data | Select specific columns: `dt, _insert_index, raw` |\n| `kind = 'SPAN_KIND_SERVER'` | Lowercase short names: `kind = 'server'`, `status.code = 'error'` |\n"
}SHA-256 of public snapshot: 0e51cabac2ed63b4b54d0f4a21e0a07a88709bd19e7c805f032ce044580b565c