KQL essentials

Most query languages make you say things inside-out — SQL wants the columns before you have named the table. KQL — the Kusto Query Language, the native tongue of every Log Analytics workspace — flows the way you think: start with a table, then pipe it through transformations, top to bottom, each step feeding the next. Say it aloud and it is barely code: take the storage logs; keep the last day; keep the failures; count them by operation.

// the sentence, written down
StorageBlobLogs
| where TimeGenerated > ago(1d)
| where StatusText != "Success"
| summarize count() by OperationName

KQL query

A pipeline that starts from a table and flows downward through operators — each | hands the previous step's rows to the next. Reading order is execution order, which is why KQL can be read aloud and SQL mostly cannot.

Three facts orient everything else. KQL is read-only — no query can damage the data, so the right way to learn is to run things and see. It is case-sensitive — where works and Where does not, and table names must match the schema pane exactly. And it rewards filtering early: put the time filter first, because every operator downstream then works on thousands of rows instead of millions.¹ That is the whole grammar lesson. The rest of the language is vocabulary, and on-call needs surprisingly little of it.

A small copy of three tables this class already talks about — StorageBlobLogs, AppRequests, AzureActivity — sits in this page, nowhere else. Type a query, run it, watch the row count move. It supports a deliberately small slice of KQL; anything outside it says so plainly instead of guessing.

This box runs entirely in your browser against canned data. Lab 1, below, is the real thing — a public Log Analytics workspace with real telemetry.

Fluency in KQL is not knowing four hundred operators; it is having ten patterns loaded and ready at 2am. Here they are, each in the form you will actually type. Read them now; type them all in Lab 1.

// 1 · peek — what does this table even look like?
StorageBlobLogs
| take 10

// 2 · filter — the workhorse: a time window, then conditions
StorageBlobLogs
| where TimeGenerated > ago(24h)
| where StatusText != "Success"

// 3 · project — only the columns you need, renamed if useful
| project TimeGenerated, OperationName, Status=StatusText

// 4 · count by — how many, grouped by what
| summarize count() by OperationName

// 5 · top — the worst offenders, sorted server-side
AzureActivity
| top 20 by TimeGenerated desc

// 6 · distinct — what values exist in this column?
Perf
| distinct CounterName

// 7 · math by group — avg, max, min, percentiles per group
Perf
| where CounterName == "% Free Space"
| summarize min(CounterValue) by Computer

// 8 · trend — the same math, but over time buckets (§3)
| summarize avg(CounterValue) by bin(TimeGenerated, 1h)

// 9 · chart it — one word turns the trend into a picture
| render timechart

// 10 · search — the last resort, when you don't know the table
search in (StorageBlobLogs, AzureActivity) "store-23"
| take 20

Notice the shape of the set: one pattern to explore (1), two to narrow (2, 3), four to aggregate (4–8 overlap), one to see (9), one to flail productively (10). Pattern 10 earns its "last resort" label — search scans everything and is slow by design, so it is for the moment you genuinely do not know where a value lives, never for routine work.² Everything in the rest of this class, and honestly most of a monitoring career, is these ten rearranged.³

Text hunting — the workhorse's sharper edge

Pattern 2 filters on whole values; the 2am filter is usually a fragment — an error phrase, a store id buried in a URL. KQL offers two verbs that look interchangeable and are not. has matches whole terms against the workspace's index, so it is fast — and it will not find store-23 inside store-234, because that is not a term match. contains is a raw substring scan: it finds the fragment anywhere, and pays for the privilege by reading everything. Reach for has first; drop to contains only when what you hunt is genuinely a piece of a longer word.

// 2a · hunt text — has for whole terms (indexed), contains for substrings (scan)
AppTraces
| where TimeGenerated > ago(1h)
| where Message has "timeout"
| where Message contains "tore-2"   // only a substring scan finds a fragment

The case rules ride alongside: has and contains ignore case by default, == does not, =~ is equality with case forgiven, and the _cs suffix — has_cs, contains_cs — buys sensitivity back when the case carries meaning. And when the column you want to filter on does not exist, extend manufactures it: a computed column the table never had, available to every operator downstream.

// 2b · extend — compute the column you wish the table had
AppRequests
| extend DurationSeconds = DurationMs / 1000.0
| where DurationSeconds > 2

Log questions are time questions — last night, since the deploy, the hour around the alert — so time handling deserves its own section. Two tools do nearly all of it. ago() anchors a window to now: ago(1h), ago(24h), ago(7d). For an incident with known bounds, between brackets it exactly:

// the hour around a 02:00 incident — remember: TimeGenerated is UTC
ContainerAppConsoleLogs_CL
| where TimeGenerated between (datetime(2026-07-12 01:30) .. datetime(2026-07-12 03:00))

Then bin() — the function behind every chart you will ever build. Summarizing by raw TimeGenerated is useless (every millisecond is its own group); bin(TimeGenerated, 1h) rounds each timestamp down into hour buckets, so summarize count() by bin(TimeGenerated, 1h) means "how many per hour" — the exact shape a render timechart wants. Two habits keep time from lying to you: everything in the workspace is UTC, so translate before comparing against anyone's "2am"; and choose the window before the operators, because the single most common broken query on-call is a correct question aimed at the wrong hours — Drill 04 is that mistake, preserved like a specimen.

Sometimes the answer genuinely lives in two tables — sign-ins in one, audit changes in another, and the question is "who signed in and then changed the firewall?" KQL's join stitches tables on a shared column:

// who signed in, joined to what they then changed
SigninLogs
| where TimeGenerated > ago(1h)
| project CorrelationId, UserPrincipalName
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(1h)
    | project CorrelationId, OperationName)
    on CorrelationId

The advice in the heading is the lesson: sparingly. Joins are where beginner queries go to die — slow when either side is unfiltered, and quietly wrong when the join column is not what you assumed or the kind silently drops or duplicates rows. Three rules keep them honest: filter both sides before joining, not after — the query above does, once per side; state the kind explicitly, because leaving it unstated gives you innerunique, which silently deduplicates the left side before matching (inner keeps every match — say what you mean); and before writing any join, ask whether one richer table already holds both facts, because it often does. One more habit worth the ink: check that the join column means the same thing on both sides. A correlation id from a storage data-plane log and one from an ARM control-plane event are both called CorrelationId and will essentially never match — the query runs clean and returns nothing, which is Drill 04's lesson wearing a different hat. A good KQL user reaches for join the way a good writer reaches for a semicolon: correctly, and not often.

count() answers "how many," and for a surprising share of on-call that is enough. But the questions that decide a service-level target, name a culprit, or size a bill need summarize to do real arithmetic — and its working vocabulary is small enough to hold in your head at 2am. The first thing to know is that a single summarize can carry several aggregates at once, each given a name, all grouped by the same key. One pass over the table answers three questions instead of three passes answering one, and the workspace bills you for the pass, not the questions.

Percentiles, because the average lies about latency

Campux's checkout endpoint averages 240 milliseconds, and the dashboard is green. The average is a comfortable fiction. If one shopper in twenty waits four seconds for the "Pay" button to respond, the mean barely twitches — twenty fast requests drown one slow one — but that one-in-twenty is thousands of abandoned carts a week, and it is invisible to avg(). percentile(DurationMs, 95) is the number that tells the truth: the wait the slowest-served 5% actually endure. The 95th and 99th percentiles are what a service-level objective is written against, because the business is felt at the tail, not the middle. Ask for several at once and name them, and one query becomes a latency report:

The average is a comfortable lie.

// Campux checkout, per endpoint — load and honest latency, last 24h
AppRequests
| where TimeGenerated > ago(24h)
| summarize
    calls   = count(),
    clients = dcount(ClientIP),
    failed  = countif(Success == false),
    p50     = percentile(DurationMs, 50),
    p95     = percentile(DurationMs, 95),
    p99     = percentile(DurationMs, 99)
    by Name
| extend fail_rate = round(100.0 * failed / calls, 1)
| top 10 by calls desc

Read the row for POST /checkout and the whole shape of a problem arrives at once: how busy it is (calls), how many distinct shoppers felt it (clients), how often it failed (fail_rate), and — the line the average was hiding — a p50 of 180 beside a p99 of 3,100. A healthy median and a savage tail is the signature of a resource under intermittent contention, and you would never have seen it in the mean. Note dcount earning its place: it counts distinct clients cheaply because it estimates rather than tallies — right for "roughly how many shoppers," wrong for anything an auditor will read.

let — name a thing once, change it once

The moment a query mentions the same window twice, the same threshold in two places, or an inner table that wants a name, reach for let. It binds a value — a duration, a number, a string, even a whole subquery — to a name at the top, and the rest of the query reads it. This is not decoration; it is the difference between a query you can safely edit at 2am and one where you change the window in one where, miss the second, and quietly compare two different hours. Define the tunable things once, up top, where the next reader — future you — can see and adjust them without hunting:

// who changed what across the estate today — one knob, defined once
let window = 24h;
let noisy  = 20;                       // flag anyone past this many changes
AzureActivity
| where TimeGenerated > ago(window)
| where ActivityStatusValue == "Success"
| summarize changes = count(), kinds = dcount(OperationNameValue)
    by Caller, ResourceGroup
| where changes > noisy
| order by changes desc

To widen the sweep to a week, change one character — 24h to 7d — and every mention updates together. AzureActivity is the estate's control-plane diary: every create, delete, and role change, stamped with its Caller. Grouped this way it answers the question that opens most Campux incidents — "what changed before this broke?" — and the noisy threshold is exactly the kind of number you will tune per estate, which is exactly why it belongs on its own line and not buried in a filter.

parse and extract — the column the table never had

Half of on-call is fishing a field out of a text column: a store id inside a request URL, a queue depth inside a log message. Two tools do it, and the distinction is worth keeping straight. extract takes a regular expression and pulls one capture group — the right reach when the value sits at no fixed position, like a query-string parameter that may or may not be present:

// which stores drove the most traffic — store id lives inside the URL
AppRequests
| where TimeGenerated > ago(1h)
| extend Store = extract(@"[?&]store=(\d+)", 1, Url)
| where isnotempty(Store)
| summarize calls = count() by Store
| top 5 by calls desc

parse is the other tool, and it shines where extract is overkill: a message with a known, fixed layout, where you name the pieces in reading order and KQL fills them in. It reads cleanly precisely because the format is fixed — reach for it on your own structured log lines, not on a URL whose parameters arrive in any order:

// a fixed-format app log line, unpicked into named columns
// "job=nightly-pos store=23 rows=48120 ms=920"
AppTraces
| where Message has "job=nightly-pos"
| parse Message with "job=" Job " store=" Store " rows=" Rows:long " ms=" Ms:long
| where Ms > 500
| summarize avg(Ms) by Store

Either way, the payoff is the same: a computed column the table never stored becomes something every operator downstream can filter, group, and chart. The field was always in the data — you just gave it a name.

inner versus leftouter — the join that finds what is missing

§4 taught the rules that keep any join honest — filter both sides first, state the kind, check the join column means the same thing on both sides. The kind itself carries a second lesson worth its own paragraph, because inner and leftouter answer opposite questions. inner keeps only rows that match on both sides — the intersection, and the right choice when you trust that a match exists and want the enriched pair. leftouter keeps every left row and pads the misses with empty columns — which is the only way to ask the most useful question a join can pose: which left rows had no match at all? The failures nobody logged, the requests with no exception record, the orders with no shipment — the gaps live exactly where an inner join throws rows away.

// failed requests with NO exception logged — the silent failures inner would hide
AppRequests
| where TimeGenerated > ago(1h) and Success == false
| join kind=leftouter (
    AppExceptions
    | where TimeGenerated > ago(1h)
    | project OperationId, Problem = Type
  ) on OperationId
| where isempty(Problem)
| summarize count() by Name, ResultCode

Run it with kind=inner and you get the opposite report — failures that did log an exception, the ones already easy to debug. The leftouter plus isempty pattern is how you find the failures that left no fingerprint, which are the ones that eat an evening. Choosing the kind is not a syntax detail; it is choosing which half of reality the query is allowed to see.

A query typed once answers a question. A query kept becomes infrastructure, and there are three rungs on that ladder:

Saved

A good query gets a name and a place in the workspace's shared query pack — 2am-you should never rewrite what 2pm-you already perfected, and a team's saved queries are its institutional memory of every past incident.

Scheduled

The same query becomes a log alert rule: Azure runs it on a cadence, and when the results cross a threshold ("any computer with under 10 percent free disk"), it fires into the alerting machinery Class Thirty builds.

Answered before asked

Pinned to a workbook or dashboard, the query runs every time someone looks — turning "run this to check" into "glance at this."

A saved query is a lesson that stays learned.