Metrics Query Syntax

The Query metrics endpoint accepts a subset of the Kusto Query Language (KQL). This page covers everything Spotflow supports.

The metrics table holds every metric in the workspace, so the same syntax queries system metrics and custom metrics.

Query Structure

A query reads the metrics table, narrows it with where operators, and reduces it to one or more time series with a single summarize operator:

metrics
| where <filter>
| summarize <aggregation>(value) by bin(timestamp, <interval>), <grouping columns>

bin(timestamp, <interval>) accepts only the following intervals: 1m, 5m, 10m, 30m, 1h, 2h, 3h, 6h, 1d, 7d, 14d. Any other interval is rejected by the backend.

Any columns listed after bin(...) as <grouping columns> split the result into one series per unique combination of their values.

For example, this query returns the average unlock duration per mechanism, in hourly buckets:

metrics
| where metric_name == 'unlock_duration_ms'
| summarize avg(value) by bin(timestamp, 1h), mechanism

Time Range

The query itself carries no time filter. Instead, the range comes from the from and to fields of the request body:

• The range must not exceed 90 days.
• It's widened to whole buckets on both ends, so a query from 08:30 with a 1h bin starts at 08:00.

Columns

Columns to filter and group by:

• metric_name — the name of the metric.
• device_id — the ID of the device that reported the metric.
• transport_route — the route the message traveled through, for example a mesh gateway.
• Any custom label by referencing it directly e.g., custom_label_name == 'value'.

Aggregations

The summarize operator takes exactly one aggregation function, and its only argument is always value:

• sum(value)
• avg(value)
• min(value)
• max(value)
• count(value)

Filters

Comparison Operators

==, !=, <, <=, >, >= compare strings and numbers. =~ and !~ compare strings while ignoring case.

Logical Operators

and, or, and the not() function combine conditions. Parentheses group them.

String Operators

startswith, endswith, contains, and their negations !startswith, !endswith, and !contains ignore case. Append _cs for the case-sensitive variants, for example startswith_cs or !contains_cs.

matches regex tests the value against a regular expression.

List Operators

in and !in test membership in a list of literals. in~ and !in~ are their case-insensitive counterparts.

metrics
| where metric_name == 'unlock_duration_ms'
| where device_id in ('device-1', 'device-2', 'device-3')
| summarize avg(value) by bin(timestamp, 1h), device_id

Functions

Because labels are always strings, functions are mostly there to reshape them before comparison.

Each of these functions takes a fixed number of arguments. Nest calls to go beyond that limit, for example strcat(strcat(a, b), c).

toint, todouble, and toreal return null when the text is not a number, which makes coalesce useful for supplying a fallback:

metrics
| where metric_name == 'unlock_duration_ms'
| where coalesce(toint(hardware_revision), 0) >= 3
| summarize avg(value) by bin(timestamp, 1h)

Arrays

transport_route is the only array column. Index it with transport_route[0] for the first element or transport_route[-1] for the last one. array_first and array_last are shortcuts for the same thing.