← 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 logs or web-events SQL query. Contains required syntax for raw-event queries - without this, queries will fail. Compatible with Explore. Also load when the user asks how raw-event queries look.",
"included_files": [],
"name": "logs-query",
"skill_md_contents": "---\nname: logs-query\ndescription: MUST load BEFORE writing any Better Stack logs or web-events SQL query. Contains required syntax for raw-event queries - without this, queries will fail. Compatible with Explore. Also load when the user asks how raw-event queries look.\n---\n\n# Logs 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 | `JSONExtractCaseInsensitive(raw, 'field', 'Nullable(String)')` |\n| Nested field | `JSONExtractCaseInsensitive(raw, 'parent', 'child', 'Nullable(String)')` |\n| Keyword search | `positionCaseInsensitive(field, 'keyword') > 0` |\n\n## Critical Rules\n\n### 1. Log Data is in `raw` Column (JSON)\n\n```sql\n-- Always use JSONExtractCaseInsensitive with Nullable types\nJSONExtractCaseInsensitive(raw, 'level', 'Nullable(String)') AS log_level\nJSONExtractCaseInsensitive(raw, 'metadata', 'user_id', 'Nullable(Int32)') AS user_id\n\n-- Fields with dots in name: use single string (NOT separate args)\nJSONExtractCaseInsensitive(raw, 'field.with.dots', 'Nullable(String)')\n```\n\nList of fields coming from `source_fields` lists fields as which are nested in `raw` - always access using `JSONExtractCaseInsensitive(raw, 'nginx', 'agent', 'Nullable(String)')`, not `SELECT nginx.agent FROM {{source}}`\n\n### 2. 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\n### Service filtering and grouping\n\n`source` returns the source's **service name expression** (a `JSONExtractCaseInsensitive(...)` or `COALESCE(...)` snippet) - copy it verbatim into SELECT / WHERE / GROUP BY. A service listed there with its own `field:` is extracted from that path instead; never guess service field paths.\n\n### 3. Standard Aggregations Only\n\nUse `COUNT`, `AVG`, `SUM` - NOT `avgMerge`, `countMerge` (those are for pre-aggregated metrics).\n\n### 4. Use Descriptive Column Aliases\n\nColumn aliases become chart legend labels. Use descriptive names instead of generic `value`.\n\n```sql\n-- BAD: Legend shows \"value\"\nSELECT {{time}} AS time, COUNT(*) AS value FROM ...\n\n-- GOOD: Legend shows \"Error count\"\nSELECT {{time}} AS time, COUNT(*) AS \"Error count\" FROM ...\n```\n\n### 5. Point Lookups: Add a `raw LIKE` Prefilter\n\nWhen filtering on one specific ID (session ID, trace_id, request ID, user ID), add a substring prefilter before the extraction:\n\n```sql\nAND raw LIKE '%YOUR_SESSION_ID%'\nAND JSONExtractCaseInsensitive(raw, 'session_id', 'Nullable(String)') = 'YOUR_SESSION_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**Time-series (single):**\n\n```sql\nSELECT {{time}} AS time, COUNT(*) AS \"Log count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time\nORDER BY time\n```\n\n**Time-series (multi-series 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 \"Log count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time, series\nORDER BY time\n```\n\n**Time-series (multi-series by level):**\n\n```sql\nSELECT\n {{time}} AS time,\n JSONExtractCaseInsensitive(raw, 'level', 'Nullable(String)') AS series,\n COUNT(*) AS \"Log count\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time, series\nORDER BY time\n```\n\n**Error rate:**\n\n```sql\nSELECT\n {{time}} AS time,\n countIf(JSONExtractCaseInsensitive(raw, 'level', 'Nullable(String)') = 'ERROR') / COUNT(*) AS \"Error rate\"\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\nGROUP BY time\nORDER BY time\n```\n\n**Keyword search:**\n\n```sql\nSELECT {{time}} AS time, JSONExtractCaseInsensitive(raw, 'message', 'Nullable(String)') AS message\nFROM {{source}}\nWHERE {{time}} BETWEEN {{start_time}} AND {{end_time}}\n AND positionCaseInsensitive(JSONExtractCaseInsensitive(raw, 'message', 'Nullable(String)'), 'timeout') > 0\nORDER BY time DESC\nLIMIT 100\n```\n\n**Map (geographic by IP) — for `map_chart`:** wrap the source's IP field (or any city/country/airport string) in `location()`, group by it, no `time` column — load the `map-hierarchy-charts` skill for the query shape and settings.\n\n## Common Mistakes\n\n| Mistake | Fix |\n| ------------------------------- | ---------------------------------------------------------- |\n| `{{_service}}` | Removed - use the expression from `source` |\n| Guessed service field path | Copy the service name expression from `source` |\n| `WHERE dt BETWEEN` | `WHERE {{time}} BETWEEN ...` |\n| `raw.level` or just `level` | `JSONExtractCaseInsensitive(raw, 'level', ...)` |\n| `'String'` type | `'Nullable(String)'` |\n| `'parent.child'` for nested | `'parent', 'child'` as separate arguments |\n| `avgMerge(...)` on logs | `AVG(...)` - logs are not pre-aggregated |\n| `AS value` for chart queries | Use descriptive alias: `AS \"Log count\"` |\n| ID equality without a prefilter | Add `raw LIKE '%<id>%'` before the extraction (see rule 5) |\n\n## Web Events (browser/RUM)\n\nWeb events are browser events from the Better Stack JS tag on Errors sources (Applications): page views/navigation, web vitals, and user/session context. They are stored and queried exactly like logs — raw JSON rows in the `raw` column with the same template variables.\n\n- `query_help` names the application's web-events collection; `source_fields` lists their fields. Don't guess field paths.\n- Event payload is nested JSON, e.g. the page URL sits at `payload.meta.url` (path at `payload.meta.path`): `JSONExtractCaseInsensitive(raw, 'payload', 'meta', 'path', 'Nullable(String)')`.\n\n## Data volume check\n\nTo check the volume of rows by type (`log`, `span`, `web_event`, `exception`) without any `WHERE` filtering, always use a metrics query instead of scanning raw events — load the `metrics-query` skill and use its per-row-type counts (`sum(logs_count)`, `sum(spans_count)`, `sum(web_events_count)`, `sum(exceptions_count)`).\n"
}SHA-256 of public snapshot: dddf6ab975de32e69b1728c8e0cb65527715f2387596f5ab8455bfbd3d92b7d3