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 habits orient the rest. The Log Analytics query editor reads data; these examples do not change the stored records. Queries can still consume resources or incur charges under some table plans, so keep practice scoped. KQL identifiers and operators are case-sensitive: where works; Where does not. Put a relevant time filter near the start so the query engine has less data to examine, while remembering that the engine also performs its own optimizations.¹

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 — keep only these columns; rename StatusText
StorageBlobLogs
| where TimeGenerated > ago(24h)
| project TimeGenerated, OperationName, Status=StatusText

// 4 · count by — how many rows per operation?
StorageBlobLogs
| where TimeGenerated > ago(24h)
| 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 — average free space in hourly buckets
Perf
| where TimeGenerated > ago(1d)
| where CounterName == "% Free Space"
| summarize AvgFree = avg(CounterValue) by bin(TimeGenerated, 1h)

// 9 · chart the hourly result; a time column is required
Perf
| where TimeGenerated > ago(1d)
| where CounterName == "% Free Space"
| summarize AvgFree = avg(CounterValue) by bin(TimeGenerated, 1h)
| 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: explore a table, narrow the rows and columns, group what remains, then present the result. Use search when you do not know which table holds a value. An unscoped search may inspect many tables, so name the tables with search in (...) when you can; a known table and column filter is usually a better starting point.² The patterns below show how to combine these operators.³

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 two sets of records share a stable identifier. A join pairs rows with the same identifier. Start with tiny in-query tables so you can predict the result before joining live telemetry:

// op-1 matches on both sides; op-2 has no error row
let Requests = datatable(OperationId:string, Name:string)
    ["op-1", "checkout", "op-2", "browse"];
let Errors = datatable(OperationId:string, Error:string)
    ["op-1", "timeout"];
Requests
| join kind=inner Errors on OperationId

The result contains checkout and timeout because both rows carry op-1; browse disappears because inner keeps only matches. With real tables, filter each side by time before joining and confirm that the keys have the same meaning. A shared column name is not enough: two unrelated services might both call a field CorrelationId without sharing values. Specify the join kind; KQL's default innerunique can deduplicate left-side keys. The leftouter example below keeps unmatched requests so you can investigate missing error records.

count() counts returned rows. summarize can calculate several named aggregates grouped by the same key, so one query can answer related questions together. Query charges depend on the table plan and data scanned; do not infer billing from the number of aggregate expressions.

Percentiles, because the average lies about latency

An average can hide a slow minority of checkout requests. percentile(DurationMs, 95) estimates the threshold below which about 95% of observed requests fall; the slowest 5% are at or above that value. Compare p50, p95, and p99 with request count and failure rate. Define any service-level objective from the user's actual experience and measurement method, not from a percentile chosen by habit.

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.