Widget query reference

Every ClickHouse-backed widget runs its SQL through the same pipeline, and the server classifies the result into one of three shapes. The widget type you pick determines which shapes it can draw — matching the two is what this page is for.

Result shape	Your query returned…	How to produce it
Time series	A time column plus at least one numeric column	Bucket with `$__time(timestamp) AS ts`, then `GROUP BY ts ORDER BY ts`. Any column named `ts`, `timestamp`, `time`, `start_time`, `last_seen` or `window_start` counts as the time column.
Single value	Exactly one row with a numeric column and no time column	A bare aggregate: `SELECT avg(value) FROM …`
Table	Anything else — multiple rows without a time column	Group by label columns instead of time: `GROUP BY host`.

Two details do a lot of work in time-series results:

String columns split series. Each distinct combination of the non-numeric, non-time columns becomes its own line/band/row, named from those values. A legend template like {{host}} controls the display name.
Several numeric columns become several series. SELECT ts, avg(value) AS avg_cpu, max(value) AS peak_cpu … draws two series, named after the columns.

An empty result renders the honest “No data in the selected time range” state, whatever the widget type. To debug the shape your query actually returns, run it in the Query Workbench — the status row names the response type.

The question	Reach for
“What is the value right now?”	Stat, Gauge
“How is it trending over time?”	Line, Area, Bar
“Which items are the biggest / worst?”	Top list, Table
“How is the total split up?”	Pie
“Where are the hot spots across many series?”	Heatmap
“Is it up, and how reliably?”	Heartbeat
“Where do users drop out of a flow?”	Funnel, RUM Funnel
“What are the exact values?”	Table, Time-series table

Trend widgets

Line chart

For: continuous signals where the shape of the curve matters — latency, CPU, throughput. The default choice for comparing a handful of series against each other.

Query shape: a time series — time bucket + numeric value, with an optional label column to draw one line per label value. Scalar or table results are not drawn (the editor suggests Stat/Gauge instead).

SELECT $__time(timestamp), labels['host'] AS host, avg(value) AS cpu
FROM metrics
WHERE metric_name = 'system_cpu_usage_total'
GROUP BY ts, host
ORDER BY ts

Area chart

For: volumes and composition — the filled area makes “how much, in total, and made of what” readable at a glance (requests by service, bytes by interface). Prefer Line when series cross each other a lot.

Query shape: same as Line — time bucket + numeric value (+ optional label column, one band per value). Use sum(…) aggregations so stacking adds up meaningfully.

SELECT $__time(timestamp), labels['service'] AS service, sum(value) AS requests
FROM metrics
WHERE metric_name = 'http_requests_total'
GROUP BY ts, service
ORDER BY ts

To show a rate instead of a per-bucket total, divide by the bucket size: sum(value) / $__bucket_seconds AS rps.

Bar chart

For: discrete, countable events per interval — errors, deployments, log volume. Bars emphasize “how many in this bucket” where a line would imply continuity.

Query shape: a time series, typically count() per bucket. This example runs against the ClickHouse Logs source:

SELECT $__time(timestamp), level, count() AS entries
FROM logs
WHERE level IN ('ERROR', 'WARN')
GROUP BY ts, level
ORDER BY ts

Single-value widgets

Stat

For: the one number someone checks first — current error rate, active hosts, p95 latency. Feed it a time series and it adds a sparkline plus a trend arrow (second half of the window vs the first); thresholds color the number.

Query shape: best: a single-series time series — the newest bucket becomes the big number, the whole series the sparkline:

SELECT $__time(timestamp), avg(value) AS cpu
FROM metrics
WHERE metric_name = 'system_cpu_usage_total'
GROUP BY ts
ORDER BY ts

A bare aggregate also works (no sparkline or trend):

SELECT round(avg(value), 1) AS cpu
FROM metrics
WHERE metric_name = 'system_cpu_usage_total'

Gauge

For: a value against a known capacity or budget — disk fill, SLO error budget, pool utilization. Only meaningful when “full” is defined: set the widget’s Y-axis max as the gauge maximum (default 100) and configure thresholds to color the arc.

Query shape: a single value — a bare aggregate, the last point of a time series, or the first numeric column of a one-row result.

SELECT avg(value) AS used_percent
FROM metrics
WHERE metric_name = 'system_memory_usage_percent'

Heartbeat

For: up/down status of one thing — a service, endpoint, or agent. Shows current status plus the uptime percentage over the dashboard window: a sample >= 1 counts as up, below 1 as down. Status reads Healthy, Degraded (up now, but with failures in the window), or Down.

Query shape: a time series of 0/1-style samples (or a single value). min(value) per bucket is the strict reading — any down sample marks the whole bucket down:

SELECT $__time(timestamp), min(value) AS up
FROM metrics
WHERE metric_name = 'service_up'
GROUP BY ts
ORDER BY ts

Ranking and breakdown widgets

Top list

For: the biggest and worst offenders — top hosts by CPU, slowest endpoints, chattiest services. Ranked horizontal bars beat a pie the moment you care about order. Shows the top N (default 20, raise Max items up to 500; a footer tells you when results were truncated).

Query shape: label column + numeric column, no time column — rows become ranked entries. (A time series also works: each series is ranked by its latest value.)

SELECT labels['host'] AS host, avg(value) AS cpu
FROM metrics
WHERE metric_name = 'system_cpu_usage_total'
GROUP BY host
ORDER BY cpu DESC
LIMIT 50

Pie chart

For: share-of-total where the parts are few — traffic by region, errors by type. Keep it to roughly eight slices; past that, use a Top list.

Query shape: label column + numeric column (each row a slice). A time series also works — each series contributes its latest value.

SELECT labels['region'] AS region, sum(value) AS requests
FROM metrics
WHERE metric_name = 'http_requests_total'
GROUP BY region
ORDER BY requests DESC
LIMIT 8

Table

For: exact values across several dimensions — inventory-style views, per-host/per-process breakdowns, anything people will read row by row. The columns you SELECT become the table columns, in order; numeric cells honour the widget’s unit and decimal settings, and the page size is configurable (5–100 rows per page).

Query shape: any result renders, but the natural fit is grouped rows without a time column:

SELECT labels['host'] AS host, labels['exe'] AS process,
       round(avg(value), 1) AS avg_cpu
FROM metrics
WHERE metric_name = 'process_cpu_time'
GROUP BY host, process
ORDER BY avg_cpu DESC
LIMIT 100

Time-series table

For: auditing the actual bucket values behind a chart — one row per time bucket, one column per series, sortable. Use it next to a Line chart when people ask “what was the exact number at 14:05?”.

Query shape: a time series only — same query you would give a Line chart. This example runs against the ClickHouse Traces source:

SELECT $__time(start_time), service_name,
       round(quantile(0.95)(duration_us) / 1000, 1) AS p95_ms
FROM spans
WHERE service_name != ''
GROUP BY ts, service_name
ORDER BY ts

Pattern widgets

Heatmap

For: spotting hot spots across many series at once — error density per service over time, load per host across a fleet. Where ten lines become spaghetti, a heatmap stays readable: each series is a row, each time bucket a column, and the value drives the cell color (scaled green → red across the result’s min/max).

Query shape: a multi-series time series — time bucket + one label column + numeric value:

SELECT $__time(start_time), service_name, count() AS errors
FROM spans
WHERE has_error = true
GROUP BY ts, service_name
ORDER BY ts

Funnel

For: staged flows where each step loses some volume — signup steps, checkout stages, pipeline phases. Renders stage-over-stage conversion and drop-off percentages.

Query shape: one series per step — time bucket + a step label column + a numeric count (GROUP BY ts, step):

SELECT $__time(timestamp), labels['stage'] AS stage, sum(value) AS users
FROM metrics
WHERE metric_name = 'checkout_stage_total'
GROUP BY ts, stage
ORDER BY ts

For funnels over real user journeys (page A → page B → conversion), prefer the RUM Funnel widget below — it computes the funnel from RUM sessions directly, no SQL required.

Widgets that do not take SQL

The remaining widget types own their data (or need none) — you configure them in the widget panel instead of writing a query.

Widget	You configure	It shows
RUM Funnel	A RUM application + funnel steps	User conversion through page flows, from RUM sessions
RUM User Flow	A RUM application	Navigation paths users actually take
RUM Health	A RUM application	Core Web Vitals and error overview
Active Alerts	Alert rules to watch (+ active-only toggle)	Currently firing alerts for those rules
MTTR / MTTA / SLA Compliance / Alerts by Severity / Alerts by Source	Nothing — organization-wide	Alert response statistics
Markdown	The text itself	Notes, runbook links, board documentation
Iframe	A URL	Embedded external content (status pages, external panels)

RUM widgets follow the dashboard’s time range; see Real User Monitoring for instrumenting an application.

The designer offers each SQL widget type where it makes sense:

Widget	Metrics	Logs	Traces
Line	Yes	Yes	Yes
Area	Yes	—	—
Bar	Yes	Yes	Yes
Pie	Yes	Yes	—
Stat	Yes	Yes	Yes
Gauge	Yes	—	—
Top list	Yes	Yes	Yes
Heatmap	Yes	—	Yes
Heartbeat	Yes	—	—
Table	Yes	—	—
Time-series table	Yes	Yes	Yes
Funnel	Yes	—	—

Every example above follows the widget-query conventions: a metric_name filter on the metrics table, the WHERE at the top level, labels['…'] map access, $__time(…) for bucketing — and no time or tenant predicates, because the platform injects both. The full rules, macros and the Query Workbench are covered in Writing widget queries.