It reads like a sentence
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.1 That is the whole grammar lesson. The rest of the language is vocabulary, and on-call needs surprisingly little of it.
The ten patterns that cover on-call
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.2 Everything in the rest of this class, and honestly most of a monitoring career, is these ten rearranged.3
Time windows and bins
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.
Joins, sparingly
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:
// failures joined to the operations that preceded them
StorageBlobLogs
| where StatusText != "Success"
| join kind=inner (AzureActivity | project CorrelationId, Caller)
on $left.CorrelationId == $right.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; state the kind explicitly (inner keeps only matches — say what you mean); and before writing any join, ask whether one richer table already holds both facts, because it often does. A good KQL user reaches for join the way a good writer reaches for a semicolon: correctly, and not often.
From query to saved search to alert
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.
The query that was worth nine hours
Class One's outage has been the bootcamp's ghost story: a disk filled, a server died, and forty stores went dark for nine hours while two engineers guessed. This class, the on-call engineer finally writes the query that would have caught it — and it is four lines:
Perf
| where TimeGenerated > ago(1h)
| where CounterName == "% Free Space"
| summarize MinFree = min(CounterValue) by Computer
| where MinFree < 10
Every machine reporting under ten percent free disk, refreshed hourly. The disk that killed Class One's weekend spent days sliding toward full — this query would have named it with time to spare a dozen times over. It goes into the shared query pack as disk-space-low, becomes a scheduled alert rule wired to the on-call rotation, and — the team being the team it now is — a printout goes on the office wall in an actual frame, as a monument to the difference between having data and asking it questions. Four lines, nine hours. That is the exchange rate this class teaches.
The official pages, and a CAMPUX overview
Get started with log queries in Azure Monitor
learn.microsoft.com/azure/azure-monitor/logs/get-started-queries
KQL — Kusto Query Language reference
learn.microsoft.com/kusto/query/
The ten patterns typed live against the demo workspace, a broken time window caught and fixed, and the disk query growing from one line to a chart to an alert will live here. Video to be added.
Type all ten against Microsoft's free demo workspace
Microsoft runs a public demo Log Analytics workspace, stocked with realistic telemetry, free to query with any Azure sign-in. It is the best KQL practice environment that exists. Type each pattern — do not paste — because the fluency is in your fingers.
Open the demo workspace: sign in to the Azure portal, then browse to
https://portal.azure.com/#blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade
What to notice: a full Log Analytics query window over someone else's estate — dozens of tables in the schema pane on the left. Make sure the editor is in KQL mode, and note the time picker beside Run: it silently scopes every query you type.Patterns 1, 6, and 2 — explore, enumerate, filter:
SecurityEvent | take 10 Perf | distinct CounterName Perf | where TimeGenerated > ago(1h) | where CounterName == "% Free Space" | take 20
On screen: first the raw shape of a table; then the vocabulary of an unfamiliar one (this is how you learn any new table — distinct its key column); then real disk-space rows. Highlight one query at a time and press Run — the editor runs the selection.Patterns 3, 4, and 5 — shape, count, rank:
SecurityEvent | where TimeGenerated > ago(4h) | project TimeGenerated, Computer, Activity SecurityEvent | where TimeGenerated > ago(4h) | summarize count() by Activity SecurityEvent | where TimeGenerated > ago(4h) | summarize Events = count() by Computer | top 5 by Events desc
On screen: the same table asked three escalating questions — what happened, how much of each kind, and who is noisiest. That third query is the universal on-call opener: top 5 by count finds the outlier in any table in ten seconds.Patterns 7, 8, and 9 — the trend, then the picture:
Perf | where TimeGenerated > ago(1d) | where CounterName == "% Free Space" | summarize avg(CounterValue) by bin(TimeGenerated, 1h) | render timechart
On screen: a line chart of average free disk across the estate, one point per hour. Change 1h to 15m and rerun — same data, finer grain. You have just built the §5 disk story's chart with three operators and a word.Pattern 10 — and why it is last:
search "error" | take 20
The lesson: it works — and watch how much longer it takes than everything else you ran, because it scanned every table for a word. Now you know both halves: what search is for (genuinely lost) and what it costs (everything). Ten patterns, all in your fingers, all free.
Build the case-file query, then break it like Drill 04
Assemble the disk-space query one pipe at a time, watch it name real machines, then deliberately aim it at the wrong window — so the most common on-call mistake is one you have already made on purpose.
Start wide, then narrow — run after each added line:
Perf | where TimeGenerated > ago(1h) | where CounterName == "% Free Space" | summarize MinFree = min(CounterValue) by Computer
On screen: every machine in the demo estate with its lowest free-disk reading this hour. Note the named result column — MinFree = — which is what makes the next line readable.Add the threshold — the line that turns a report into a detector:
| where MinFree < 60
What to notice: the case file uses 10 percent; the demo machines are healthier, so 60 gives you rows to look at. A threshold after a summarize filters groups, not raw rows — this two-stage where is a pattern you will reuse constantly.Now sabotage it, Drill 04 style. Pretend the incident was 02:00–03:00 yesterday, and run the query with today's window:
Perf | where TimeGenerated > ago(1h) // wrong window | where CounterName == "% Free Space" | summarize MinFree = min(CounterValue) by Computer | where MinFree < 60
What to notice: the query runs, returns tidy results, and says nothing about the incident — because ago(1h) means the last hour from now. No error, no warning: a correct-looking answer to the wrong question. This is the failure mode that eats on-call hours.Fix it with an exact bracket, then save your work:
| where TimeGenerated between (datetime(2026-07-12 02:00) .. datetime(2026-07-12 03:00))The lesson: swap the ago line for a between bracket (adjust the date to yesterday's) and the query interrogates the incident's actual hours — in UTC, converted first. Then click Save → Save as query and name it disk-space-low: you have just climbed the first rung of §5's ladder, and Class Thirty turns it into an alert.
The one who found it first
Something spiked at 3am and everyone wants to know what. You open Log Analytics, start with a table, and build up a query one pipe at a time — a where, a time filter, a summarize by hour — until a million rows collapse into the single line that explains the spike. Five operators, and you are the one who found it while others were still refreshing dashboards.
Examination
Four drills, then two situations. The situations have no marking scheme — write your answer before you reveal the reasoning, or the exercise is worthless. Nothing is stored; this is between you and the page.
B — rows versus columns, the two axes of every table. where travels vertically, discarding rows that fail a condition; project travels horizontally, keeping and renaming the columns you care about. D is the same sentence reversed, and it is the reversal that produces the classic beginner error: trying to project away bad rows, or where-ing a column that project already dropped upstream — remember the pipe flows down, so a column removed on line three does not exist on line four. Hold the axes straight and patterns 2 and 3 never tangle: narrow the rows first, then trim the columns, then aggregate what survives.
bin() rounds timestamps into buckets, and buckets are what make grouping by time mean anything. Raw TimeGenerated values are precise to fractions of a second, so grouping by them directly produces a group per row — technically valid (so not A), practically useless: a million one-member groups instead of twenty-four hourly counts. bin is real arithmetic, not chart decoration (B), and it has nothing to do with limiting the window (D — that is where's job). The mental model: bin(TimeGenerated, 1h) asks "which hour does this row belong to?", and the bucket size is a resolution dial — 1h for a day's overview, 5m for the minutes around an incident. Every timechart you will ever render is a summarize-by-bin wearing a picture.
Read-only, top-to-bottom, filter-early. The first true option is worth savouring: it is the license to learn by running — no query you type this class can hurt anything, which is rare in this bootcamp and should be exploited. The two rejects invert real properties. search checks every table at once and is therefore among the slowest things you can do — breadth is the cost, not the feature; Lab 1's step 5 let you feel the difference. And KQL is strictly case-sensitive — Where is a syntax error and securityevent is not a table — which is the first thing to check when a copied query mysteriously fails. Precision in, precision out; it is a query language, not a search engine.
// checkout failures from last night's incident
1. AppServiceHTTPLogs
2. | where TimeGenerated > ago(1h)
3. | where ScStatus >= 500
4. | summarize count() by CsUriStem
5. | order by count_ desc
Line two. ago(1h) is always anchored to now — at 09:30 it means "since 08:30," a window that ends six and a half hours after the incident did. The query is syntactically perfect and semantically aimed at the wrong morning, and its clean, confident "no failures found" is the trap: an engineer who trusts it concludes the incident left no trace and starts doubting the logging instead of the window. Wrong-time-filter is the single most common broken query in on-call life precisely because nothing errors.
The fix is §3's bracket: where TimeGenerated between (datetime(2026-07-12 02:00) .. datetime(2026-07-12 03:00)) — with the incident's local "2am" first converted to UTC, the second half of the same trap. The other lines are healthy: numeric comparison on status codes is normal (A), stacked wheres before a summarize is the house style (C), and order by is real (D). The habit that prevents all of this: before reading any query's results, read its window, out loud, and ask whether the incident is inside it.
Query one establishes the shape — when exactly, how bad, one line or a cliff. Bracket the incident generously and bin fine: AppServiceHTTPLogs | where TimeGenerated between (datetime(…01:30) .. datetime(…03:00)) | where ScStatus >= 500 | summarize count() by bin(TimeGenerated, 5m) | render timechart. Thirty seconds of reading tells you whether the spike was a wall (something broke at an instant — look for a deploy, a job, a certificate) or a ramp (something filled or saturated — look at resources), and whether it truly ended at 02:40 or is still smouldering. The shape is the diagnosis's first fork, and people who skip it interrogate details before knowing which details matter. Note what the window is not: ago(1h) — Drill 04 was last night's rehearsal for exactly this page.
Query two finds where it concentrates — because spikes are almost never uniform. Same window, group by the suspects: | summarize count() by CsUriStem | top 10 by count_ — or by instance, or by client, depending on the architecture. If ninety percent of the errors are one endpoint, you have a code path; one instance, you have a sick machine; everything evenly, you have a dependency below all of them — three different investigations, separated by one summarize. This is Lab 1's "top 5 by count" opener doing its real job: not answering the question, but choosing the next one, which is what second queries are for.
Then follow the concentration to the sentences. Whatever query two names, query three reads its actual error records — project TimeGenerated, details… | take 50 — and correlates the 02:00 timestamp against what else happened at 02:00, which at Campux is a short and suggestive list: the POS job's cron fires at 02:00. The craft in this situation is not exotic KQL — every query here is Lab 1 material. It is asking the questions in an order where each answer shrinks the search space. Shape, concentration, sentences: memorise the sequence and 2am stops being improvisation.
Start from questions, not queries. The temptation is to import fifty clever queries from a blog; the discipline is to list the questions this team actually asks — from the last quarter's incidents, the services that page, the things the lead checks every morning — and write only those. A first pack is small and boring on purpose: errors-by-service-last-hour, the disk-space detector from this class's case file, failed-job-executions, top-noisy-resources, who-changed-what (AzureActivity). Ten queries that map to real questions beat fifty that map to someone else's estate. Each gets a name that reads like the question it answers and a one-line comment saying when to reach for it — because the audience is a stressed stranger at 2am, and that stranger is future you.
Then give the pack a home and a ratchet. Home: the workspace's shared saved queries, so they appear in everyone's Log Analytics — not a wiki page that drifts, not a personal folder that leaves when its owner does, which is precisely the failure you were hired into. Ratchet: a rule the team adopts in one meeting — every incident's postmortem contributes its best query to the pack. That single habit converts each painful night into permanent capability; it is the Class Twenty-Two "add the missing check" reflex, applied to investigation instead of CI. Six months of the ratchet and the pack is the team's actual incident memory, indifferent to resignations.
Close the loop by promoting the ones that earn it. Review the pack quarterly with §5's ladder in hand: a query someone runs every morning belongs on a dashboard; a query whose answer should never be "yes" (disk under ten percent, job failed twice) belongs in an alert rule, asked by a robot on a schedule. The pack is not a library — it is a pipeline from "question we asked once" to "question the system asks itself." The departing engineer's real failure was not messiness; it was keeping institutional knowledge in the one storage tier with no redundancy. You are moving it to durable storage. Say it that way in the interview, too — this exact situation is a favourite.
Five things worth carrying out of this class
- KQL reads like a sentence: table, then pipe, then operators, top to bottom — reading order is execution order. It is read-only, case-sensitive, and rewards filtering early.
- Ten patterns cover on-call: take, where+ago, project, summarize count() by, top, distinct, math-by-group, bin trends, render timechart, and search as the expensive last resort.
- Time is the axis that lies: ago() anchors to now, between() brackets an incident, bin() buckets timestamps into chartable groups — and everything is UTC, converted before compared.
- Joins sparingly: filter both sides first, state the kind explicitly, and check whether one table already holds both facts. A semicolon, not a comma.
- A query kept is infrastructure: saved to the shared pack, scheduled as an alert rule, pinned to a dashboard. Four lines would have bought back Class 1's nine hours — that is the exchange rate.
- Filter-early is a habit, not a hard law — the query engine optimises a good deal on your behalf, and a misplaced where usually costs seconds, not correctness. The reason to build the habit anyway: on the tables where it matters (billions of rows, long windows), it matters enormously, and the version of you writing that query will be tired. Put the time filter on line two every time and you never have to think about it again. ↩
- KQL is also the language of Azure Data Explorer, Microsoft Sentinel, Microsoft Defender hunting, and Class Twenty-Five's Resource Graph — one grammar, five doors. This is why the class insists on typing rather than pasting: the ten patterns transfer to every one of those tools verbatim, and "comfortable in KQL" on a CV is checkable in thirty seconds of interview screen-share. Be the candidate for whom that is a good thing. ↩
- Log Analytics also offers a Simple mode — point-and-click filters that build queries without KQL — and AI assistants increasingly draft KQL from natural language. Both are legitimate; neither replaces reading the language, because the drafted query still has to be checked by someone who can see that its time window is wrong. Drill 04's query would sail through any assistant looking plausible. The tools write; you still audit. ↩