5.2.2. Analyzing Logs and Metrics with KQL
💡 First Principle: KQL reads like an assembly line: the table enters first, then each pipe (|) is a station that filters, reshapes, or aggregates what flows through. There's no query planner to appease and nothing to mutate — data in, answers out. Master five stations and you can interrogate anything Azure Monitor stores.
All telemetry from this guide converges into Log Analytics tables — the App Insights set matters most: requests (incoming calls), dependencies (outgoing calls: SQL, HTTP, queue — where your model calls and pgvector queries appear), traces (application logs), exceptions, and customMetrics. Container logs from 3.2.4 land in their own tables (e.g., ContainerAppConsoleLogs_CL). One language queries them all.
The core stations, each doing one honest job:
| Operator | Job | Example |
|---|---|---|
where | Filter rows (do this first — cheap) | where timestamp > ago(1h) |
project | Keep/rename columns | project timestamp, name, duration |
summarize | Aggregate by groups | summarize avg(duration) by name |
extend | Compute new columns | extend slow = duration > 2000 |
render | Chart the result | render timechart |
Assembled into this domain's canonical investigation — "which dependency makes the chatbot slow?":
dependencies
| where timestamp > ago(24h)
| where operation_Name == "POST /chat"
| summarize p95 = percentile(duration, 95), calls = count() by name, bin(timestamp, 1h)
| render timechart
bin() buckets time for trending; percentile() beats averages for latency truth (a healthy average hides a miserable p95 — and users live at the p95). Cross-table investigation uses the shared correlation column: filter requests to the slow operation, take its operation_Id, and pull matching rows from dependencies and exceptions — KQL's expression of the tracking-number model from 5.2, and the payoff of 5.2.1's propagated context. join merges tables when needed, but the exam's bread and butter is the five stations above plus count, top, and ago() time windows.
What KQL is not: SQL. No SELECT (the table leads), no INSERT/UPDATE/DELETE (read-only by design), no correcting the past — telemetry is testimony, not state.
⚠️ Exam Trap: Drag-and-drop KQL ordering questions hinge on the pipeline model: the table comes first, where filters early (performance), summarize before render. Any arrangement starting with an operator instead of a table, or rendering before aggregating, is wrong by construction.
A handful of second-tier operators earn their place in real investigations. let names a sub-expression so a filtered set can feed several analyses without repetition; top 5 by p95 desc is the idiomatic "worst offenders" ending; parse and extract pull structured fields out of unstructured log messages — the console-log tables from Container Apps often need them before aggregation. Queries can span resources: app('rag-api-prod') reaches an Application Insights resource from elsewhere, and workspace(...) does the same for Log Analytics — the mechanism behind cross-environment comparisons in one query. Timestamps are UTC throughout; render charts in local time knowingly, not accidentally. When the same investigation runs daily, save it as a function in the workspace and the on-call runbook becomes one line.
A few conveniences worth keeping in the toolbelt: between(datetime(...) .. datetime(...)) brackets fixed incident windows where ago() handles rolling ones; dcount(user_Id) estimates distinct users where count() tallies rows — the difference between 'how many requests' and 'how many people'; and datatable lets you inline a small literal table to test a query's logic without touching real telemetry. For repeated investigations, saving a parameterized function in the workspace turns yesterday's ad-hoc archaeology into tomorrow's one-liner — the observability analogue of promoting a runbook.
Reflection Question: Why does where timestamp > ago(1h) belong at the top of the pipeline rather than just before render — what does each station's position cost?
