KQL for Azure Monitor and Log Analytics: From Joins to Time-Series, Without Blowing the Budget

You open Log Analytics to answer one question — “which endpoint started throwing 500s at 14:30?” — and forty minutes later you are still fighting the query, not the incident. The join returned a tenth of the rows you expected. The time chart is spiky in a way that looks like an outage but is really gap-free-versus-gappy buckets. The summarize is aggregating on a column you meant to group by. And the Stats pane says you scanned 380 GB to answer a question that lived in the last fifteen minutes. Kusto Query Language (KQL) — the read-only, pipeline-based query language behind Azure Monitor, Log Analytics, Microsoft Sentinel, Azure Resource Graph, and Application Insights — is easy to write and easy to write wrongly, and the two failure modes are correctness (the answer is subtly false) and cost (the answer is right but you paid for a full-retention table scan to get it).

This is the working tour of the language that actually matters when you run production observability on Azure. Not a syntax dump — the operators, in the order a real query flows: filter and shape rows (where, project, extend), collapse them (summarize, bin, make-series), correlate across tables (join with its six kinds, union, lookup), tear apart semi-structured payloads (parse, parse_json, mv-expand, mv-apply), reason over time (series_decompose_anomalies, series_decompose_forecast), package logic for reuse (let, stored functions, materialize), and steer the engine when the default plan is wrong (hint.strategy=shuffle, hint.strategy=broadcast). Woven through every section are the two things a senior engineer internalises that a beginner does not: order is performance (the columnstore rewards a TimeGenerated filter first and a narrow project early), and the default is often not what you want (the default join is innerunique, not relational inner, and it silently changes your counts).

By the end you will write KQL that is both correct and cheap: you will know which join kind matches the relational semantics you meant, when bin() lies and make-series … default=0 tells the truth, how mv-expand multiplies rows and where to put it, why materialize() turns three scans into one, and how the same query drops into a scheduled query rule to page someone. Because this is a reference you will return to mid-incident and mid-optimization, the operators, the join matrix, the string functions, the performance rules, the workspace plans and the alert knobs are all laid out as scannable tables — read the prose once, then keep the tables open.

What problem this solves

Every Azure workload emits telemetry, and almost all of it lands in a Log Analytics workspace as tables you query with KQL. The alert that pages you, the dashboard your director watches, the Sentinel detection that catches an attacker, the Usage query that explains a doubled bill — all of it is KQL. Get the language wrong and the failure is quiet: no exception, no red banner, just a chart that misleads, a join that undercounts, an alert that never fires because the query returned zero rows for the wrong reason, or a monthly bill that climbs because a saved query scans a month to answer a question about an hour.

What breaks without real KQL fluency is trust and money. Trust, because a dashboard built on contains scans and default innerunique joins produces numbers that don’t reconcile with reality, and once an on-call engineer stops believing the dashboard they stop looking at it. Money, because Log Analytics is billed per GB ingested and Basic Logs is billed per GB scanned at query time — an unbounded union * or a contains on a hot table is not just slow, it is a line item. And correctness under pressure, because the queries that matter most run during incidents, when a subtly wrong join or a bin-not-make-series baseline sends you chasing the wrong instance for twenty minutes.

Who hits this: platform and SRE teams querying Azure Monitor daily; security analysts writing Sentinel detections and hunting queries; anyone who owns a Log Analytics bill; developers reading Application Insights requests/exceptions/dependencies. It bites hardest on high-cardinality data (per-request OperationId, per-user IDs), cross-table correlation (request → exception → dependency), semi-structured payloads (JSON blobs in AzureDiagnostics, custom AppTraces), and time-series analytics (anomaly detection, forecasting) where the difference between bin and make-series is the difference between a real baseline and noise. The fix is never “learn more syntax” in the abstract — it is knowing, per operator, what it does, what the default is, when it’s wrong, and what it costs.

To frame the whole language before the deep dive, here is the KQL pipeline as a set of stages, the operators that live in each, and the one rule that governs the stage:

Learning objectives

By the end of this article you can:

• Read and write the KQL tabular pipeline fluently — order operators for both correctness and cost, and explain why TimeGenerated-first and early project are the two highest-leverage performance moves on a columnstore.
• Use where, project, project-away, project-rename and extend correctly, and know when has beats contains, when a filter defeats the index, and when to compute versus when to filter.
• Master summarize — every common aggregate (count, countif, dcount, sum, avg, percentiles, arg_max/arg_min, make_list/make_set) — and the difference between bin() for tables and make-series … default=0 for gap-filled series.
• Pick the right join kind from all six (innerunique, inner, leftouter, rightouter, fullouter, leftsemi, leftanti, rightsemi, rightanti) and explain why the default innerunique silently changes counts.
• Steer the query engine with hint.strategy=shuffle (high-cardinality keys) and hint.strategy=broadcast / lookup (a small dimension table), and know which the engine already picks for you.
• Tear apart semi-structured data with parse, extract/extract_all, parse_json/todynamic, mv-expand and mv-apply, and place row-multiplying operators where they don’t blow up cardinality.
• Reason over time with make-series, series_decompose_anomalies and series_decompose_forecast, and know when the seasonal-decomposition assumptions hold and when they lie.
• Package logic with let, query-scoped and stored functions, and materialize() — and turn a finished query into a scheduled query rule (log alert) with dimensions, windows and thresholds, provisioned as Bicep.

Prerequisites & where this fits

You should already know the Azure Monitor data model at a high level: a Log Analytics workspace is a collection of tables, each with a fixed, typed schema, into which Azure resources send telemetry via diagnostic settings and the Azure Monitor Agent. You should be comfortable in the Log Analytics query editor (or az monitor log-analytics query), understand that every log table carries a TimeGenerated datetime column, and know the difference between metrics (pre-aggregated numeric time series, queried with the metrics API) and logs (raw typed rows, queried with KQL). Basic HTTP/status-code literacy and a working mental model of a relational join help.

This sits in the Observability track and is the query-language backbone under most of it. It assumes the platform context from The Azure Monitor Data Platform Explained: Metrics, Logs, Traces and Where Each Lives and pairs directly with Metrics vs Log Analytics: Choosing the Right Store for Speed, Cost and Retention, which decides what lands in KQL-queryable tables in the first place. The workspace it queries is designed in How to Create a Log Analytics Workspace: Region, Access Modes and Resource-Context RBAC, and the telemetry it reads arrives via Data Collection Rules and the Azure Monitor Agent and How to Wire Diagnostic Settings. Once your KQL is right, it powers alerts — How to Create Your First Metric Alert and Action Group and Killing Alert Noise: Tuning Thresholds, Suppression and Flapping — and, in security, KQL Threat Hunting Playbooks: MITRE ATT&CK Mapping, UEBA, and Hunting Notebooks. The same language, with a different resource surface, drives Azure Resource Graph Cookbook: KQL Queries for Inventory and Drift at Scale.

A quick map of where KQL is used across Azure, because the dialect and the constraints differ by surface:

Core concepts

Six mental models make every later operator obvious.

KQL is a read-only pipeline, and order is performance. A query is a source table followed by a chain of |-separated operators; each takes a tabular input and produces a tabular output, left to right. Nothing mutates data — you cannot UPDATE or DELETE. Because each stage feeds the next, where you put an operator changes how much data the next stage reads. Filtering rows early (where TimeGenerated > ago(1h)) means every downstream operator sees fewer rows; narrowing columns early (project) means less compressed data comes off disk. This is not stylistic — on a columnstore it is the single biggest lever on both latency and cost.

The store is a time-partitioned columnstore, and that dictates the two golden rules. Log Analytics stores each table partitioned by ingestion time and compressed per column. Two consequences drive almost every performance rule you will ever apply. First, filter on TimeGenerated first: the engine uses time to prune whole partitions (“data shards”) before reading anything, so a tight time window can skip 99% of the table. Second, select fewer columns: reading 5 columns instead of 40 reads a fraction of the compressed bytes off disk. Every other tip (has over contains, don’t wrap the filtered column in a function) is a corollary of “help the engine skip data.”

summarize is the workhorse, and the shape of its output is exact. summarize collapses many rows into one row per group. Everything before by is an aggregation function (count(), sum(x), dcount(id)); everything after by is a grouping key. The output has one column per aggregate plus one per key, and one row per distinct key combination. Get this shape wrong — put a key where an aggregate goes, or forget the by — and the result is not an error, it is a wrong-but-plausible table. Most “the numbers look off” bugs are a summarize whose grouping keys don’t match what you meant.

A time series is not just a summarize by bin(). Two ways to bucket by time look similar and behave differently. summarize count() by bin(TimeGenerated, 5m) produces a table with one row per non-empty bucket — empty buckets are simply absent. make-series count() default=0 on TimeGenerated step 5m produces vector-valued series — arrays of values and times, with gaps filled by the default. The series_* analytics functions (anomaly detection, forecasting) require the gap-filled, regular series from make-series; feed them a gappy bin result and the baseline is distorted because missing buckets read as “no data,” not “zero.”

A join is a cardinality bomb with a default you didn’t choose. join correlates rows from two tables on a key. Its behaviour is governed by kind=, and the default is innerunique, which first de-duplicates the left table’s key values, then joins — so a genuine one-to-many relationship can silently collapse to one-to-one and undercount. A relational inner join is kind=inner. Beyond the kind, a join reads the right table into memory to build a hash lookup, so a fat right side is the classic out-of-memory or slow join; you filter and project both sides and put the smaller table on the right. The join is where both correctness bugs (wrong kind) and cost/OOM bugs (fat right side, high-cardinality key) live.

dynamic and semi-structured data need explicit parsing and casting. Real logs carry strings and JSON, not clean columns. A dynamic-typed column (or a string parsed with parse_json) is a tree you index into (props.endpoint), and leaf values are untyped until you cast them (toint(props.statusCode), tostring(props.region)). Comparing or aggregating an uncast dynamic leaf silently does the wrong thing. When a JSON field is an array, mv-expand fans it to one row per element — which multiplies your row count, so it belongs after your filters, not before.

The vocabulary in one table

Before the deep sections, pin down every moving part. The glossary at the end repeats these for lookup; this table is the mental model side by side:

The tabular pipeline and how a query is read

A KQL query reads top to bottom, left to right: name a table, then pipe it through operators. The first non-trivial skill is reading a query the way the engine executes it — as a stream of tabular results, each narrower or smaller than the last.

AppRequests
| where TimeGenerated > ago(1h)     // 1. source, then prune by time
| where Success == false            // 2. high-selectivity equality
| where Url has "/api/checkout"     // 3. indexed term match on survivors
| project TimeGenerated, Name, DurationMs, ResultCode, OperationId  // 4. narrow columns
| extend DurationSec = DurationMs / 1000.0  // 5. compute on the small set
| sort by DurationMs desc           // 6. order the result
| take 20                           // 7. cap what returns

Each stage matters, and the order is deliberate. Before you can query a table you have not seen, discover its shape and where data lives:

AppRequests | getschema           // column names, types, ordinal

union withsource=SourceTable *    // which tables hold data, and how much
| where TimeGenerated > ago(1d)
| summarize Rows = count() by SourceTable
| sort by Rows desc

getschema returns the column list with types — indispensable before parsing or casting. The bounded union is the discovery pattern: withsource=SourceTable tags each row with its origin table so you can see which tables are populated. Run union * only while exploring and always bound it with a time filter — without one you ask the engine to touch every table for the full retention window, the single most common way people accidentally scan terabytes.

Here is the operator reference you will reach for constantly — what each does and the gotcha that separates a fast query from a slow one:

Filtering and shaping fast: where, project, extend

The filter/shape stage is where you win or lose most of the query’s cost. Cut rows before anything expensive, narrow columns before you read them, and never help the engine less than you have to.

Filtering: put the cheap, selective predicates first

AppRequests
| where TimeGenerated between (ago(2h) .. ago(1h))   // bounded window, both ends
| where Success == false                             // equality, high selectivity
| where ResultCode in ("500", "502", "503")          // set membership, indexed
| where Url has "/api/checkout"                       // whole-term match on survivors

The ordering is not cosmetic. Time first prunes partitions. Then equality and in on high-selectivity columns cut the surviving rows sharply. String matching (has, contains) runs last, on the smallest set. The distinctions that matter most:

Two rules save more query time than any other. Never wrap the filtered column in a function: where toupper(Name) == "GET /" forces the engine to compute toupper on every row and defeats the index — filter the raw column, or use case-insensitive operators (=~, has). And has beats contains by orders of magnitude on a hot table, because has uses the term index while contains is an unindexed substring scan; on a busy AppTraces, that is the difference between two seconds and two minutes.

Shaping: project narrows, extend computes

project is the column-pruning lever — list exactly the columns you need, in the order you want them:

AppRequests
| where TimeGenerated > ago(1h)
| project TimeGenerated, Name, DurationMs, ResultCode, OperationId

Use the project family precisely: project-away to drop a few fat columns while keeping the rest; project-rename for a pure rename (cheaper than extend); project-keep with wildcards to keep a family of columns. extend adds computed columns and must come after you have shrunk the row set, because it runs its expression on every surviving row:

AppRequests
| where TimeGenerated > ago(1h)
| project TimeGenerated, Name, DurationMs, ResultCode
| extend
    DurationSec = DurationMs / 1000.0,
    Is5xx       = toint(ResultCode) >= 500,
    Endpoint    = tostring(split(Name, " ")[-1])

The shaping operators and when each is the right tool:

summarize, dcount, arg_max: aggregations that don’t lie

summarize is where rows become insight. The shape rule is absolute: aggregation functions before by, grouping keys after.

AppRequests
| where TimeGenerated > ago(24h)
| summarize
    Total    = count(),
    Failures = countif(Success == false),
    Users    = dcount(UserId),
    (P50, P95, P99) = percentiles(DurationMs, 50, 95, 99)
  by Name
| extend FailureRate = round(100.0 * Failures / Total, 2)
| sort by P95 desc

countif() counts a subset in the same pass without a separate branch. percentiles() returns several approximate-but-cheap percentiles at once — fine for SLOs, and far cheaper than exact percentiles. dcount() is an approximate distinct count using HyperLogLog — fast and memory-bounded on high-cardinality columns like OperationId or UserId, at the cost of a small error (a few percent); use count(distinct …)-style exactness only when you truly need it, because exact distinct on millions of values is expensive.

The aggregation functions you will actually use, and what each is for:

arg_max / arg_min deserve special mention because they solve a problem people otherwise butcher: “give me the whole latest record per resource.” summarize arg_max(TimeGenerated, *) by ResourceId returns, for each ResourceId, the entire row with the newest TimeGenerated — the canonical “latest state per entity” query, used everywhere from “current status per VM” to “most recent signin per user.”

bin: time-series buckets as a table

To turn rows into a time series table, group by a time bucket with bin():

AppRequests
| where TimeGenerated > ago(6h)
| summarize Requests = count(), AvgMs = avg(DurationMs)
    by bin(TimeGenerated, 5m), Name
| render timechart

bin(TimeGenerated, 5m) rounds each timestamp down to a 5-minute boundary, so count() becomes requests-per-5-minutes. render timechart is a portal hint that visualises the result — it changes no data and is ignored by the API — but it sanity-checks the shape instantly. The critical limitation: bin produces no row for an empty bucket. If no requests landed between 03:15 and 03:20, that bucket is simply absent from the result. For a chart the portal interpolates and it looks fine; for downstream math or anomaly detection, the missing bucket is a hole, not a zero — which is exactly why you reach for make-series when a series_* function is next.

The time-bucketing choices and when each fits:

Joins, unions, and lookups across tables

Correlation across tables is where KQL gets both powerful and dangerous. Three operators do it: union stacks tables with compatible schemas; join correlates rows on a key; lookup enriches from a small dimension table.

The join kinds — pick deliberately, because the default surprises you

A typical join ties a failed request to the exception it threw, by operation:

AppExceptions
| where TimeGenerated > ago(1h)
| project OperationId, ProblemId, ExceptionType = Type, ExceptionMessage = OuterMessage
| join kind=inner (
    AppRequests
    | where TimeGenerated > ago(1h)
    | where Success == false
    | project OperationId, Name, Url, DurationMs
  ) on OperationId

Two mechanics decide whether a join is correct and survivable. First, filter and project both sides first: the engine reads the right table into memory to build the hash table, so a fat right side is the classic slow/OOM join — put the smaller table on the right. Second, pick the kind deliberately, because the default innerunique de-duplicates the left key before joining and surprises anyone who expected relational inner.

The complete join-kind matrix — memorise the top four, know the rest exist:

leftanti earns a callout: it cleanly replaces awkward NOT-IN logic. The canonical use is silent-host detection — take distinct Computer from Heartbeat over the last hour, join kind=leftanti against distinct Computer from the last 15 minutes, and what survives is the machines that went quiet:

Heartbeat
| where TimeGenerated > ago(1h)
| distinct Computer
| join kind=leftanti (
    Heartbeat
    | where TimeGenerated > ago(15m)
    | distinct Computer
  ) on Computer
// result: computers seen in the last hour but NOT the last 15 min → went silent

The failure modes that make joins wrong or slow, and how to avoid each:

union — stack compatible tables

union concatenates tables. It is the tool for “search across all my tables” and for combining shards of the same logical data (e.g., an old and a new table version):

union withsource=SourceTable (AppRequests), (AppDependencies)
| where TimeGenerated > ago(1h)
| where Success == false
| summarize Failures = count() by SourceTable, bin(TimeGenerated, 5m)

withsource=SourceTable tags each row with its origin — essential when the union spans tables with overlapping schemas. Prefer an explicit, parenthesised list of tables over union *; if you must wildcard (e.g. union App*), still bound it with a time filter. Column resolution follows a union mode: by default columns present in some but not all tables become nullable in the output.

lookup — the right way to enrich from a small dimension table

For small, static lookups (region maps, owner tables, watchlists), use lookup with an inline datatable. The engine broadcasts the dimension table to every node rather than shuffling the big table — far cheaper than a join for this pattern:

let owners = datatable(Service:string, Team:string) [
    "checkout", "payments",
    "search",   "discovery",
    "profile",  "identity"
];
AppRequests
| where TimeGenerated > ago(1h)
| extend Service = tostring(split(Url, "/")[1])
| lookup kind=leftouter owners on Service
| summarize Requests = count() by Team

lookup supports kind=leftouter (default — keep all left rows) and kind=inner. Reach for it whenever the right side is a small, static, or slowly-changing dimension; reach for join when both sides are large fact tables.

When to use which correlation operator:

Steering the engine: shuffle and broadcast hints

Most of the time the engine picks a good plan. When it doesn’t, two hint.strategy values change how a join (or summarize) is distributed across the cluster’s nodes.

hint.strategy=shuffle repartitions both sides by the join key so each node handles a slice of the key space. It is the fix when the join key has very high cardinality (millions of distinct values) and a single-node join runs out of memory or time. You can also hint the number of partitions with hint.shufflekey=<column>:

AppRequests
| where TimeGenerated > ago(1d)
| join hint.strategy=shuffle (
    AppDependencies
    | where TimeGenerated > ago(1d)
  ) on OperationId

hint.strategy=broadcast sends the (small) left side to every node holding the right side — the same idea lookup uses automatically, but expressed on a join when the left side is genuinely small (roughly under ~100k rows):

SmallLeftTable            // a small, filtered result
| join hint.strategy=broadcast (
    HugeRightTable
    | where TimeGenerated > ago(1h)
  ) on Key

The rule of thumb: broadcast when one side is small; shuffle when both sides are large and the key is high-cardinality. The strategies at a glance:

Parsing semi-structured logs

Real logs are rarely fully columnar. KQL gives you a ladder of parsing tools, from the cheapest and most structured to the most flexible and slowest.

parse — extract named fields by example pattern

parse reads a string and pulls named, typed fields out of it by an example pattern:

AppTraces
| where TimeGenerated > ago(1h)
| parse Message with "user=" UserId " action=" Action " ms=" Latency:long
| where isnotempty(UserId)
| project TimeGenerated, UserId, Action, Latency

parse is fast and readable when the format is stable. Its trap: on a non-matching line it silently sets the fields to empty, not an error — so guard with isnotempty() before aggregating, or you count blanks. Use parse-where (which drops non-matching rows) when you only want lines that fit the pattern.

extract and extract_all — regex capture

When the format is irregular, fall back to extract (one capture group) or extract_all (all matches):

AppTraces
| where TimeGenerated > ago(1h)
| extend StatusCode = extract(@"status=(\d{3})", 1, Message)
| where isnotempty(StatusCode)
| summarize count() by StatusCode

Regex is the flexible-but-slow rung; prefer parse when the format allows it.

parse_json — turn a string into a navigable dynamic

For JSON, parse the string into a dynamic object with parse_json (alias todynamic), then index into it, casting leaf values to a concrete type before comparing or aggregating:

AzureDiagnostics
| where TimeGenerated > ago(1h)
| extend props = parse_json(properties_s)
| extend
    Endpoint = tostring(props.endpoint),
    Code     = toint(props.statusCode),
    Region   = tostring(props.region)
| summarize count() by Endpoint, Code

The most common waste here: if a column is already dynamic (many Azure tables store JSON natively — check with getschema), skip parse_json and index it directly; re-parsing an already-parsed object is pure overhead. And always cast the leaf — props.statusCode is untyped dynamic; comparing it to an integer without toint() does something you didn’t intend.

The type-cast functions you’ll pair with parsing:

mv-expand and mv-apply — arrays into rows

When a JSON field is an array, mv-expand fans it to one row per element so you can aggregate nested data:

AppTraces
| where TimeGenerated > ago(1h)
| extend payload = parse_json(Message)
| mv-expand item = payload.items
| extend Sku = tostring(item.sku), Qty = toint(item.qty)
| summarize Units = sum(Qty) by Sku

mv-expand multiplies rows — a 10-element array becomes 10 rows — so apply it after filtering, never before, or you explode the working set. mv-apply is the variant that runs a sub-query per array: use it when you need to filter, sort, or aggregate within each row’s array before flattening. For example, “the single highest-quantity item per trace”:

AppTraces
| where TimeGenerated > ago(1h)
| extend payload = parse_json(Message)
| mv-apply item = payload.items on (
    top 1 by toint(item.qty) desc
  )
| extend TopSku = tostring(item.sku), TopQty = toint(item.qty)

The parsing ladder, cheapest and most structured to most flexible and slowest:

Time-series analytics: make-series and the series functions

summarize … by bin() gives a table; make-series gives vector-valued series with gap-filling, which the analytics functions require.

let step = 10m;
AppRequests
| where TimeGenerated > ago(2d)
| make-series Reqs = count() default = 0
    on TimeGenerated step step by Name

default = 0 is the important part: missing buckets become 0 instead of holes, so downstream math does not silently skip them. The result is one row per Name, each carrying a value array (Reqs) and a matching time array (TimeGenerated). On top of that structure, the series_* functions operate.

Anomaly detection — series_decompose_anomalies splits the series into seasonal + trend + residual and flags points the residual cannot explain:

let step = 10m;
AppRequests
| where TimeGenerated > ago(7d)
| make-series Reqs = count() default = 0
    on TimeGenerated step step
| extend (anomalies, score, baseline) =
    series_decompose_anomalies(Reqs, 1.5)
| render anomalychart

The second argument is the sensitivity threshold in residual standard deviations; lower is more sensitive. It returns three series — a flag (-1/0/1 for negative/none/positive anomaly), a score, and the computed baseline.

Forecasting — swap in series_decompose_forecast on the same make-series output to project points ahead (here, 24 steps into the future):

let step = 1h;
AppRequests
| where TimeGenerated > ago(14d)
| make-series Reqs = count() default = 0
    on TimeGenerated from ago(14d) to now() + 1d step step
| extend Forecast = series_decompose_forecast(Reqs, toint(1d / step))
| render timechart

Both assume a roughly regular, seasonal signal and overfit on sparse or bursty data — always eyeball the baseline before trusting the flags, and prefer at least one to two full seasonal periods of history. The series toolbox:

The bin-versus-make-series decision, stated once more as a rule because it is the single most common time-series mistake:

Reusing logic: let, functions, and materialize

As queries grow, let, functions, and materialize() keep them readable and fast.

let — name values, tables, and lambdas

let binds a name to a scalar, a tabular expression, or a lambda, scoped to the query:

let lookback = 24h;
let slowThresholdMs = 1000;
let SlowRequests =
    AppRequests
    | where TimeGenerated > ago(lookback)
    | where DurationMs > slowThresholdMs;
SlowRequests
| summarize Slow = count() by Name
| join kind=inner (
    SlowRequests | summarize Total = count() by Name
  ) on Name

let improves readability and lets you reference the same sub-result by name. But a plain let-bound tabular expression is re-evaluated on each reference — SlowRequests above is computed twice. That is where materialize() comes in.

materialize — compute an expensive sub-result once

Wrap a tabular let in materialize() and the engine computes it once, caches the result for the query’s lifetime, and reuses it on every reference:

let SlowRequests = materialize(
    AppRequests
    | where TimeGenerated > ago(24h)
    | where DurationMs > 1000
);
SlowRequests | summarize Slow = count() by Name
| join kind=inner (
    SlowRequests | summarize Total = count() by Name
  ) on Name
| extend Pct = round(100.0 * Slow / Total, 1)

materialize() is the highest-leverage performance tool for any query that references the same sub-result more than once (both sides of a join, a union of aggregates, a fan-out) — it turns N scans into one. The trade-off is memory: the materialized result is held for the query, so materialize a filtered, narrowed sub-result, not a raw table.

let binding types and when materialization helps:

Functions — package logic for reuse across queries

A query-scoped function is a let-bound lambda; a stored function (saved in the workspace) is reusable across all queries and users — the way teams share detection logic, ASIM parsers, and common transformations. Save one via the portal (“Save as function”) or with az:

// Query-scoped function
let failureRate = (T:(Success:bool)) {
    T | summarize Total = count(), Fails = countif(Success == false)
      | extend Rate = round(100.0 * Fails / Total, 2)
};
AppRequests | where TimeGenerated > ago(1h) | invoke failureRate()

# Save a reusable stored function (a "saved search" of category Function) in the workspace
az monitor log-analytics workspace saved-search create \
  --resource-group rg-observability --workspace-name law-prod \
  --saved-search-id "FailedCheckouts" \
  --category "Function" --display-name "FailedCheckouts" \
  --function-alias "FailedCheckouts" \
  --query "AppRequests | where Name has '/checkout' and Success == false"

Once saved with a --function-alias, FailedCheckouts becomes a callable table-valued function in any query in that workspace — the mechanism behind Sentinel’s ASIM normalization and every team’s shared query library.

Common query patterns you’ll reach for constantly

A handful of patterns recur across observability and security. Learn them once; reuse them everywhere.

Top-N per group

“The three slowest endpoints per cloud role” needs top-nested, not a global top:

AppRequests
| where TimeGenerated > ago(1h)
| top-nested 5 of cloud_RoleName by count(),
  top-nested 3 of Name by avg(DurationMs)

top-nested produces the top-K within each outer group — the clean answer to “top N per category,” which people otherwise fake with row-number tricks.

Latest state per entity

Covered above but worth repeating as a pattern: arg_max(TimeGenerated, *) per key returns the whole newest row:

Heartbeat
| where TimeGenerated > ago(1h)
| summarize arg_max(TimeGenerated, *) by Computer   // current state of each host

Sessionization — group events into sessions by an idle gap

Turn a stream of per-user events into sessions, where a gap longer than N minutes starts a new session. Use prev() over an ordered partition:

let idleGap = 30m;
AppPageViews
| where TimeGenerated > ago(1d)
| order by UserId asc, TimeGenerated asc
| extend Gap = TimeGenerated - prev(TimeGenerated)
| extend NewSession = iff(UserId != prev(UserId) or Gap > idleGap, 1, 0)
| serialize SessionId = row_cumsum(NewSession)
| summarize Start = min(TimeGenerated), End = max(TimeGenerated), Events = count()
    by UserId, SessionId

serialize freezes row order so the windowed functions (prev, row_cumsum) are deterministic; row_cumsum running-sums the session-boundary flags into a stable session id.

Funnel — conversion through ordered steps

Count how many users progress through an ordered set of steps:

let window = 1d;
AppEvents
| where TimeGenerated > ago(window)
| where EventName in ("view", "add_to_cart", "checkout", "purchase")
| summarize Steps = make_set(EventName) by UserId
| extend
    Viewed    = set_has_element(Steps, "view"),
    Carted    = set_has_element(Steps, "add_to_cart"),
    CheckedOut= set_has_element(Steps, "checkout"),
    Purchased = set_has_element(Steps, "purchase")
| summarize
    Viewed = countif(Viewed), Carted = countif(Carted),
    CheckedOut = countif(CheckedOut), Purchased = countif(Purchased)

The pattern catalogue, with the operator that powers each:

Query performance and the columnstore

Everything above has a cost dimension. On a columnstore, a query’s price is roughly how much compressed data it must read, and you control that with a small set of rules. First, the rules ranked by impact:

The single most useful skill is reading what a query cost. In the Log Analytics editor, the Stats / query-details pane after a run shows CPU, memory, and — crucially — data scanned. Run a query with and without its time filter and compare the scanned bytes; the difference is the lesson. Programmatically, az monitor log-analytics query returns timing, and the workspace’s own Usage/query-audit tables record heavy queries.

The anti-patterns that quietly cost the most, and the fix for each:

Workspace design and the table plans that shape KQL

KQL performance and cost are inseparable from how the workspace is designed, and the biggest lever is the table plan. Every table in a Log Analytics workspace has a plan that trades query capability for ingestion price:

Setting a verbose, low-value table to Basic sharply cuts its ingestion price, at the cost of interactive querying and alerting — and it changes what KQL you can run against it. Choose per table by how often you query it:

# Move a chatty, rarely-queried table to Basic Logs
az monitor log-analytics workspace table update \
  --resource-group rg-observability --workspace-name law-prod \
  --name ContainerLogV2 --plan Basic

The table-plan implications for how you write KQL:

Two workspace-design facts shape every cross-table query you write. join scope is the workspace — you can join across tables in one workspace freely, but joining across workspaces needs the workspace("name").Table reference. And RBAC can be resource-context or workspace-context, which changes what rows a query returns for a given user — a resource-context reader sees only rows for resources they can access, so the same KQL can return different results per identity. The workspace-referencing functions:

Architecture at a glance

Picture the path a single question takes, from a table on disk to a chart or a pager, because the shape of that path is why the performance and correctness rules exist. At the bottom sits the Log Analytics workspace: a set of tables, each a time-partitioned, per-column-compressed columnstore. A query enters as text and the engine plans it: the time filter on TimeGenerated is read first and prunes whole partitions, so the very next stage — reading columns — touches only the shards that survive. The narrower your project, the fewer compressed column-blocks come off disk. This is the front third of the pipeline, and it is where 90% of the cost is decided.

From there the surviving rows flow through the middle of the pipeline — where predicates using the term index (has) or a full scan (contains), summarize/bin/make-series collapsing rows into groups or gap-filled series, join/lookup/union pulling in a second table (the right side loaded into memory as a hash, or a small dimension broadcast to every node), and parse/parse_json/mv-expand tearing strings and arrays apart (the last of which multiplies rows, which is why it sits after the filters). let and materialize() name and cache sub-results so a fan-out computes once instead of N times.

The tail of the pipeline is where the same query splits into three destinations. Interactively, render hints the portal to draw a chart and the Stats pane reports what you scanned — the feedback loop that teaches you cost. As a scheduled query rule, the identical KQL runs on a timer (evaluationFrequency) over a lookback window (windowSize), and when the aggregated result crosses a threshold it fires an action group — a query becomes a pager. And in Sentinel, the same language runs as an analytics rule against security tables and watchlists, turning a hunt into a detection. One language, one columnstore, three exits: chart, alert, detection. The whole method is: prune by time, read narrow, correlate deliberately, cast what you parse, and know which exit you are writing for.

Real-world scenario

Meridian Freight, a logistics SaaS, runs an event-driven platform on Azure: API Management in front, ~40 App Services and 12 AKS workloads behind, all wired to one Log Analytics workspace (law-meridian-prod) in Central India. The observability team is three engineers; the workspace ingests about 900 GB/day and the monthly Azure Monitor bill sits near ₹9,00,000. Their dashboards are widely used and widely distrusted — the “failure rate by endpoint” tile never matches what customers report, and the on-call SLO burn alert has a reputation for both missing real incidents and paging on nothing.

The incident that forced a reckoning was a Tuesday-morning latency spike. The p95-latency dashboard showed a clean 40 ms baseline with a sudden 900 ms tower at 09:12 — an obvious outage. On-call escalated, three engineers joined a bridge, and twenty-five minutes in nobody could find a matching error spike or a deploy. The “outage” was a query bug: the latency tile used summarize percentile(DurationMs, 95) by bin(TimeGenerated, 1m) over a low-traffic internal admin endpoint mixed into the same tile as the customer API. During a quiet minute the admin endpoint had exactly one slow request, and with bin leaving empty minutes absent, the chart’s autoscaling drew that single 900 ms point as a tower. There was no incident. Twenty-five minutes and three engineers, spent on a bin-versus-make-series artefact and an un-split dimension.

The audit that followed found three systemic KQL problems. First, the failure-rate mismatch: the tile joined AppRequests to AppExceptions with a bare join — the default innerunique — which de-duplicated the request side and undercounted every endpoint with more than one failure per operation, so the dashboard structurally reported fewer failures than reality. Switching to kind=inner and pre-aggregating with summarize before the join reconciled the tile with customer reports on the first try. Second, the SLO burn alert ran AppRequests | summarize … | where BurnRate > 2 on a query that used contains "/api/" on the URL — an unindexed scan of a 200 GB/day table every five minutes — and its own slowness meant it sometimes evaluated stale windows and missed fast burns. Rewriting the filter to Url has "/api/" and bounding the window tightly cut the rule’s runtime from ~40 s to under 3 s. Third, the bill: the top DataType in Usage was a single ContainerLogV2 stream of INFO-level request/response bodies nobody queried interactively — 300 GB/day of the 900.

The fixes landed in two waves. Immediately, the team rewrote the four worst dashboard tiles: make-series … default=0 for every latency and rate chart (no more phantom towers), kind=inner with pre-aggregation for every correlation tile, and has/== filters throughout. Over the next two weeks they moved the verbose ContainerLogV2 body stream to Basic Logs (they still needed it for rare deep-dives, just not interactively) and added a DCR transformation to drop DEBUG at ingestion. Net effect: the latency dashboard stopped crying wolf, the failure-rate tile finally matched reality, the SLO alert caught the next real burn in under a minute, and ingestion fell from 900 to ~560 GB/day — the monthly bill dropped by roughly ₹2,50,000 with no loss of signal. The line the lead wrote on the wall: “A KQL dashboard is only as trustworthy as its worst join kind. Read the query before you trust the chart.”

The incident and audit as a timeline, because the order of realizations is the lesson:

Advantages and disadvantages

KQL’s design — a read-only pipeline over a columnstore, with powerful defaults chosen for the common case — is what makes it fast to write and easy to get subtly wrong. Weigh it honestly:

The language is right for anyone who lives in Azure telemetry — the moment you need cross-table correlation, time-series analytics, or semi-structured parsing, KQL beats exporting to a spreadsheet or a general-purpose tool. It bites hardest on people who treat it like SQL (the join default is different), who build dashboards without reading the underlying queries (phantom towers, undercounts), and who ignore the cost dimension until a doubled bill forces it. Every disadvantage is manageable — but only if you know the default, the silent failure, and the cost of each operator, which is the entire point of this article.

Hands-on lab

Build a small Log Analytics workspace, ingest a bit of custom data, and run the exact patterns above against it — correctness and cost, end to end. Free-tier-friendly (a tiny workspace ingesting a few MB costs pennies; delete at the end). Run in Cloud Shell (Bash).

Step 1 — Variables and resource group.

RG=rg-kql-lab
LOC=centralindia
WS=law-kql-lab-$RANDOM
az group create -n $RG -l $LOC -o table

Step 2 — Create the Log Analytics workspace.

az monitor log-analytics workspace create \
  --resource-group $RG --workspace-name $WS --location $LOC -o table
WSID=$(az monitor log-analytics workspace show \
  --resource-group $RG --workspace-name $WS --query customerId -o tsv)
echo "Workspace GUID: $WSID"

Expected: a workspace row; WSID is the GUID you query against.

Step 3 — Run a schema/discovery query. Use the always-present Heartbeat-style Operation/Usage tables, or query Usage (populated within minutes) to prove the query path works:

az monitor log-analytics query \
  --workspace "$WSID" \
  --analytics-query "Usage | where TimeGenerated > ago(1d) | summarize GB = sum(Quantity)/1024 by DataType | sort by GB desc | take 5" \
  -o table

Expected: a small table of DataType and GB (may be near-zero on a fresh workspace — that is fine; the point is the query executes).

Step 4 — Prove the time-filter cost rule. Run the same aggregate with a tight and a wide window and note the difference in the returned Stats (via --query on the response, or the portal Stats pane):

# Tight window
az monitor log-analytics query --workspace "$WSID" \
  --analytics-query "AzureActivity | where TimeGenerated > ago(1h) | count" -o table
# Wide window (scans far more)
az monitor log-analytics query --workspace "$WSID" \
  --analytics-query "AzureActivity | where TimeGenerated > ago(30d) | count" -o table

The lesson is the mechanism, not the number: the wide query reads far more partitions to answer the same shape of question.

Step 5 — Run each core pattern in the portal editor. Open the workspace → Logs, and run these against AzureActivity (present in every subscription) to exercise the operators:

// summarize + bin (time-series table)
AzureActivity
| where TimeGenerated > ago(7d)
| summarize Ops = count() by bin(TimeGenerated, 1h), OperationNameValue
| render timechart

// make-series + anomaly (the correct time-series form)
AzureActivity
| where TimeGenerated > ago(7d)
| make-series Ops = count() default = 0 on TimeGenerated step 1h
| extend (anomalies, score, baseline) = series_decompose_anomalies(Ops, 1.5)
| render anomalychart

// arg_max: latest activity per caller
AzureActivity
| where TimeGenerated > ago(1d)
| summarize arg_max(TimeGenerated, OperationNameValue, ActivityStatusValue) by Caller

Expected: the first renders a time chart; the second an anomaly chart with a baseline; the third one row per Caller with their newest operation.

Step 6 — Save a reusable function.

az monitor log-analytics workspace saved-search create \
  --resource-group $RG --workspace-name $WS \
  --saved-search-id "RecentFailures" --category "Function" \
  --display-name "RecentFailures" --function-alias "RecentFailures" \
  --query "AzureActivity | where TimeGenerated > ago(1h) and ActivityStatusValue == 'Failure'"

In the editor, run RecentFailures | count — the saved function is now callable like a table.

Validation checklist. You created a workspace, proved the query path, demonstrated the time-filter cost rule, ran summarize/bin, the correct make-series + anomaly form, arg_max for latest-state, and saved a reusable function. The lab steps mapped to what each proves:

Cleanup (avoid lingering charges).

az group delete -n $RG --yes --no-wait

Cost note. A tiny workspace ingesting a few MB during this lab costs well under ₹20; deleting the resource group stops all charges. (Note the 31-day soft-delete on workspaces — the delete removes it from billing immediately.)

Common mistakes & troubleshooting

The KQL failures that cost the most time — as a scannable table you can keep open while you debug, then the reasoning underneath. These are silent failures: the query runs and returns a wrong-but-plausible answer, which is the dangerous kind.

The expanded reasoning for the ones that bite hardest:

1. The join undercounts. The default join is innerunique, which de-duplicates the left table’s join key before matching — so if an operation had three exceptions, an AppRequests↔AppExceptions join keeps one, not three. Confirm: re-run with kind=inner; if the count jumps, innerunique was collapsing it. Fix: always name the kind — inner for relational, leftouter to keep unmatched left rows.

2. Phantom spikes on a time chart. summarize count() by bin(TimeGenerated, 1m) produces no row for a minute with zero events, so gaps are absent, not zero. The portal’s autoscale then draws a lone real point as a dramatic tower. Confirm: the same query as make-series … default=0 shows the true, gap-filled shape. Fix: use make-series … default=0 for any chart where zeros matter and for every series_* input.

3. The query scanned a terabyte. The two culprits are a missing TimeGenerated filter and an unbounded union * (or workspace-wide search). Confirm: the Stats pane’s “data scanned” is huge relative to the answer. Fix: a tight time filter first, always; name tables explicitly instead of union *.

5. Half the parsed rows are blank. parse … with sets the target fields to empty on a non-matching line — no error. Aggregating then counts blanks as a real group. Confirm: summarize Blank = countif(isempty(Field)), Total = count(). Fix: guard with where isnotempty(Field), or use parse-where to drop non-matches, or fix the pattern (a stray space breaks it).

9. The alert that never pages. A scheduled query rule fires when its query returns rows crossing the threshold; if a bad filter or a silently-failing parse makes the query return zero rows, the alert simply never fires — you find out during the incident it was supposed to catch. Confirm: run the exact alert query over its window manually and check it returns the rows you expect during a known-bad period. Fix: test alert queries against historical incidents; assert they return non-empty; alert on the absence of expected data where appropriate.

Best practices

• Time filter first, every interactive query. A TimeGenerated bound is the highest-leverage move on a columnstore — it prunes partitions before any read. Bound both ends (between) for historical windows.
• project early and narrow. Read the five columns you need, not forty. On a columnstore this is a direct, proportional I/O saving.
• has/==, never contains, on term data. Use the term index. Reserve contains/matches regex for genuine mid-term substring needs and expect them to be slow.
• Name the join kind; never ship the default. inner for relational, leftouter to keep unmatched left rows, leftanti for “present-then-absent.” Assume innerunique is wrong until proven otherwise.
• Filter and project both sides of every join, and put the smaller table on the right — the engine reads the right side into memory.
• make-series … default=0, not bin, before any series_* function and for any math across buckets. bin is for charts and tables where absent buckets don’t matter.
• Cast every dynamic leaf (toint, tostring, todouble) before comparing or aggregating, and skip parse_json on columns that are already dynamic.
• Guard parse with isnotempty (or use parse-where). Non-matches fail silently to empty and pollute aggregates.
• materialize() any sub-result referenced more than once — both join sides, fan-outs, unions of aggregates. It turns N scans into one.
• Put mv-expand after your filters. It multiplies rows; expanding before filtering explodes the working set.
• Package shared logic as stored functions. Team detections, ASIM parsers, and common transforms belong in reusable functions, versioned like code.
• Read the query before trusting the chart. A dashboard tile is only as correct as its join kind, its bin-vs-make-series choice, and its filters. Audit tiles the way you audit code.
• Test alert queries against real history. Run the rule’s KQL over a known-bad window and assert it returns rows — an alert that returns zero for the wrong reason never fires.

Security notes

• RBAC changes what a query returns. Log Analytics supports workspace-context (see all rows in the workspace) and resource-context (see only rows for resources you can access) access. The same KQL returns different results per identity under resource-context — design dashboards and alerts knowing this, and grant the narrowest scope that works.
• Least privilege on the workspace. Prefer the built-in Log Analytics Reader for query-only users and Log Analytics Contributor only for those who manage it; use table-level RBAC to restrict sensitive tables (e.g., SecurityEvent, tables with PII) to the roles that need them.
• Redact and minimize at ingestion, not in the query. A DCR transformKql can drop or hash sensitive fields before they are stored, so PII never lands — far safer than relying on every query author to project-away a secret column.
• Sentinel queries touch security-sensitive data. Detections and hunts run over SigninLogs, AuditLogs, SecurityEvent; treat the queries themselves as sensitive (they encode what you monitor and, by omission, what you don’t) and manage them as code with review.
• Don’t leak internal topology through shared queries or functions. A saved function or a shared dashboard exposes table names, field names, and thresholds — scope who can see and edit them.
• Watchlists and lookup tables can carry sensitive lists (high-value assets, VIP users, allow-lists). Store them as Sentinel watchlists with appropriate RBAC, not as inline datatables pasted into every query.
• Immutable audit of queries. For regulated workloads, capture who ran which query (query audit logs) — KQL is read-only but reading sensitive data is itself an access event worth logging.

The security controls that also improve query hygiene:

Cost & sizing

KQL cost has two halves, and both are controllable:

• Ingestion (per GB) dominates the bill. You pay to store data, and Analytics-plan tables cost the most per GB. The biggest levers are upstream of KQL: DCR transformKql to drop DEBUG/noise and strip fat columns before storage, and the table plan — verbose, rarely-queried tables belong on Basic Logs (cheaper ingestion) or Auxiliary (cheapest), not Analytics.
• Query cost on Basic/Auxiliary is per GB scanned. On Analytics, interactive queries are included; on Basic Logs you pay per GB scanned at query time, so a wide-window search over a Basic table is a real charge — narrow the window hard and reach for it only during investigations.
• Commitment tiers discount ingestion. Once daily volume is predictable, move off pay-as-you-go onto a commitment tier (e.g., 100/200/…/GB-per-day) for a per-GB discount; size to your steady-state floor, since overage still bills at the discounted rate.
• A daily cap is a circuit breaker, not a strategy. It protects the budget but drops data once hit — often the very logs you need to debug the spike. Set it above peak, alert before it, and treat hitting it as an incident.
• Usage is your source of truth. Trend GB by DataType weekly; a table that climbs without a feature shipping is a mis-set log level or a chatty new service, not real growth.

# What is each table costing? (the query to run weekly)
az monitor log-analytics query --workspace "$WSID" \
  --analytics-query "Usage | where TimeGenerated > ago(7d) | summarize GB = sum(Quantity)/1024 by DataType | sort by GB desc" \
  -o table

# Set a daily cap (GB) as a backstop, and a commitment tier via the workspace SKU
az monitor log-analytics workspace update \
  --resource-group $RG --workspace-name $WS --quota 200   # daily cap in GB

The cost levers, coarsest to finest, and what each buys:

A rough monthly picture: a workspace ingesting ~500 GB/day on Analytics runs into the low lakhs of INR/month before optimization; moving 30–40% of that volume to Basic Logs and dropping DEBUG at ingestion typically reclaims the first ₹2–3 lakh, and a commitment tier sized to the new floor shaves more. As with Meridian, the fix is usually where the data lands, not a bigger SKU.

Interview & exam questions

1. What is the difference between summarize by bin() and make-series, and when must you use each? bin() inside summarize produces a table with one row per non-empty time bucket — empty buckets are absent. make-series … default=0 produces gap-filled vector series (value + time arrays). You must use make-series before any series_* function (anomaly detection, forecasting) and for any math across buckets, because those need regular, gap-filled input; bin is fine for charts and tables where absent buckets don’t distort the answer.

2. Why does the default KQL join sometimes undercount, and how do you fix it? The default kind is innerunique, which de-duplicates the left table’s join key before matching. A genuine one-to-many relationship collapses to one-to-one and undercounts. Fix by naming the kind explicitly — kind=inner for a relational inner join, or the appropriate outer kind — and by pre-aggregating before the join when you can.

3. Name the two highest-leverage KQL performance rules and why they work. (1) Filter on TimeGenerated first — the columnstore is time-partitioned, so a tight window prunes whole partitions before any read. (2) project early and narrow — the store is per-column-compressed, so reading 5 columns instead of 40 reads a fraction of the bytes. Both are corollaries of “help the engine skip data.”

4. When do you use hint.strategy=shuffle versus hint.strategy=broadcast? broadcast when one side of a join is small (roughly under 100k rows) — ship it to every node. shuffle when both sides are large and the join key is high-cardinality — repartition both by the key so each node handles a slice, avoiding a single-node memory blow-up. Don’t shuffle a low-cardinality key (it over-partitions).

5. How do you get the whole latest record per entity? summarize arg_max(TimeGenerated, *) by EntityId. arg_max returns the full row (* = all columns) at the maximum of the first argument — the canonical “current state per entity.” arg_min gives the earliest. Using max(TimeGenerated) alone gives only the timestamp, not the row.

6. Why can a parse silently corrupt an aggregate, and how do you guard against it? parse … with sets its target fields to empty (not an error) when a line doesn’t match the pattern, so aggregating counts blanks as a real group. Guard with where isnotempty(Field) after the parse, use parse-where to drop non-matches, or tighten the pattern. Always validate the match rate on real data.

7. What is the risk of mv-expand, and where should it go in a pipeline? mv-expand fans an array to one row per element, multiplying rows — a 10-element array becomes 10 rows. Placed before filters it explodes the working set (slow, possibly OOM). Put it after your where clauses, expanding only the rows you actually need. Use mv-apply when you need a sub-query per array.

8. When should a table be on Basic Logs instead of Analytics, and what do you give up? Basic Logs suits high-volume, rarely-queried data (verbose app/firewall/container logs) — it has cheaper ingestion but bills per GB scanned at query time and offers reduced KQL (limited operators, no alerting on the table, short interactive retention). You give up interactive dashboards and alerts on that table; you keep cheap storage and occasional investigation.

9. What does materialize() do and when is it worth it? It computes a tabular sub-expression once, caches the result for the query’s lifetime, and reuses it on every reference — turning N scans into one. It is worth it whenever a let-bound sub-result is referenced more than once (both join sides, fan-outs, unions of aggregates). The cost is memory, so materialize a filtered, narrowed sub-result, not a raw table.

10. How does RBAC access mode change what a KQL query returns? Under resource-context access, a user sees only rows for resources they have permission on, so the same query returns different results per identity. Under workspace-context, they see all rows in the workspace. This affects dashboards, alerts, and Sentinel queries — design for it and grant the narrowest scope that works.

11. Why might a perfectly valid alert query never fire? A scheduled query rule fires when its query returns rows crossing the threshold. If a bad filter, a silently-failing parse, or a wrong join kind makes the query return zero rows, the rule never fires — you discover it during the incident it was meant to catch. Test alert queries against historical bad windows and assert they return the expected rows.

12. What is the cheapest way to detect hosts that stopped reporting? A leftanti join: distinct Computer over the last hour, join kind=leftanti against distinct Computer over the last 15 minutes — what survives is present-then-absent. It cleanly replaces awkward NOT-IN logic and is the canonical silent-host detection.

These map to AZ-104 (Administrator) — monitor resources with Azure Monitor, configure Log Analytics — and AZ-204 (Developer) — instrument and monitor solutions, Application Insights, KQL. The security/hunting angle (Sentinel detections, ASIM, watchlists, UEBA) maps to SC-200 (Security Operations Analyst), and the data-platform depth touches DP-203 where Azure Data Explorer appears. A compact cert map:

Quick check

1. Your latency time chart shows a dramatic 900 ms tower at 03:14 but no matching errors or deploy. What is the single most likely query cause, and how do you confirm it?
2. A join between AppRequests and AppExceptions returns fewer failures than customers report. What is the default doing, and what one keyword fixes it?
3. True or false: dcount(UserId) returns an exact distinct count.
4. You need the whole most-recent row per Computer. Which single summarize expression gives it?
5. Your Log Analytics query scanned 300 GB to answer a question about the last hour. Name the two most likely causes.

Answers

1. bin() leaving empty buckets absent. A low-traffic series with one slow request in an otherwise-empty minute is drawn by the portal’s autoscale as a tower, because bin produces no row for the empty minutes around it. Confirm by re-running as make-series … default=0; the gap-filled series shows the true (undramatic) shape.
2. The default innerunique de-duplicates the left (request) key before matching, collapsing one-to-many to one-to-one and undercounting. Fix by specifying kind=inner (or the outer kind you actually mean).
3. False. dcount is an approximate distinct count using HyperLogLog — fast and memory-bounded on high cardinality, with a small (~1–2%) error. Use exact distinct only when the value set is small enough to afford it.
4. summarize arg_max(TimeGenerated, *) by Computer — arg_max returns the full row (*) at the maximum TimeGenerated, i.e. the current state per host. max(TimeGenerated) alone gives only the timestamp.
5. (a) A missing or too-wide TimeGenerated filter, so the engine can’t prune partitions; and/or (b) an unbounded union * / workspace-wide search that touches every table. Add a tight time filter and name tables explicitly; check the Stats pane’s “data scanned.”

Glossary

• KQL (Kusto Query Language) — the read-only, pipeline-based query language behind Azure Monitor, Log Analytics, Sentinel, Resource Graph, Application Insights, and Azure Data Explorer.
• Log Analytics workspace — the container of typed tables that KQL queries; the unit of RBAC, retention, billing, and join scope.
• Table plan — Analytics (full KQL, alerts), Basic Logs (reduced KQL, per-query scan billing), or Auxiliary (cheapest, batch) — the per-table trade of capability for price.
• TimeGenerated — the datetime every log row carries; the partition/prune key you filter first, always.
• Tabular pipeline — a source table followed by |-separated operators, each consuming and producing a tabular result; order is performance.
• summarize — collapses rows into one per group; aggregation functions before by, grouping keys after.
• bin() — rounds a value (usually TimeGenerated) down to a bucket; produces a time-series table with empty buckets absent.
• make-series — produces gap-filled vector series (value + time arrays); required input for series_* functions.
• join kind — how two tables correlate: innerunique (default, de-dups left), inner, leftouter/rightouter/fullouter, leftsemi/rightsemi, leftanti/rightanti.
• innerunique — the default join kind, which de-duplicates the left key before matching and can silently undercount.
• leftanti — a join keeping left rows with no match; the canonical “present-then-absent” / silent-host pattern.
• lookup — enriches from a small dimension table by broadcasting it to every node — cheaper than join for that pattern.
• hint.strategy=shuffle — repartitions both join sides by the key for high-cardinality joins across the cluster.
• hint.strategy=broadcast — ships a small side of a join to every node holding the other side.
• dynamic — a JSON/array/property-bag value; index into it, and cast leaf values (toint, tostring) before use.
• parse / parse_json — extract named fields from a string by pattern / turn a JSON string into a navigable dynamic; non-matches fail silently to empty.
• mv-expand / mv-apply — fan an array to one row per element / run a sub-query per array; mv-expand multiplies rows, so place it after filters.
• arg_max / arg_min — return the whole row at the max/min of a column; arg_max(TimeGenerated, *) is “latest state per entity.”
• dcount — approximate distinct count (HyperLogLog); fast and memory-bounded on high cardinality, with small error.
• let — binds a name to a scalar, table, or lambda within one query; a plain tabular let is re-evaluated on each reference.
• materialize() — computes a sub-result once and caches it for the query; turns N scans into one for multi-referenced sub-results.
• Stored function — a saved, workspace-wide table-valued function (a saved search of category Function); the mechanism behind ASIM parsers and shared query libraries.
• Scheduled query rule — a log search alert: KQL run on a schedule (evaluationFrequency) over a lookback window (windowSize) that fires action groups on a threshold.
• Usage table — the workspace’s own billing/volume table; GB by DataType is your source of truth for what data costs.

Next steps

You can now write KQL that is both correct and cheap. Build outward:

• Next: Azure Monitor and Application Insights: Full-Stack Observability — where the requests/exceptions/dependencies tables you query with this KQL come from, end to end.
• Foundation: The Azure Monitor Data Platform Explained: Metrics, Logs, Traces and Where Each Lives — the platform model beneath every table you query.
• Decision: Metrics vs Log Analytics: Choosing the Right Store for Speed, Cost and Retention — what belongs in KQL-queryable logs versus metrics.
• Design: How to Create a Log Analytics Workspace: Region, Access Modes and Resource-Context RBAC — the workspace your queries run against and the RBAC that shapes their results.
• Act on it: How to Create Your First Metric Alert and Action Group and Killing Alert Noise: Tuning Thresholds, Suppression and Flapping — turn KQL into alerts that page correctly.
• Hunt with it: KQL Threat Hunting Playbooks: MITRE ATT&CK Mapping, UEBA, and Hunting Notebooks — the same language, pointed at Microsoft Sentinel.
• Govern with it: Azure Resource Graph Cookbook: KQL Queries for Inventory and Drift at Scale — KQL over ARM resource metadata for inventory and drift.