Lesson 24.5 · Azure III: Monitor, KQL & Cost · 17 min read

KQL 2: summarize, bin, percentiles

Q: Why is my error rate always zero?

Integer division. count() and countif() return longs, and long divided by long truncates, so 16 errors out of 243 requests is 0. Multiply by a real number first, errors 100.0 / requests, or use todouble(). It's the most common bug in KQL alert queries, and an alert with this bug never fires, which is worse than no alert because everyone believes it's watching.

Q: What does bin() do?

It rounds a value down to a multiple of a bucket size. bin(TimeGenerated, 5m) turns 09:23:41 into 09:20:00, so summarize ... by bin(TimeGenerated, 5m) gives one row per five-minute bucket. Choose the bucket for the window: 1m for an hour, 5m for a few hours, 1h for a day. Buckets with no data produce no row at all, so a gap in a chart means "no data", not zero.

Q: Can I summarize twice?

Yes. The output of summarize is an ordinary table, so you can aggregate it again. For example, errors per service per minute first, then per service the worst minute and the number of bad minutes. Or restarts per pod first, then per namespace. It's often the clearest way to express "the maximum of a rate" or "how many pods had problems", which a single summarize can't do.

Q: How do you calculate the error rate and p95 latency per service in KQL?

- summarize ... by groups the rows - one row out per service - and computes each aggregate per group (sort uniq -c grown up). Name the aggregates; they become your columns and alert fields. - The integer-division trap: count() returns a long, and errors / requests is 0. One real operand (100.0) fixes it - otherwise an alert on it never fires. - Percentiles, not averages: the average hides the slow tail users feel. KQL percentiles are estimates - fine for operations. - For a trend, add bin(TimeGenerated, 5m) to the by and render timechart; empty buckets do not appear, so a gap is "no data", not zero. - For the current state from snapshot tables: summarize argmax(TimeGenerated, PodStatus) by Namespace, Name.

In plain words

Imagine a teacher with a pile of test papers. Instead of reading every paper, she sorts them into piles by class, then for each pile writes down how many papers there are, how many failed, and the score that 95% of pupils beat. Then she does it again, but sorting by which week the test was taken, so she can see if scores dropped in week three.

That's summarize. summarize requests = count(), errors = countif(Success == false), p95 = percentile(DurationMs, 95) by AppRoleName makes one row per service. by bin(TimeGenerated, 5m) makes one row per five-minute bucket, which is how every graph over time works. arg_max(TimeGenerated, *) picks the latest row per pod. And one trap: long divided by long rounds down, so a 1.75% error rate shows as 0.

"Which service has the most errors, and how slow is it for the unluckiest users?" is not a list of rows - it is one number per service. Filters (24.3) cannot give you that. Grouping can, and grouping is how every incident number and every chart is made.

What you need to know already: 24.3 (where, project, extend, let), 0.1 (the golden signals: latency, traffic, errors, saturation), 0.2 (percentiles, and why they do not average), Ch 7 (sort | uniq -c: counting by group in bash).

summarize: group, then compute

summarize <aggregates> by <columns> puts the rows into groups - one group per distinct value of the by columns - and computes each aggregate (a function that turns many rows into one value, like count or average) once per group. It is sort | uniq -c (Ch 7) grown up: one row out per group.

AppRequests
| where TimeGenerated > ago(1h)
| summarize requests = count(), errors = countif(Success == false),
            p95 = percentile(DurationMs, 95) by AppRoleName
| extend errorRate = round(errors * 100.0 / requests, 2)
| order by errorRate desc

AppRoleName   requests  errors  p95      errorRate
------------  --------  ------  -------  ---------
payments-api  243       16      1829.32  6.58
web-frontend  486       22      102.15   4.53
cart          303       1       51.96    0.33
orders-api    242       0       137.7    0

Line by line: last hour of requests; per AppRoleName, count all rows (requests), count the failed ones (errors), and take the 95th percentile of duration (p95: 95% of requests were faster than this, 0.2); then add an error-rate percentage and sort by it. round(x, 2) rounds to 2 decimals.

The aggregation functions

The third column is the name KQL gives the result if you do not name it.

count()                  rows                          count_
countif(pred)            rows where pred is true       countif_
dcount(col)              distinct values (estimate)    dcount_col
sum(col) avg(col) min max                              sum_col, avg_col ...
percentile(col, 95)      one percentile                percentile_col_95
percentiles(col, 50, 95, 99)   several                 percentile_col_50, ...
make_set(col)            the distinct values, as a list    set_col
make_list(col)           all values, as a list             list_col
arg_max(col, *)          the whole row where col is largest
any(col)                 some value from the group

Unnamed aggregates get those generated names. Name them yourself (p95 = percentile(DurationMs, 95)) - the names become your column headers and your alert fields.

Time buckets: bin()

A chart over time needs one number per time slot, so you group by time - but no two timestamps are equal. bin() fixes that by rounding.

AppRequests
| where TimeGenerated > ago(2h) and AppRoleName == "payments-api"
| summarize p95 = percentile(DurationMs, 95), errors = countif(ResultCode startswith "5")
    by bin(TimeGenerated, 5m)
| order by TimeGenerated asc

bin(TimeGenerated, 5m) rounds each timestamp down to its 5-minute bucket, so the by clause makes one row per bucket. That is how every "graph over time" query works. Pick the bin to suit the window: 1m for an hour, 5m for a few hours, 1h for a day, 1d for a month.

| render timechart after it draws a line chart (time along the bottom) in the portal; the CLI prints the rows (and a note). Buckets with no rows do not appear at all - a gap in a chart is "no data", not "zero".

The latest row per thing: arg_max

Inventory tables have a row per pod per minute (24.1). To get each pod's current state you want its newest row. arg_max(A, B, C) means "the row where A is largest, and give me B and C from that row".

KubePodInventory
| where TimeGenerated > ago(15m)
| summarize arg_max(TimeGenerated, PodStatus, Computer) by Namespace, Name

"For each pod, the row with the largest TimeGenerated, and these columns from it" = the pod's current state. arg_max(TimeGenerated, *) keeps every column. Inventory tables (pods, nodes) are snapshots every minute, so this is the pattern you want far more often than take.

The integer division trap

| summarize errors = countif(Success == false), total = count()
| extend rate = errors / total            // 0 - long / long is integer division
| extend rate = errors * 100.0 / total    // 1.75 - one real operand makes it real

A long is KQL's whole-number type; a real is a number with decimals. count() returns a long, and long divided by long throws away the fraction (1 / 4 = 0). Every error-rate query written without a 100.0 or todouble() reports 0% - including in an alert that therefore never fires.

Percentiles, not averages

The average of a latency distribution hides the tail your users feel. p50 is "typical", p95/p99 is "the slow requests". During the spot incident later in this chapter, payments' average barely moves while its p95 goes from ~250ms to several seconds.

KQL percentiles are estimates (computed with an algorithm called t-digest, so it does not have to sort every value); on small groups they equal a real value from the data. Good enough for operations; do not quote them to the microsecond.

dcount

dcount() (distinct count) is also an estimate (an algorithm called HyperLogLog), accurate to a couple of percent when there are very many distinct values (high cardinality) and exact for small ones. "How many distinct users hit the error" is dcount(user); "how many pods served payments" is dcount(AppRoleInstance) (AppRoleInstance = which pod served the request).

Two summarizes in a row

The output of summarize is a table like any other, so you can summarise it again - per pod first, per namespace second (the restarts mission), or "per minute, then the worst minute":

AppRequests
| where TimeGenerated > ago(3h)
| summarize err = countif(Success == false) by AppRoleName, bin(TimeGenerated, 1m)
| summarize worstMinute = max(err), badMinutes = countif(err > 0) by AppRoleName

Shaping for charts

render timechart wants one datetime column and one or more numeric columns (or a string column to split series by):

AppRequests
| where TimeGenerated > ago(3h)
| summarize p95 = percentile(DurationMs, 95) by bin(TimeGenerated, 5m), AppRoleName
| render timechart

One line per role. In the CLI you get the rows; paste the same query into the portal's Logs blade (or a workbook, Azure's saved page of charts) for the picture.

summarize vs distinct vs count

count                      -> one number
summarize count() by X     -> one row per X with its count
distinct X                 -> the X values, no counts
summarize dcount(X)        -> how many distinct X (an estimate)
summarize make_set(X)      -> the distinct X as one list

What you can now do

Turn "per service / per minute / per pod" questions into summarize ... by.
Compute an error rate without the integer-division trap, and a p95 instead of an average.
Get the current state of every pod from a snapshot table with arg_max.

Why it helps

Almost every question you'll answer during an incident is a summarize: error rate per service, p95 latency per endpoint, restarts per namespace, errors per minute to find when it started. Being fluent with it is what makes you fast on call, and the same queries become your dashboards and log alerts.

The traps are expensive in production. An error-rate alert written as errors / total never fires because of integer division. An average latency panel stays flat while users wait seconds at p95. A gap in a chart gets read as "zero errors" when it means "no data". And counting rows in KubePodInventory counts minutes, not pods. Knowing these makes your alerts and dashboards tell the truth.

FAQ

Why is my error rate always zero?

Integer division. count() and countif() return longs, and long divided by long truncates, so 16 errors out of 243 requests is 0. Multiply by a real number first, errors * 100.0 / requests, or use todouble(). It's the most common bug in KQL alert queries, and an alert with this bug never fires, which is worse than no alert because everyone believes it's watching.

What does bin() do?

It rounds a value down to a multiple of a bucket size. bin(TimeGenerated, 5m) turns 09:23:41 into 09:20:00, so summarize ... by bin(TimeGenerated, 5m) gives one row per five-minute bucket. Choose the bucket for the window: 1m for an hour, 5m for a few hours, 1h for a day. Buckets with no data produce no row at all, so a gap in a chart means "no data", not zero.

Why use percentiles instead of averages?

Because latency distributions have long tails, and the average hides them. If 95% of requests take 100 ms and 5% take 5 seconds, the average looks fine while one in twenty users waits five seconds. p50 shows the typical experience, p95 and p99 the slow requests users complain about. KQL's percentiles are estimates using a t-digest, accurate enough for operations but not to the microsecond.

What is arg_max for?

Getting the whole row where a column is largest, per group. summarize arg_max(TimeGenerated, *) by Name gives each pod's latest snapshot with all its columns: its current status, node and restart count. It's the key pattern for inventory tables, which record every pod every minute. arg_max(TimeGenerated, PodStatus, Computer) keeps only the columns you name, which is lighter.

Can I summarize twice?

Yes. The output of summarize is an ordinary table, so you can aggregate it again. For example, errors per service per minute first, then per service the worst minute and the number of bad minutes. Or restarts per pod first, then per namespace. It's often the clearest way to express "the maximum of a rate" or "how many pods had problems", which a single summarize can't do.

In an interview Mid

How do you calculate the error rate and p95 latency per service in KQL?

AppRequests
| where TimeGenerated > ago(1h)
| summarize requests = count(), errors = countif(Success == false),
            p95 = percentile(DurationMs, 95) by AppRoleName
| extend errorRate = round(errors * 100.0 / requests, 2)
| order by errorRate desc

summarize ... by groups the rows - one row out per service - and computes each aggregate per group (sort | uniq -c grown up). Name the aggregates; they become your columns and alert fields.
The integer-division trap: count() returns a long, and errors / requests is 0. One real operand (100.0) fixes it - otherwise an alert on it never fires.
Percentiles, not averages: the average hides the slow tail users feel. KQL percentiles are estimates - fine for operations.
For a trend, add bin(TimeGenerated, 5m) to the by and render timechart; empty buckets do not appear, so a gap is "no data", not zero.
For the current state from snapshot tables: summarize arg_max(TimeGenerated, PodStatus) by Namespace, Name.

Also asked: Why can average latency be misleading, and what do you use instead? · How do you get the current state of each pod from a table of per-minute snapshots? · How would you chart errors per minute for one service?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.