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

KQL is easy to write and easy to write expensively. A query that scans a month of AppTraces to answer a question that lived in the last 15 minutes returns the right answer while quietly burning your budget. This is a working tour of the language that matters in Log Analytics, plus the ingestion-side levers (table plans, transformations, commitment tiers) that decide whether observability is a line item or a crisis.

1. The data model you must internalize

A Log Analytics workspace is a collection of tables, each with a fixed schema of typed columns. Azure Monitor data lands in well-known tables (AzureActivity, AzureDiagnostics, Heartbeat, Perf, AppRequests, AppExceptions), and every one has the query model’s spine: TimeGenerated, a datetime that every time filter, bin(), and retention policy keys off.

Internally the store is a columnstore: data is partitioned by time and compressed per column. Two consequences drive almost every performance rule. Filtering on TimeGenerated first lets the engine skip whole partitions before reading anything, and selecting fewer columns (project) reads less compressed data off disk.

Before querying a table you have not seen, discover its shape with getschema, and use a bounded union to find which tables hold data and how much:

AppRequests | getschema    // column names and types

union withsource=TableName *
| where TimeGenerated > ago(1d)
| summarize Rows = count() by TableName
| sort by Rows desc

Run union * only when 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 most common way people accidentally scan terabytes.

2. Filtering and shaping fast: where, project, extend

KQL is a pipeline: each | passes a tabular result to the next operator, so order is performance. Cut rows before anything expensive.

AppRequests
| where TimeGenerated > ago(1h)     // time filter first: prunes partitions
| where Success == false            // then high-selectivity equality
| where Url has "/api/checkout"     // string match on survivors
| project TimeGenerated, Name, DurationMs, ResultCode, OperationId
| extend DurationSec = DurationMs / 1000.0

A few specifics separate fast queries from slow:

• has versus contains. has matches whole indexed terms and is far faster; contains is an unindexed substring scan. Prefer has, has_cs, startswith, or == on term-structured data.
• project early. Reading 5 columns instead of 40 is a real I/O win on a columnstore; use project-rename instead of extend for a pure rename.
• extend computes, it does not filter. Compute after shrinking the row set.
• Do not wrap the filtered column in a function. where toupper(Name) == "GET /" defeats indexing; filter the raw column.

3. summarize and bin: aggregations and time-series buckets

summarize collapses rows into groups: keys go after by, everything before it is an aggregation.

AppRequests
| where TimeGenerated > ago(24h)
| summarize
    Total    = count(),
    Failures = countif(Success == false),
    (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 without a separate branch, and percentiles() returns several approximate-but-cheap percentiles in one pass, fine for SLOs. To build a time-series, 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, not data, but it sanity-checks shape instantly. Two more helpers earn their keep: dcount() for approximate distinct counts (HyperLogLog, fast on high-cardinality columns like OperationId), and arg_max() / arg_min() to get the full row at a max/min — summarize arg_max(TimeGenerated, *) by ResourceId is the canonical “latest record per resource” pattern.

4. Joins and unions across tables

union stacks tables with compatible schemas; join correlates rows on a key. Both are where cardinality explosions live. 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, so a fat right side is the classic slow-join cause — put the smaller table on the right. Second, pick the kind deliberately: the default innerunique dedupes the left key first and surprises people who expected a relational inner.

kind	Keeps	Use when
innerunique (default)	Deduped left keys + matches	You did not think about it (be careful)
inner	Only matching rows, all dupes	True relational inner join
leftouter	All left rows, nulls where no match	Enrich without dropping rows
leftsemi	Left rows that have a match (left cols only)	“Which requests had an exception?”
leftanti	Left rows with no match	“Which requests had no heartbeat?”

leftanti deserves a callout: it cleanly replaces awkward NOT-IN logic. The canonical use is silent-host detection — 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.

For small, static lookups (region maps, owner tables), use lookup with an inline datatable, which the engine broadcasts as a dimension table rather than shuffling:

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

5. Parsing semi-structured logs

Real logs are rarely fully columnar. parse extracts named fields from a string by example pattern:

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

If the format is irregular, fall back to extract (one regex capture group) or extract_all (many):

AppTraces
| extend StatusCode = extract(@"status=(\d{3})", 1, Message)
| where isnotempty(StatusCode)

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)
| summarize count() by Endpoint, Code

If a column is already dynamic (many Azure tables store JSON natively), skip parse_json and index it directly; re-parsing a parsed object is wasted work.

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

AppTraces
| 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, so apply it after filtering; mv-apply is the variant for a subquery per array.

6. Time-series analytics: make-series and anomalies

summarize ... by bin() gives a table; make-series gives vector-valued series with gap-filling that the analytics functions need.

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 with value and time arrays. On top of it, 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 (residual standard deviations); lower is more sensitive. It returns three series: a flag (-1/0/1), a score, and the baseline. For capacity work, swap the decomposition for series_decompose_forecast(Reqs, 24) on the same output to project 24 points ahead. Both assume a roughly regular, seasonal signal and overfit on sparse data, so eyeball the baseline before trusting the flags.

7. Turning queries into alerts

A saved query is observability; a query that pages someone is operations. In Azure Monitor that is a scheduled query rule (a log search alert): it runs your KQL on a schedule and fires action groups when the result crosses a threshold. Provision it as code, not by clicking — Bicep keeps it reviewable:

resource alert 'Microsoft.Insights/scheduledQueryRules@2023-03-15-preview' = {
  name: 'high-5xx-rate'
  location: location
  properties: {
    severity: 2
    enabled: true
    scopes: [ workspaceResourceId ]
    evaluationFrequency: 'PT5M'
    windowSize: 'PT15M'
    criteria: {
      allOf: [
        {
          query: '''AppRequests
| where Success == false and toint(ResultCode) >= 500
| summarize Fails = count() by bin(TimeGenerated, 5m), Name'''
          timeAggregation: 'Total'
          operator: 'GreaterThan'
          threshold: 50
          dimensions: [
            { name: 'Name', operator: 'Include', values: [ '*' ] }
          ]
          failingPeriods: {
            numberOfEvaluationPeriods: 1
            minFailingPeriodsToAlert: 1
          }
        }
      ]
    }
    autoMitigate: true
    actionGroups: [ actionGroupId ]
  }
}

The mechanics that matter:

• dimensions turn one rule into per-value alerts. Splitting on Name evaluates each endpoint independently and names the offender in the payload, instead of one giant “something is failing” alert.
• windowSize vs evaluationFrequency. The window is how far back each run looks; the frequency is how often it runs. A 15-minute window every 5 minutes overlaps deliberately, smoothing single-bucket noise.

For signals you do not want to hand-tune a number on, reach for dynamic thresholds: configured on metric alerts via the DynamicThresholdCriterion shape, they learn the baseline and earn their keep when seasonality makes a static number wrong half the day.

8. Cost control: table plans, transformations, and tiers

Query performance is half the bill. The other half is ingestion, priced per GB, where the real money goes. Three levers, coarsest to finest:

Table plans. Every table in a workspace has a plan that trades capability for price:

Plan	Query power	Retention model	Use for
Analytics	Full KQL, alerts, fast interactive	Interactive + long-term	Signals you query and alert on
Basic Logs	Reduced KQL, limited operators, pay-per-query scan	Short interactive, then long-term	High-volume, rarely-queried logs (verbose app/firewall traces)
Auxiliary	Lowest ingestion cost, batch-oriented query	Long-term archive	Cheap, occasionally-investigated data

Setting a verbose, low-value table to Basic cuts its ingestion price sharply, at the cost of interactive querying and alerting. Pick the plan per table by how often you query it:

az monitor log-analytics workspace table update -g rg-observability \
  --workspace-name law-prod --name ContainerLogV2 --plan Basic

Ingestion-time transformations. A Data Collection Rule (DCR) runs a KQL transformKql over incoming records before they are stored — the highest-leverage lever, because it drops bytes you never pay to ingest:

source
| where Level != "DEBUG"               // drop debug noise at the door
| project-away StackTrace, RawPayload   // strip fat columns you never query
| extend Region = tostring(parse_json(Properties).region)   // promote one field

Filtering DEBUG and dropping a fat StackTrace/RawPayload column at ingestion removes a large fraction of volume without losing the signal you search; the same hook redacts sensitive fields before they land.

Commitment (capacity) tiers. Once daily ingestion is predictable, move off pay-as-you-go onto a commitment tier (e.g., 100 GB/day) for a per-GB discount. Size it to your steady-state floor, not peak, since overage still bills at the discounted rate. Pair it with a daily cap as a circuit breaker against runaway log loops:

az monitor log-analytics workspace update -g rg-observability \
  --workspace-name law-prod --quota 200   # daily cap in GB; pauses past this

A daily cap 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.

Enterprise scenario

A retail platform team’s ContainerLogV2 ingestion crossed 1.2 TB/day after a microservice rollout, and the monthly Log Analytics bill nearly doubled. The Usage table pinned it: one chatty service emitting INFO-level request/response bodies, plus a fat RawPayload column nobody queried. The reflex was to drop the table to Basic Logs — but the SRE on-call still ran interactive failure-triage KQL against it during incidents, and Basic loses join and alerting. So a blanket plan change was off the table.

The fix was a two-stage split at ingestion, not a plan downgrade. A Data Collection Rule transformKql filtered noise and stripped the fat column before storage, then the same data was routed: lean, queryable rows stayed on Analytics, while the verbose body was sent to a separate Basic Logs table for the rare deep-dive.

source
| where Level != "INFO" or Url has_any ("/checkout", "/payment")
| project-away RawPayload, ThreadContext
| extend BodySize = strlen(RequestBody)

That single rule cut stored volume by roughly 60% while keeping every signal the on-call query actually touched. The gotcha worth flagging: DCR transformations bill on output bytes for Analytics, so adding columns in a transform raises cost — project-away is the lever, not extend. After volume stabilized for two weeks, they moved the workspace onto a 600 GB/day commitment tier sized to the new floor, with a daily cap at 900 GB as a circuit breaker. Net effect: the bill came in under the pre-rollout baseline, with no loss of incident-time queryability.

Verify

Confirm each layer with a concrete check before trusting it.

// 1. Time filter prunes: run with and without it, compare data scanned
//    in the query details / Stats pane.
AppRequests | where TimeGenerated > ago(1h) | count

# 2. The table plan you set applied, and the alert rule is live.
az monitor log-analytics workspace table show -g rg-observability \
  --workspace-name law-prod --name ContainerLogV2 --query "{plan:plan}" -o table
az monitor scheduled-query show -n high-5xx-rate -g rg-observability \
  --query "{enabled:enabled, freq:evaluationFrequency, window:windowSize}" -o table

// 3. A transformation is dropping what you think. Ingestion volume per table:
Usage
| where TimeGenerated > ago(1d)
| summarize GB = sum(Quantity) / 1024 by DataType
| sort by GB desc

The Usage table is your source of truth for “what is this costing me.” Trend GB by DataType weekly; a table that climbs without a feature shipping is a misconfigured log level.

Checklist

• Every interactive query starts with a TimeGenerated bound
• has / == used instead of contains for term matches
• project narrows columns before joins and expensive operators
• Joins filter and project both sides; kind is explicit, not default innerunique
• make-series ... default = 0 (not bin) before any series_* function
• Alert rules carry dimensions so the payload names the offender
• High-volume, rarely-queried tables on Basic or Auxiliary plans
• A DCR transformKql drops DEBUG and fat columns at ingestion
• Daily cap set above peak, with an alert before it
• Steady-state ingestion on a commitment tier sized to the floor

Pitfalls

• The default join is innerunique. It silently dedupes the left key. If counts look too low or a 1:many collapses to 1:1, name the kind you want.
• contains on a hot table is an unindexed substring scan; on a busy AppTraces, two seconds versus two minutes.
• bin() is not make-series. bin leaves gaps; feed that to series_decompose_anomalies and missing buckets distort the baseline. Use make-series ... default = 0.
• Basic Logs is not free querying. You pay per GB scanned at query time and lose operators and alerting. Right for write-heavy, read-rarely data; wrong for dashboards.
• Daily caps drop data, often the spike you most need. A cap is a backstop; transformations and plans are the strategy.

Next step: pull a week of Usage for your busiest workspace, rank DataType by GB, and for the top three ask “do I query this interactively, and do I alert on it?” The honest answers map straight onto plan choices and a transformation or two — usually where the first 30-40% of the bill disappears.