Joins and calculations

Arithmetic between queries

A ratio (an error rate, retransmits over segments, spool used over spool capacity) is the most common thing a single-selector query cannot express. Two queries, or a query and a number, combine with + - * /:

agent_pps_current | avg by (agent_id) / agent_pps_ceiling | max by (agent_id)

* and / bind tighter than + and -, both are left-associative, and parentheses group explicitly, exactly as arithmetic normally works:

agent_pps_current | avg by (agent_id) / agent_pps_ceiling | max by (agent_id) * 100

reads as (current / ceiling) * 100, the average probe rate as a percentage of the maximum reported ceiling in the bucket. A zero or missing ceiling yields no point. For HTTP failures, use 1 - http_check_passed | avg by (target); the pass flag already supplies one observation per check.

For each side of an operator to combine point by point, they have to agree on what a “point” is. Concretely, one of three has to hold:

• Both sides group by the same dimensions: identical by (...), order ignored, and matching rows are combined by that shared key.
• One side has no by (...) at all. A query with no grouping collapses to one value, which is then applied to every key on the grouped side, spool capacity per agent divided by one tenant-wide ceiling, say.
• One or both sides are a plain number. hop_loss | avg by (test_id) * 100 scales every series by a constant.

A key present on one side of a by (...) and not the other is simply absent from the result; there is no filling with zero, and dividing by zero is absent for that key rather than infinity, so a spike does not show up as an off-the-chart line.

For a per-host denominator against per-process results, use explicit on (...) group_left after * or /; see the examples below.

An expression combines at most four queries; past that it is a derived metric, not something to type inline every time you want it, ask us if you find yourself typing the same ratio often enough to want it as its own metric name.

A - immediately after a letter, digit or another - continues a name rather than subtracting, because metric and label names may themselves contain a hyphen (my-label). Put a space before it to mean subtraction: a | avg - b | avg subtracts; a-b | avg is one (unusual) metric name.

Explicit many-to-one matching

host_process_cpu_percent | avg by (agent_id, process_instance) / on (agent_id) group_left host_metrics_cpu_count | max by (agent_id)

The right side must group by exactly the matching keys. Missing matches and division by zero yield no point; ambiguous right-side matches are errors.

Joining sources and calculating outputs

Join another aggregated query under a name and reference its outputs with that name. This compares process memory with the host’s used memory:

from host_resources
| where resource_kind = "process" and monitored = true
| summarize avg(rss_bytes) as memory by agent_id, process_name
| join left (
    from custom
    | summarize avg(host_metrics_mem_used_bytes) as used by agent_id
  ) as host on (agent_id)
| extend memory_share = memory / host.used
| order by memory_share desc
| limit 20

join left keeps unmatched left observations; missing right values remain missing. join inner retains matching entity/time-bucket pairs. The right query must group by exactly the on keys, which must also appear in the left grouping. This supports one-to-one and many-to-one matching, including several processes or tests matching one agent. Empty keys never match. Inputs use the same bucket width, and only equal time buckets match; old values are not carried forward.

Use extend alias = expression for arithmetic over selected outputs. Supported operators are +, -, *, /, unary minus, and parentheses. Use spaces around subtraction because hyphens are legal in names. Later calculated outputs can reference earlier ones. Missing operands, division by zero, or non-finite results produce no calculated point. Adding/subtracting measured values requires matching units. Dividing matching units produces a ratio; scaling by a constant preserves the unit and does not implicitly convert bytes into another storage unit.

Join before calculating or ranking the outer query. Up to three named joins are supported. A joined query can calculate its own outputs but cannot contain nested joins, ranking, or pick. Both input and expanded output series count toward the query budget; limit does not bypass those limits.

For an alert, replace ranking with | pick memory_share and set the threshold in the condition editor. The formula applies to the selected aggregates over the alert window. Its sample count is the minimum operand count, and its contributing agents are the union of the operands’ agents.

Result predicates, rolling windows, and time shifts are not yet supported. Existing metric-selector arithmetic and explicit many-to-one matching remain available.

Continue reading

All metric query topics