What this covers

KQL (Kusto Query Language) is how you query Log Analytics and Application Insights. Queries are pipelines: start with a table, then pipe (|) rows through operators that filter, reshape, aggregate and sort. This page covers the core operators, the Application Insights tables and their classic vs workspace names, aggregation and time bucketing, joins and unions, querying metrics, and charting.

Exam weight: Secure, monitor, and troubleshoot Azure solutions (20–25%)

Skills measured

"Write KQL queries to analyze logs and metrics"

Key concepts

Pipeline mental model

KQL

TableName
| operator1
| operator2
| operator3

Each | takes the previous result set and transforms it. Order matters: filter early, aggregate later, sort last. You write queries in the Logs query editor, opened from Azure Monitor, an Application Insights resource or a Log Analytics workspace.

Query scope and table names

Opened from	Table names	Example columns
Application Insights resource	Classic: `requests`, `dependencies`, `exceptions`, `traces`, `customEvents`, `customMetrics`, `performanceCounters`	`timestamp`, `duration`, `success`, `operation_Id`, `cloud_RoleName`, `customDimensions`, `itemCount`
Log Analytics workspace	`AppRequests`, `AppDependencies`, `AppExceptions`, `AppTraces`, `AppEvents`, `AppMetrics`, `AppPerformanceCounters`	`TimeGenerated`, `DurationMs`, `Success`, `OperationId`, `AppRoleName`, `Properties`, `ItemCount`

The Learn module writes classic names; this site mostly uses workspace names. Know both.

Core operators

Operator	Purpose
`where`	Filter rows
`project` / `extend`	Select (and rename/compute) columns / add columns
`project-away` / `project-rename`	Drop / rename
`summarize`	Aggregate (group by)
`order by` / `sort by`	Sort
`top`	Sort + limit
`take` / `limit`	Cap rows, no sort guarantee
`join`	Combine on key
`union`	Stack tables
`render`	Chart in portal
`let`	Variables / named queries
`distinct`	Unique values
`make-series`	Build a time series (fills empty bins)

String matching inside where:

Operator	Meaning
`==` `!=` `>` `<`	Comparisons
`and` / `or`	Combine conditions
`in ("a","b")`	Membership
`has`	Whole-term match, uses the term index (fast)
`contains`	Substring match, no index (slower)
`startswith`	Prefix match

Aggregation functions

count(), sum(), avg(), min(), max(), percentile(col, N), percentiles(col, 50, 95, 99), dcount() (fast estimate), count_distinct() (exact, costlier), countif(), make_list(), make_set(). bin(TimeGenerated, 5m) rounds timestamps into fixed buckets; without it every timestamp is its own group.

Metrics in KQL

Custom OTel metrics land in AppMetrics (classic customMetrics), already pre-aggregated per interval with Sum, ItemCount, Min, Max. Average = sum(Sum) / sum(ItemCount). Platform metrics for Azure resources can be routed to a workspace via diagnostic settings (AzureMetrics).

How it works

Always put the time filter first (where TimeGenerated > ago(1h)); the engine prunes data by time before anything else.

Hands-on

Azure portal

Open Application Insights → Logs (classic names) or the workspace → Logs (App* names).
Set the time picker or include ago() in the query.
Run, then switch Results / Chart to visualize.

Azure CLI

Azure CLI / Bash

az monitor log-analytics query \
  --workspace <workspace-guid> \
  --analytics-query "AppRequests | where TimeGenerated > ago(1h) | summarize count() by ResultCode" \
  --output table

Python

from datetime import timedelta
from azure.identity import DefaultAzureCredential
from azure.monitor.query import LogsQueryClient

client = LogsQueryClient(DefaultAzureCredential())
result = client.query_workspace(
    workspace_id="<workspace-guid>",
    query="AppRequests | summarize P95 = percentile(DurationMs, 95) by Name",
    timespan=timedelta(hours=1),
)
for row in result.tables[0].rows:
    print(row)

KQL examples

where, project, extend:

KQL

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

KQL

AppTraces
| where Message has "timeout"        // preferred
| where Message contains "time"      // broader, slower

KQL

AppRequests
| extend IsSlow = DurationMs > 1000
| where IsSlow == true

Classic equivalent with top:

KQL

requests
| where timestamp > ago(1h)
| where success == false
| project timestamp, name, resultCode, duration, cloud_RoleName
| top 20 by duration desc

summarize:

KQL

AppDependencies
| where TimeGenerated > ago(1h)
| summarize count(), avg(DurationMs), max(DurationMs) by Target

KQL

AppRequests
| where TimeGenerated > ago(1d)
| summarize
    Total = count(),
    Failures = countif(Success == false),
    P95 = percentile(DurationMs, 95)
  by Name
| order by P95 desc

Time buckets and charts:

KQL

AppRequests
| summarize RequestCount = count() by bin(TimeGenerated, 5m)
| render timechart

KQL

requests
| where timestamp > ago(24h)
| summarize requestCount = count() by bin(timestamp, 1h), cloud_RoleName
| render timechart

KQL

AppRequests
| summarize FailureRate = 100.0 * countif(Success == false) / count()
    by bin(TimeGenerated, 1h)
| render timechart

top:

KQL

AppRequests
| top 10 by DurationMs desc

join (specify kind explicitly):

KQL

AppExceptions
| where TimeGenerated > ago(6h)
| join kind=inner (
    AppRequests
    | where TimeGenerated > ago(6h)
  ) on OperationId
| project TimeGenerated, ProblemId, OuterMessage, RequestName = Name, ResultCode

union (Type = source table):

KQL

union AppRequests, AppDependencies, AppExceptions, AppTraces
| where OperationId == "abc123"
| project TimeGenerated, Type, Name, Message
| order by TimeGenerated asc

let:

KQL

let threshold = 1000;
let startTime = ago(2h);
AppDependencies
| where TimeGenerated > startTime
| where DurationMs > threshold

KQL

let SlowOps =
    AppDependencies
    | where DurationMs > 2000
    | distinct OperationId;
AppRequests
| where OperationId in (SlowOps)

Metrics:

KQL

AppMetrics
| where TimeGenerated > ago(6h) and Name == "llm.tokens"
| summarize Tokens = sum(Sum) by bin(TimeGenerated, 15m), AppRoleName
| render timechart

Worked example: top 5 dependency targets causing failures in 24 h, for operations that also had an exception.

KQL

let FailingOps =
    AppExceptions
    | where TimeGenerated > ago(24h)
    | distinct OperationId;
AppDependencies
| where TimeGenerated > ago(24h)
| where OperationId in (FailingOps)
| summarize
    Total = count(),
    Failures = countif(Success == false),
    P95Latency = percentile(DurationMs, 95)
  by Target
| extend FailureRatePct = round(100.0 * Failures / Total, 1)
| top 5 by FailureRatePct desc

Exam traps

Need	Use
Highest N values	`top N by col` (not `take`)
Quick look at data shape	`take 10`
Fast term search	`has`
Substring inside a token	`contains`
Error rate in one pass	`countif()` / `count()`
Latency SLO	`percentile(DurationMs, 95)`, not `avg`
Approximate unique users	`dcount()`

Quick review

KQL is a top-to-bottom pipeline joined by |.
Classic names in App Insights scope, App* names in workspace scope.
has uses the term index; contains does not.
summarize ... by bin(TimeGenerated, 1h) for time series.
top sorts and limits; take returns arbitrary rows.
Default join kind is innerunique.
Custom metrics are in AppMetrics / customMetrics.

Practice

Practice: KQL questions

Write basic KQL queries