KQL That Earns Its Keep: summarize, join, make-series and Time-Bucketing Azure Logs

You can write the App Insights queries that find a slow endpoint, but the moment someone asks “show me failed requests per minute, per region, with a 7-day baseline,” the query falls apart. You reach for summarize, get ragged time gaps, try to join in the baseline and the row count explodes, and twenty minutes later you have a screenshot of something almost right. KQL — the Kusto Query Language that powers Azure Log Analytics, Application Insights, Azure Resource Graph, Microsoft Sentinel and Azure Data Explorer — is easy to start and deep once you push it. The gap between “I can filter logs” and “I can turn logs into answers” is four or five operators, and this article is about exactly those.

The four that earn their keep are summarize (collapse many rows into aggregates per group), bin() (snap timestamps into fixed buckets so “per minute” actually means per minute), join (correlate two tables — choosing the right one of five flavours so you don’t silently drop or duplicate rows), and make-series (a dense, gap-filled time series ready to chart or feed an anomaly function). Around them sits the glue: extend, project, parse/extract, mv-expand and materialize. Master that set and most “I need a dashboard / an alert / a root-cause query” tasks become a ten-line query you write in one pass.

This is a concept-and-pattern guide, not a syntax dump. We lead with the mental model — KQL is a left-to-right pipeline where each | takes a table in and emits a table out, and where you filter early and aggregate late because the engine scans columnar, time-partitioned data. Then we go operator by operator with decision tables (which join kind, which aggregation function, which time grain), walk an end-to-end observability query, and close with the cost and performance levers. Everything here runs in the Log Analytics query editor on the demo data or your own workspace; bring a workspace and follow along.

What problem this solves

Logs arrive as a firehose of individual events — one row per request, per heartbeat, per sign-in, per syslog line — and a single busy workspace ingests millions of rows an hour. A human cannot read that. The entire value of a query language over logs is reduction: turning ten million rows into the twenty numbers that answer “is it healthy, what changed, who is affected, and is this normal.” summarize is the reduction engine; bin() makes the reduction time-aware; join lets the reduction span more than one signal; make-series makes the reduction chart-ready and statistically tractable. Without fluency in these, you are stuck at “filter and eyeball,” which does not survive contact with production volume.

What breaks without this skill is subtle and expensive. People paste a requests | where success == false into a dashboard tile and wonder why the chart is jagged and the time axis lies — because they never bucketed. They join an exceptions table to a requests table with the default kind=innerunique and quietly lose duplicate matches, so “errors per request” is wrong and nobody notices for a quarter. Or they write a query that scans traces over 30 days with no time filter, the editor spins, and they conclude “Log Analytics is slow” when in fact they handed the engine an unbounded scan. Each of these is a query-shape problem, not a platform problem, fixed by understanding what these operators do and what they cost.

Who hits this: anyone who has graduated from “App Insights gave me a chart” to “I need a specific answer the canned charts don’t give.” SREs building alert queries (a Log Analytics alert is a KQL query on a schedule), platform engineers building workbooks, security analysts hunting in Sentinel, FinOps folks slicing usage. The skill is portable — the same summarize/bin/join/make-series you learn against AppRequests works against SigninLogs, AzureDiagnostics, Heartbeat, syslog, custom tables, and Resource Graph.

The small set of operators that does most of the heavy lifting, and the job each owns:

Learning objectives

By the end of this article you can:

• Read any KQL query as a left-to-right pipeline, predict its shape before running it, and explain why “filter early, aggregate late” is the cardinal rule.
• Use summarize fluently — pick the right aggregation function (count, dcount, percentile, arg_max, …), group by one or many keys, and bucket time with bin() so “per minute” is exact.
• Choose correctly among the five join kinds (innerunique, inner, leftouter, rightouter, fullouter, plus the semi/anti leftsemi/leftanti) and name what each one does to unmatched and duplicate rows — so you never silently lose or multiply data.
• Build a gap-filled time series with make-series, fill missing buckets with a default, and feed it into series_decompose_anomalies() or render it as a chart.
• Shape and clean data with extend, project, parse, extract, mv-expand and bag_unpack, and know when to reach for each.
• Apply the cost and performance levers — time filters, column projection, materialize(), hint.shufflekey, query-scope choice — that keep a query fast and cheap on a busy workspace.
• Turn a question into a query in one pass: failed-requests-per-minute-per-region-with-baseline, top-N noisy resources, error-to-request correlation, sign-in anomaly detection.

Prerequisites & where this fits

You should already have a Log Analytics workspace with data in it (App Insights telemetry, a VM sending Heartbeat/Perf, activity logs, or the demo data) and know how to open the Logs blade and run a query. If you are still standing one up, How to Create a Log Analytics Workspace: Region, Access Modes and Resource-Context RBAC covers the workspace, and The Azure Monitor Data Platform Explained: Metrics, Logs, Traces and Where Each Lives covers what lands in logs versus metrics. You need no prior Kusto; we build from the pipeline up. SQL familiarity (GROUP BY, JOIN) helps, though KQL’s join semantics differ in ways this article makes explicit.

This sits in the Observability track — the language layer you use after you understand where data lives and before you build alerts, workbooks and dashboards. It pairs tightly with Metrics vs Log Analytics: Choosing the Right Store for Speed, Cost and Retention — KQL applies only to the logs store, and knowing when a question belongs to metrics saves you from forcing log queries to do a metric’s job. If queries return nothing at all, that is an ingestion problem, not a KQL problem; see No Logs Showing Up? Troubleshooting Empty Log Analytics Tables and Ingestion Gaps. The same language drives control-plane inventory in Azure Resource Graph Cookbook: KQL Queries for Inventory and Drift at Scale, so most of what you learn here transfers there.

The surfaces that run KQL — the language is shared, but the data and limits differ:

Write your query in terms of TimeGenerated/timestamp and the standard operators, and it ports across these with minor column-name changes.

Core concepts

Five mental models make every later pattern obvious.

KQL is a pipeline, read left to right. A query is a source table followed by operators joined by the pipe |. Each operator receives a table and emits a table. AppRequests | where Success == false | summarize count() by Name reads literally as “take AppRequests, keep the failures, count them per operation name.” You compose the transformation step by step, and the shape at any point is just “whatever the last operator emitted” — so you can always inspect the table at every stage by truncating the query.

Filter early, aggregate late — because the data is columnar and time-partitioned. Log Analytics stores data in compressed, columnar shards partitioned by ingestion time. A where TimeGenerated > ago(1h) lets the engine skip every shard outside that window — it never reads them. Every row you eliminate before a summarize or join is a row those expensive operators never touch. The canonical good shape: narrow the time window → filter rows → project away unneeded columns → THEN summarize/join. This rule alone separates a 300-ms query from a multi-second one.

summarize collapses rows into groups; the by clause defines the groups. summarize <aggregations> by <grouping keys> produces one output row per distinct combination of the grouping keys. No by means one group — the whole table. The grouping keys can be columns or expressions, and the killer expression is bin(TimeGenerated, 1m), which rounds each timestamp down to its minute so “group by minute” works — demystifying aggregation and time-bucketing at once.

join correlates two pipelines, and the kind decides what happens to unmatched and duplicate rows. LeftTable | join kind=<kind> (RightTable) on <key> matches rows by key. The trap that bites everyone: KQL’s default join is innerunique, which first de-duplicates the left table on the join key, so it is not the inner join you know from SQL. The flavours differ precisely in whether unmatched rows survive and whether duplicates multiply — the difference between a correct answer and one silently 30% off. Full decision table below.

make-series is the dense cousin of summarize for time. summarize count() by bin(TimeGenerated, 1m) only emits rows for minutes that had data — quiet minutes are missing, so a chart connects across holes and lies about gaps. make-series instead produces a value for every bucket in the range, filling empties with a default, returning arrays ready to render or feed to anomaly/forecast functions. When you need a real, evenly-spaced curve, you want make-series, not summarize.

The pipeline and the cardinal rule

Everything starts with reading a query as a flow. Read this one aloud, stage by stage:

AppRequests
| where TimeGenerated > ago(1h)        // 1. narrow the time window (shard pruning)
| where Success == false               // 2. keep only failures
| project TimeGenerated, Name, ResultCode, DurationMs, OperationId  // 3. drop unused columns
| summarize failures = count(),        // 4. aggregate, late
            p95 = percentile(DurationMs, 95)
        by Name, bin(TimeGenerated, 5m)
| order by failures desc                // 5. sort the small result

Stage by stage the table shrinks: the time filter discards most shards, the success filter discards most surviving rows, the projection narrows the width, and only then does summarize group what’s left. By the time order by runs it sorts a handful of rows. That is the shape you want every query to have.

The ordering is not stylistic — it changes how much data the engine touches. This table makes the rule concrete with the why for each step:

The two shaping operators beginners conflate, distinguished:

A useful debugging habit: truncate the query after any stage to see the intermediate table. Append | take 10 to sample, or | getschema to see the columns and types the pipeline emits — you never guess about shape, you look.

summarize: the reduction engine

summarize is where logs become answers. It is two lists: the aggregations (what to compute) and, after by, the grouping keys (what to compute them per). The output has one row per distinct grouping-key combination, one column per aggregation plus the keys.

AppRequests
| where TimeGenerated > ago(24h)
| summarize
    total      = count(),
    failed     = countif(Success == false),
    p50        = percentile(DurationMs, 50),
    p95        = percentile(DurationMs, 95),
    p99        = percentile(DurationMs, 99),
    users      = dcount(UserId)
  by Name
| extend failRate = round(100.0 * failed / total, 2)
| order by failed desc

That one query gives you, per operation, the volume, failure count, latency distribution, distinct users, and a derived failure-rate — the core of an endpoint health view. Note the pattern: aggregate first, then extend the ratio from the aggregated columns, since failRate only makes sense after count() and countif() exist.

Choosing the aggregation function

The function decides what you compute per group. Picking the wrong one is a quiet error — count() when you meant dcount(), avg() when a percentile() would tell the truth about latency. The menu you actually use:

Two deserve emphasis. arg_max(TimeGenerated, *) is the idiomatic “latest row per entity” — summarize arg_max(TimeGenerated, *) by ResourceId gives the most recent record for each resource, all columns intact, in one line (no window functions, no self-join). And dcount() is approximate by design (HyperLogLog, counting distinct values without holding them all in memory); for “how many unique users” the ~1% error is irrelevant, but dcount(UserId, 4) raises accuracy if you need it tighter. Knowing it is approximate prevents the “my distinct count is off by a few” confusion.

Grouping by one key, many keys, and none

The by clause is flexible; the multi-key and no-key cases are the ones that trip people up:

The last row points at a real subtlety: when you group by a value pulled from a dynamic (JSON) column, wrap it in tostring() (or the right to* function) so the key has a concrete scalar type — grouping by a raw dynamic can behave unexpectedly.

Time-bucketing with bin()

“Per minute” is meaningless until every timestamp in a bucket maps to the same representative value. bin(value, roundTo) rounds value down to the nearest multiple of roundTo. For datetimes, bin(TimeGenerated, 1m) turns 12:03:47.812 into 12:03:00, so every event in that minute shares one key and summarize ... by bin(TimeGenerated, 1m) yields one row per minute.

AppRequests
| where TimeGenerated > ago(6h)
| summarize requests = count(),
            failed   = countif(Success == false)
        by bin(TimeGenerated, 5m)
| render timechart

That is the canonical “requests over time” tile. Two things make or break it. First, the grain (5m here) controls resolution and cost: finer = more buckets, busier chart; coarser = fewer rows, smoother, cheaper. Second, this summarize emits only buckets that had data — quiet periods are absent, and a timechart draws a straight line across the gap as if traffic were steady. If that lie matters, switch to make-series (below).

Pick the grain to fit the window — too fine over a long range floods you with buckets; too coarse over a short range hides the spike:

Rule of thumb: aim for ~50–500 buckets across your window. Below that you lose resolution; far above it the chart is noise. A few time idioms beyond bin() worth keeping handy:

One timezone note: Log Analytics stores and bins in UTC. bin(TimeGenerated, 1d) gives UTC days, which may not match a viewer’s local “today.” For display, shift before binning (TimeGenerated + 5h30m for IST) — but know the raw buckets are UTC.

join: correlating two signals (and the five flavours)

The moment your question spans two tables — “which requests triggered which exceptions,” “which resources have no heartbeat,” “errors enriched with deployment metadata” — you need join. The footgun: the default kind is innerunique, not inner, and it silently de-duplicates the left table on the join key first. If you don’t know that, your counts are wrong and you get no error.

The shape is LeftTable | join kind=<kind> (RightTable) on <key>; the right table goes in parentheses (itself a full pipeline). The flavour reference — the columns that matter are “what happens to unmatched rows” and “to duplicates”:

Three solve recurring real problems.

inner vs innerunique — getting counts right. Joining requests to exceptions to count exceptions per request with the default innerunique collapses duplicate request keys and undercounts. Be explicit:

AppRequests
| where TimeGenerated > ago(1h)
| project OperationId, Name, ResultCode
| join kind=inner (
    AppExceptions
    | where TimeGenerated > ago(1h)
    | project OperationId, ExceptionType = Type, Method = Method
  ) on OperationId
| summarize exceptions = count() by Name, ExceptionType
| order by exceptions desc

leftanti — finding what’s missing. The one people forget exists and reinvent badly. “Which VMs stopped sending heartbeats?” is all expected VMs anti-joined against VMs seen recently:

// Machines that reported in the last 24h but NOT in the last 10 min = went silent
Heartbeat
| where TimeGenerated > ago(24h)
| distinct Computer
| join kind=leftanti (
    Heartbeat
    | where TimeGenerated > ago(10m)
    | distinct Computer
  ) on Computer
// result: computers present 24h ago but absent in the last 10 minutes

leftouter — enrich without dropping. When you join a lookup (deployment metadata, a tag table) and want to keep every left row even if the lookup misses, use leftouter; unmatched rows get nulls on the right, and you can coalesce() a default. Using inner here silently drops rows with no match — a classic data-loss bug.

Join mechanics that prevent surprises:

A lighter alternative to join for combining similar-shaped results is union, which stacks rows vertically (not by key). Use union for “all of these events together” (union AppRequests, AppDependencies) and join for “these correlated by a key.”

make-series: dense, gap-filled time series

As we saw, summarize ... by bin() omits empty buckets — a cosmetic lie for a chart, fatal for anomaly detection, where the math expects an evenly-spaced series with no holes. make-series emits a value for every step across an explicit range, fills gaps with a default, and returns the result as arrays — typically one row whose columns are the whole series.

AppRequests
| where TimeGenerated > ago(24h)
| make-series
    requests = count() default = 0
    on TimeGenerated
    from ago(24h) to now() step 5m
    by cloud_RoleName
| render timechart

Read the clauses: make-series <agg> default=<fill> on <timecol> from <start> to <end> step <grain> by <splitkey>. Every 5-minute slot from 24h ago to now gets a requests value; quiet slots become 0; by cloud_RoleName produces one series per role. The difference from summarize is the guarantee of density — no missing buckets, ever.

When to choose which:

Because make-series returns arrays, you process them with the series functions:

A complete anomaly query:

AppRequests
| where TimeGenerated > ago(7d)
| make-series reqs = count() default = 0
    on TimeGenerated from ago(7d) to now() step 1h
| extend (anomalies, score, baseline) =
    series_decompose_anomalies(reqs, 1.5)   // 1.5 = sensitivity
| render anomalychart with (anomalycolumns = anomalies)

That flags hours where request volume deviated from its own learned baseline and seasonality — an “is this spike abnormal?” detector in five lines, impossible without the dense series. To turn the arrays back into a row-per-bucket table, mv-expand unpacks them.

Shaping and cleaning: extend, parse, mv-expand, dynamic

Real logs are messy: useful values are buried in free-text messages, packed into JSON dynamic columns, or stored as arrays. The shaping operators get the value out so you can group or filter on it.

Computed columns and string extraction

extend adds columns; parse and extract pull structure from strings. Use parse when the text has a stable pattern, extract (regex) when it’s irregular:

// parse: pull fields from a structured-ish message
AppTraces
| where Message has "latency"
| parse Message with * "endpoint=" endpoint " latency=" latencyMs:long "ms" *
| summarize p95 = percentile(latencyMs, 95) by endpoint

// extract: one regex group out of free text
AzureDiagnostics
| extend status = extract(@"status=(\d{3})", 1, ResultDescription)
| summarize count() by status

The shaping toolkit, with the job each owns:

Working with dynamic (JSON) columns

Many tables have a dynamic column — Properties, Tags, customDimensions — holding nested JSON. Reach in with dot/bracket notation, and coerce the leaf to a scalar type before using it:

AppRequests
| extend region = tostring(customDimensions.region),
         tier   = tostring(customDimensions.serviceTier)
| summarize count() by region, tier

For arrays inside JSON, mv-expand is the unpacker — it turns one row with an N-element array into N rows, so you can group across elements:

// One row per tag, from a JSON array of tags
SomeTable
| mv-expand tag = parsedTags          // array column -> one row per element
| summarize events = count() by tostring(tag)

The dynamic/array handling table:

One readability habit across all of this: define reusable pieces with let at the top. It binds a name to a scalar, list, table expression, or function, so you write the messy part once:

let lookback = 24h;
let slowMs   = 1000;
let prod = AppRequests | where TimeGenerated > ago(lookback) and cloud_RoleName == "prod-api";
prod
| summarize total = count(), slow = countif(DurationMs > slowMs) by Name
| extend slowPct = round(100.0 * slow / total, 1)
| order by slowPct desc

Architecture at a glance

There is no system diagram here because the “architecture” is the shape of a query and the path data takes through the engine — a mental model, not a deployment.

Data lands in your Log Analytics workspace continuously and is stored columnar and partitioned by ingestion time — a stack of immutable, compressed time-slices, each holding all columns for the rows ingested in that window. The engine reads a query as a left-to-right pipeline: the source table names which slices to consider, and each operator transforms the table and hands it on. First comes partition pruning — a where TimeGenerated > ago(1h) discards every slice outside that hour without reading it. Then column pruning: because storage is columnar, a query projecting only Name and DurationMs physically reads only those two columns. So the cheapest queries are narrow in time and narrow in columns, and they get there first.

After pruning, data flows through the operators in order. where clauses thin the rows; summarize/make-series then fan in, collapsing many rows to few grouped by your keys. A join is the one place data fans out — the engine builds the (filtered, projected, ideally small) right-hand table, distributes it across nodes, and matches the left stream against it; this is why an unfiltered join is the most expensive thing you can do. The final order/top/render act on a result that should, by now, be tiny. Picture every good query as a funnel: wide at the source but immediately pruned by time and columns, narrowed by filters, fanned-in by aggregation, emitted small and shaped. When a query is slow, point to where that funnel failed to narrow early — an absent time filter, a wide column carry, or a join on raw tables — and fix it there.

Real-world scenario

LumenCart, a mid-size online retailer (fictional, but the shape is real), runs a .NET API on App Service behind Front Door, fronting Azure SQL and a handful of third-party APIs, all telemetered to one Log Analytics workspace via Application Insights. During a Tuesday-evening flash sale, the on-call SRE, Priya, gets paged: checkout latency is up and a trickle of 5xx is showing on the public status page. The canned App Insights “Failures” blade shows more exceptions but doesn’t say whether this is a code bug, a dependency, or load — and the marketing team is asking “is the site actually down or just slow?” Priya has ninety seconds of credibility before someone says “just restart it.”

She starts with the funnel mindset. First, is volume abnormal or just elevated? A make-series of request count over seven days at hourly grain, with series_decompose_anomalies, confirms the spike is above baseline but not record-breaking — real load, not a retry storm. Next, where do the failures concentrate? A summarize failed = countif(Success==false), p95=percentile(DurationMs,95) by Name, bin(TimeGenerated, 5m) over the last hour pins failures to two operations — POST /checkout and POST /payment — with p95 climbing from 400 ms to 4.2 s on exactly those, while every other endpoint is flat. Not a global outage; two endpoints degrading.

Now the cross-signal step. She joins requests to dependencies on OperationId (kind=inner, explicitly — the default would dedupe and undercount) and summarizes dependency duration by target: the slow path is dominated by the external payment provider, whose dependency p95 has gone from 120 ms to 3.8 s. A second query — a leftanti of “expected DB calls” vs “successful DB calls” — comes back empty, exonerating SQL. The picture is now defensible: the payment provider is slow under flash-sale load, dragging checkout p95 up and tripping a fraction of requests past the Front Door origin timeout into 5xx. Not LumenCart’s code, not the database, not capacity.

The fix follows from the diagnosis, not panic. They raise the Front Door origin timeout to stop borderline-slow checkouts becoming hard 5xx, flip the payment client to a circuit-breaker-with-retry so a slow provider degrades gracefully, and — because the anomaly query proved load was within plan — do not scale up blindly. Priya pins three queries to a shared workbook so the next incident starts where this one ended. The whole diagnosis took under ten minutes, every claim backed by a query she could show. That is the difference KQL fluency makes: not prettier charts, but faster, defensible attribution under pressure — using exactly the five operators this article centres on: summarize, bin(), join (the right kind), make-series, and an anomaly function on top.

Advantages and disadvantages

KQL-on-Log-Analytics is a specific set of trade-offs; knowing them tells you when to lean on it and when a different tool fits better.

Where each matters: the expressiveness is decisive for incident attribution and security hunting, where the question is novel and a fixed dashboard can’t answer it. The performance-is-on-you caveat bites hardest on busy, paid workspaces and on alert rules that run on a schedule — a wasteful query that’s merely annoying interactively becomes a recurring cost and latency problem when it runs every five minutes forever. And the one-language-across-surfaces advantage compounds: the summarize/join/make-series muscle memory you build against App Insights is the same skill that hunts in Sentinel and inventories with Resource Graph.

Hands-on lab

This lab runs entirely in the Log Analytics Logs blade against data you likely already have (App Insights AppRequests, or VM Heartbeat/Perf). If a table is empty, swap in one you do have — the operators are identical. No resources are created, so there is nothing to tear down.

Step 1 — Open the editor and confirm data. Portal → your Log Analytics workspace → Logs. Sanity check:

AppRequests
| where TimeGenerated > ago(1h)
| take 5

Expected: up to five raw request rows. If empty, try Heartbeat | where TimeGenerated > ago(1h) | take 5 and use Heartbeat/Computer in place of request fields below.

Step 2 — Inspect the shape with getschema. Learn the columns and types before you aggregate:

AppRequests
| getschema
| project ColumnName, ColumnType

Expected: a list of columns (Name, Success, DurationMs, ResultCode, OperationId, TimeGenerated, …) — how you discover field names without guessing.

Step 3 — Your first summarize. Count and failure-rate per endpoint over a day:

AppRequests
| where TimeGenerated > ago(24h)
| summarize total = count(), failed = countif(Success == false) by Name
| extend failRate = round(100.0 * failed / total, 2)
| order by total desc

Expected: one row per operation, with totals, failures and a percentage. Note that failRate is computed after aggregation.

Step 4 — Add time-bucketing with bin(). Turn it into a time series and chart it:

AppRequests
| where TimeGenerated > ago(6h)
| summarize requests = count(), failed = countif(Success == false) by bin(TimeGenerated, 5m)
| render timechart

Expected: a line chart, one point per 5-minute bucket. Switch the bin to 1m (more, noisier points) then 1h (fewer, smoother) — the grain trade-off live.

Step 5 — Latency percentiles. Replace the average instinct with the honest metric:

AppRequests
| where TimeGenerated > ago(6h)
| summarize p50 = percentile(DurationMs, 50),
            p95 = percentile(DurationMs, 95),
            p99 = percentile(DurationMs, 99)
        by bin(TimeGenerated, 15m)
| render timechart

Expected: three latency lines. The gap between p50 and p99 is your tail; a rising p99 with a flat p50 means “most requests fine, some users suffering.”

Step 6 — A join to correlate. Count exceptions per failing endpoint (explicit kind=inner):

AppRequests
| where TimeGenerated > ago(3h) and Success == false
| project OperationId, Name
| join kind=inner (
    AppExceptions
    | where TimeGenerated > ago(3h)
    | project OperationId, ExceptionType = Type
  ) on OperationId
| summarize exceptions = count() by Name, ExceptionType
| order by exceptions desc

Expected: a table of endpoint × exception-type counts. (If AppExceptions is empty, skip — but read the query; the pattern is the lesson.)

Step 7 — A leftanti gap-finder. Find machines that went silent (use Heartbeat):

Heartbeat
| where TimeGenerated > ago(24h)
| distinct Computer
| join kind=leftanti (
    Heartbeat | where TimeGenerated > ago(15m) | distinct Computer
  ) on Computer

Expected: computers seen in the last day but not the last 15 minutes — i.e. recently-silent hosts. An empty result means everything is reporting (good).

Step 8 — A dense series with make-series + anomaly detection.

AppRequests
| where TimeGenerated > ago(7d)
| make-series reqs = count() default = 0 on TimeGenerated from ago(7d) to now() step 1h
| extend (anomalies, score, baseline) = series_decompose_anomalies(reqs, 1.5)
| render anomalychart with (anomalycolumns = anomalies)

Expected: a request-volume curve with anomalous hours highlighted. Compare to Step 4’s summarize+bin chart and notice the gaps are now filled (default 0).

Step 9 — Save and reuse. Click Save → save as a query, or pin the chart to a dashboard / Azure Workbook. This is how a good incident query becomes a permanent asset rather than a screenshot.

Teardown. Nothing to delete — queries create no resources. Remove any saved query or pinned tile if you want a clean slate.

Common mistakes & troubleshooting

The failure modes that turn a five-minute query into an afternoon. Each is root cause → how to confirm → fix.

1. Query is slow or times out. Root cause: no (or too-wide) time filter, so the engine scans far more shards than needed; or a join on unfiltered tables. Confirm: the query-stats panel shows huge “data scanned” with a tiny result. Fix: add where TimeGenerated > ago(<window>) as the first operator, project before any join, and filter both join inputs.

2. join returns fewer rows than expected. Root cause: the default kind=innerunique de-duplicated the left table on the join key. Confirm: re-run with kind=inner and compare counts; if they differ, dedup was happening. Fix: state the kind explicitly — inner for all matches, leftouter to keep unmatched left rows.

3. Chart shows steady traffic across an outage. Root cause: summarize ... by bin() omits empty buckets, and the timechart connects across them. Confirm: look for missing timestamps in the table form. Fix: use make-series ... default=0 ... step <grain> so every bucket exists.

4. Distinct count is “wrong.” Root cause: dcount is approximate (HyperLogLog). Confirm: compare with summarize by UserId | count (exact, heavier). Fix: accept the ~1% error for scale, or raise accuracy with dcount(UserId, 4); use the exact form only on small inputs.

5. Grouping by a JSON field yields one giant group or weird keys. Root cause: grouping by a raw dynamic value without coercing to a scalar. Confirm: getschema shows the column is dynamic. Fix: wrap it — by tostring(Properties.region).

6. Numbers look off after a parse/extract. Root cause: extracted values are strings until cast. Confirm: getschema shows the new column as string. Fix: cast — toint(), todouble(), todatetime() — before maths or range filters. Prefer parse ... latency:long to type at parse time.

7. Time-of-day buckets don’t match local time. Root cause: Log Analytics bins in UTC. Confirm: compare a known event’s TimeGenerated to local. Fix: shift before binning (TimeGenerated + 5h30m for IST) for display, knowing the raw buckets are UTC.

8. mv-expand multiplies your counts. Root cause: mv-expand legitimately turns one row into N, so counts now reflect array elements, not original events. Confirm: count before and after the mv-expand. Fix: aggregate at the right level — compute per-row counts before expanding, or summarize ... by an original key after.

9. Alert rule fires constantly or never. Root cause: the query’s time window doesn’t match the alert’s evaluation frequency/period, or the threshold is on a raw count that empty buckets distort. Confirm: run the exact alert query manually over the alert’s period. Fix: align the query window to the evaluation period; threshold on a rate, not a raw count.

10. union or wide query returns a sea of columns. Root cause: combining tables with different schemas, or never projecting. Confirm: getschema | count shows dozens of columns. Fix: project the columns you need before union/render; use project-keep/project-away to trim.

11. “Operator/function not supported” in Resource Graph. Root cause: Azure Resource Graph supports a subset of KQL (no make-series, limited joins, paged results). Confirm: the error names the unsupported operator. Fix: rework with supported operators, or move time-series work to Log Analytics; see Azure Resource Graph Cookbook: KQL Queries for Inventory and Drift at Scale.

12. Same subquery computed several times. Root cause: a plain let table expression may be recomputed at each reference. Confirm: the query references one subquery in two+ joins/unions. Fix: wrap it in materialize() so it’s computed once and cached for the query’s lifetime.

Best practices

• Always start with a time filter. where TimeGenerated > ago(<window>) as the first line is non-negotiable on a busy workspace — it’s the difference between scanning a slice and scanning everything.
• Filter early, project early, aggregate late. Discard rows and columns before summarize/join; the engine then does the heavy work on the smallest possible input.
• State the join kind explicitly. Never rely on the innerunique default. Write inner, leftouter, or leftanti so the reader (and you, in six months) knows the intent.
• Put the smaller, hard-filtered table on the right of a join. The right side is the one distributed/broadcast; keep it lean and add hint.shufflekey for big-to-big joins.
• Use percentile, not avg, for latency. Averages hide tails; a healthy p50 with a bad p99 is a real, common, otherwise-invisible state.
• Reach for make-series whenever gaps matter — honest charts, anomaly detection, forecasting. Use summarize ... by bin() only when continuity is guaranteed.
• Coerce dynamic leaves to scalar types (tostring, toint) before grouping, filtering, or maths. Cast parse/extract output too.
• Name and reuse with let; cache with materialize() when a subquery is referenced more than once.
• Pick a sane time grain — ~50–500 buckets across the window. Too fine floods the chart; too coarse hides the spike.
• Turn good incident queries into saved queries / workbook tiles. A query you’ll run again is an asset, not a screenshot.
• Check the query-stats panel (data scanned, CPU) when tuning — it tells you objectively whether a change made the query cheaper.
• Prefer countif/sumif/dcountif over filter-then-count branches. Conditional aggregates are clearer and compute several conditions in one pass.

Security notes

KQL itself is read-only — a query cannot modify or delete data — but what a query can see is governed by the workspace’s access model, and that is where the security thinking lives.

The single most important lever is resource-context RBAC: when access is resource-scoped, the same query run by two users returns different rows based on what each can read, so you grant log visibility by granting resource access rather than blanket workspace read. Setup details for these modes live in How to Create a Log Analytics Workspace: Region, Access Modes and Resource-Context RBAC. Treat any table that might carry PII or credentials as sensitive: restrict it, and fix the logging so secrets never land there — a query control is a backstop, not a substitute for not logging the secret.

Cost & sizing

In Log Analytics you mostly pay for data ingested and retained, not per query — interactive queries on the analytics tier are generally not billed per-query. So the dominant cost lever is what you ingest and how long you keep it. Query shape still matters for capacity and latency, in specific ways:

Right-sizing in practice: the biggest savings come before KQL runs — reduce ingestion (filter chatty diagnostic categories, move high-volume low-query data to Basic Logs, trim retention) and commit to a tier if you sustain ≥100 GB/day. On the query side, what you control is mostly capacity and latency: a well-shaped query finishes fast and doesn’t contend for shared engine resources — which matters most for alert queries that run forever on a schedule. The included allowances (a small monthly ingestion grant, ~31-day retention on many tables) are enough to learn on. For the deeper decision of what even belongs in logs vs metrics — the largest cost lever of all — see Metrics vs Log Analytics: Choosing the Right Store for Speed, Cost and Retention. (Prices vary by region and change; treat figures as order-of-magnitude.)

Interview & exam questions

These map to AZ-104 (Monitor and maintain Azure resources), AZ-400 (instrument an app, observability), AZ-700 (network monitoring with logs), and SC-200 (KQL for Microsoft Sentinel hunting).

Q1. What does summarize count() by bin(TimeGenerated, 5m) produce, and why might a chart of it mislead? One row per 5-minute bucket that contains data. It misleads because empty buckets are omitted, so a timechart draws a straight line across gaps as if traffic were continuous. Use make-series ... default=0 when honest gaps matter.

Q2. KQL’s default join kind is innerunique. How does it differ from inner, and why is the difference dangerous? innerunique first de-duplicates the left table on the join key, then matches; inner keeps every matching pair. The danger is silent undercounting — a bare join looks like a SQL inner join but drops left-side duplicates, with no error. Always state the kind.

Q3. When would you use leftanti? To find left rows with no match on the right — the “what’s missing” query: machines that stopped sending heartbeats, resources without a required tag. It returns left columns only, like WHERE NOT EXISTS.

Q4. Why is dcount() described as approximate, and how do you make it more accurate? It uses HyperLogLog to count distinct values without storing them all, trading ~1–2% error for billion-row scale. Raise accuracy with a second argument, dcount(col, 4); for an exact count on small data use summarize by col | count.

Q5. What is the difference between summarize and make-series? summarize emits rows only for groups that exist (sparse), returning scalars; make-series emits a value for every time bucket in an explicit range, filling gaps with a default, returning arrays. Use make-series for dense charts and anomaly/forecast functions.

Q6. Why does operator order matter for performance? Give the cardinal rule. Each operator processes the table the previous one emitted, and storage is columnar and time-partitioned. The rule is filter early, aggregate late: a leading where TimeGenerated > ago(...) prunes whole shards, then summarize/join run on the smallest possible data.

Q7. How do you extract a value from a JSON dynamic column and group by it safely? Reach in with dot/bracket notation (Properties.region) and coerce to a scalar before grouping: summarize count() by tostring(Properties.region). Grouping by a raw dynamic can behave unexpectedly; the cast fixes it.

Q8. What does arg_max(TimeGenerated, *) give you, and what problem does it solve elegantly? The entire row (all columns, via *) with the maximum TimeGenerated per group — the idiomatic “latest record per entity” (summarize arg_max(TimeGenerated, *) by ResourceId), replacing a self-join or window function with one line.

Q9. A Log Analytics query scans huge data for a tiny result. What’s the first fix? Add a time filter as the first operator (where TimeGenerated > ago(<window>)) so the engine prunes shards, and project only needed columns before any join. The query-stats panel (data scanned) confirms the improvement.

Q10. When should materialize() be used? When a subquery (a let-bound table expression) is referenced more than once — joins, unions, multiple branches. materialize() computes it once and caches the result for the query’s lifetime, avoiding repeated recomputation.

Q11. Across which Azure services does KQL apply, and what’s a key caveat when porting a query? Log Analytics, Application Insights, Azure Resource Graph, Microsoft Sentinel, and Azure Data Explorer. The caveat: the data and operator support differ — e.g. Resource Graph supports only a subset (no make-series, limited joins, current-state not time-series), and time columns differ (TimeGenerated vs timestamp).

Q12. How does a Log Analytics scheduled alert relate to KQL, and what’s a common shaping bug? A scheduled alert is a KQL query run on a cadence; the result (rows or an aggregate) is compared to a threshold. A common bug is a mismatch between the query’s time window and the alert’s evaluation period/frequency, or thresholding a raw count that empty buckets distort — align the window and prefer rate/make-series-based stability.

Quick check

1. You want “requests per minute, exactly, including minutes with zero traffic, as a chart.” Which operator do you reach for, and why not the obvious summarize?
2. A colleague’s join (no kind= specified) is undercounting. What is the most likely cause and the one-word fix to the query?
3. You need the most recent heartbeat row (all columns) for each machine. Write the summarize clause.
4. Your daily rollup buckets at a strange local hour. What’s the underlying reason?
5. Name the cardinal performance rule for KQL in five words.

Answers

1. make-series (with default=0 and an explicit step). summarize ... by bin() omits empty buckets, so the chart would connect across gaps and falsely imply steady traffic; make-series fills every bucket.
2. The default join kind is innerunique, which de-duplicates the left table on the join key. The fix is to specify the kind explicitly — write kind=inner (or leftouter).
3. summarize arg_max(TimeGenerated, *) by Computer — arg_max(..., *) returns the entire winning row per group.
4. Log Analytics stores and bins in UTC, so bin(TimeGenerated, 1d) produces UTC days, which won’t align with the viewer’s local “today” unless you shift the timestamp before binning.
5. “Filter early, aggregate late.” (Equivalently: time-filter and project before summarize/join.)

Glossary

• KQL (Kusto Query Language) — The read-only, pipeline-structured query language behind Log Analytics, Application Insights, Resource Graph, Sentinel and Azure Data Explorer.
• Pipeline — A query expressed as a source table followed by operators chained with |, each consuming and emitting a table.
• TimeGenerated — The standard time column on workspace tables; filtering on it enables time-partition (shard) pruning. (App Insights uses timestamp.)
• summarize — The aggregation operator: one output row per distinct grouping-key combination, with aggregate columns.
• bin() — Rounds a value (usually a datetime) down to the nearest multiple of a grain, enabling exact time-bucketing.
• dcount — Approximate distinct count (HyperLogLog); fast at scale, ~1–2% error, tunable with a second accuracy argument.
• percentile — Approximate n-th percentile; the honest latency metric (p50/p95/p99) versus a misleading average.
• join — Correlates two tables on a key; the kind determines treatment of unmatched and duplicate rows.
• innerunique — The default join kind: de-duplicates the left table on the join key before matching; the most common KQL surprise.
• leftouter / leftanti / leftsemi — Keep-all-left (nulls on right) / left-rows-without-a-match (the “gap” query) / left-rows-that-have-a-match (existence).
• make-series — Produces a dense, gap-filled time series (arrays) over an explicit range with a fill default; feeds charts and anomaly/forecast functions.
• dynamic — A JSON-typed column; read fields with dot/bracket notation and coerce leaves to scalar types before use.
• let / materialize() — Bind a name to a value/table/function for reuse / cache a subquery so it’s computed once within a query.
• Resource-context RBAC — An access model where query results are scoped to the resources a user can read, so the same query returns different rows per user.

Next steps

• Harden the workspace these queries run against, including access modes and resource-context RBAC: How to Create a Log Analytics Workspace: Region, Access Modes and Resource-Context RBAC.
• Decide which questions belong in logs versus metrics: Metrics vs Log Analytics: Choosing the Right Store for Speed, Cost and Retention.
• Put your queries to work end-to-end across requests, dependencies and traces: Azure Monitor and Application Insights: Full-Stack Observability.
• Turn a good query into a firing alert with the right thresholds: How to Create Your First Metric Alert and Action Group for Email, SMS and Push.
• Apply the same language to control-plane inventory and drift: Azure Resource Graph Cookbook: KQL Queries for Inventory and Drift at Scale.