Lesson 20 — Log Analytics & KQL

Domain 5 — Monitor & Maintain AZ-104: 10–15% ~35 min Prereq: Lesson 19 — Azure Monitor

Why You Need This

Azure Monitor collects metrics and logs from every layer of your infrastructure, but that data is only useful if you can query it. Log Analytics and KQL (Kusto Query Language) are how you turn raw telemetry into actionable intelligence — finding the engineer who deleted a production VM, charting CPU utilisation over time, or proving that your VMs have been sending heartbeats continuously for the past 30 days.

On the AZ-104 exam, KQL questions test whether you understand the operator pipeline, time functions, and which Azure tables to query for which scenarios. In production, writing KQL is a daily skill — every alert, workbook, and compliance report runs on it.

Log Analytics Workspace

A Log Analytics workspace is the storage and query container for log data in Azure Monitor. Think of it as a database: it holds named tables, you query it with KQL, and you pay for what you ingest and retain.

Key tables you must know

TableWhat it containsCommon use
AzureActivity All control-plane operations in the subscription — create, delete, modify resource Who did what? Who deleted the VM?
SecurityEvent Windows Security event log (logon events, privilege use, etc.) from monitored VMs Failed logons, account lockouts, privilege escalation
Perf Performance counters from Windows/Linux VMs (CPU, memory, disk, network) Resource utilisation trending and alerting
Heartbeat One record per minute from each VM running the Log Analytics agent Is the agent alive? VM connectivity health
SigninLogs Microsoft Entra ID interactive sign-in events (requires Entra diagnostic settings) Failed authentication, MFA patterns, risky sign-ins
AzureDiagnostics Diagnostic logs from many Azure services (Key Vault, NSG, App Service, etc.) Service-level troubleshooting across multiple resource types
ContainerLog stdout/stderr from containers in AKS Application log analysis in Kubernetes workloads

Workspace costs

Log Analytics pricing has two components:

  • Data ingestion: approximately $2.76 per GB ingested (Pay-As-You-Go). Commitment tiers (100 GB/day, 200 GB/day, etc.) provide significant discounts at scale.
  • Data retention: first 30 days of retention is included in the ingestion price. Beyond 30 days, you pay approximately $0.12 per GB per month. The maximum interactive retention is 730 days (2 years). Archival tier extends to 12 years at a lower cost.
Cost trap Verbose diagnostic categories (e.g. AzureDiagnostics from a busy Application Gateway) can generate gigabytes of logs per day. Always audit ingestion volumes before enabling diagnostics at scale. Use the Log Analytics workspace usage and estimated costs blade to identify the most expensive data sources before your first bill arrives.

KQL Basics — The Pipe Syntax

KQL (Kusto Query Language) uses a left-to-right pipe model: you start with a table, then chain operators with | (pipe). Each operator receives the output of the previous one and passes its own output to the next. This makes queries read like a sentence.

TableName
| operator1 arguments
| operator2 arguments
| operator3 arguments
Case sensitivity rules Column names and string comparisons are case-sensitive. Operator keywords (where, project, summarize) are case-insensitive. In practice, write operators lowercase for readability and always match column name capitalisation exactly — a common source of zero-result queries.

Core operators

where — filter rows

The most-used operator. Reduces the row set to those matching a condition. Always apply where as early in the pipeline as possible to minimise the data processed by later operators.

AzureActivity
| where TimeGenerated > ago(24h)
| where ResourceGroup == "rg-payments-prod"

project — select and rename columns

Reduces the column set to only those you need. This is the equivalent of SELECT col1, col2 in SQL. Columns not named in project are dropped from the output.

AzureActivity
| where TimeGenerated > ago(24h)
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, Level

summarize — aggregate

Groups rows and computes aggregates. The by clause defines the grouping keys. Built-in aggregate functions: count(), sum(), avg(), min(), max(), dcount() (distinct count), make_set().

Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)

order by / sort by — sort results

order by and sort by are synonyms. Use desc for descending (most recent first is typical for timestamps).

AzureActivity
| where TimeGenerated > ago(7d)
| order by TimeGenerated desc

take / limit — sample rows

Returns the specified number of rows without a guaranteed order. Use for exploration, not for production reporting.

AzureActivity
| take 10

extend — add a computed column

Adds a new column derived from existing columns. The original columns are preserved.

Heartbeat
| extend UpperComputer = toupper(Computer)
| project Computer, UpperComputer, OSType, TimeGenerated

join — combine two tables

Joins two tables on a common field. The kind parameter controls join semantics — inner (rows matching in both), leftouter (all rows from left, nulls if no match), rightouter, fullouter.

Heartbeat
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| join kind=inner (
    Perf
    | where TimeGenerated > ago(1h)
    | where CounterName == "% Processor Time"
    | summarize AvgCPU = avg(CounterValue) by Computer
) on Computer

union — combine row sets

Appends rows from multiple tables (or workspaces) into a single result set. Use when different tables hold the same schema of data.

union SecurityEvent, WindowsEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625

render — visualise

Instructs the Log Analytics UI to render the result as a chart. Common types: timechart, barchart, piechart, table. This operator only affects the UI rendering — it does not change query results when used programmatically.

Perf
| where TimeGenerated > ago(1h)
| where CounterName == "% Processor Time"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
| render timechart

parse — extract fields from unstructured strings

Extracts named fields from a string column using a pattern. Useful for application logs where data is embedded in a message field.

AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS"
| parse requestUri_s with Protocol "://" Host "/" Path
| project TimeGenerated, Protocol, Host, Path, httpStatus_d

mv-expand — expand multi-value fields

Turns a single row containing a dynamic array or property bag into multiple rows, one per element. Used when a column contains a JSON array.

AzureActivity
| where Properties has "policies"
| mv-expand policies = todynamic(Properties).policies
| project TimeGenerated, Caller, policies

Time Functions

Time expressions in KQL are relative to query execution time. This is critical for scheduled alerts — the query re-evaluates the time window each time it runs.

ExpressionMeaningTypical use
ago(1h) 1 hour before now where TimeGenerated > ago(1h)
ago(7d) 7 days before now Week-over-week reporting
now() Current UTC timestamp Computing age of events
datetime('2026-06-01') Absolute timestamp Fixed date range queries
bin(TimeGenerated, 1h) Rounds TimeGenerated down to nearest hour boundary Creates time buckets for summarize — required for render timechart
startofday(now()) Midnight UTC of current day Today's data from midnight
bin() is required for timecharts If you want to render timechart, you must use bin(TimeGenerated, interval) in your summarize by clause. Without it, each unique timestamp creates its own bucket, producing a chart with thousands of individual points instead of smooth aggregated lines.

String Functions

KQL has a rich set of string operators. The most important ones for AZ-104 scenarios:

Function / OperatorExampleNotes
contains where OperationNameValue contains "delete" Case-insensitive substring match
startswith where Computer startswith "vm-web" Prefix match, case-insensitive
endswith where OperationNameValue endswith "delete" Suffix match
matches regex where Computer matches regex "^vm-prod-[0-9]+" Full RE2 regex syntax
toupper() extend Upper = toupper(Computer) Uppercase conversion
tolower() extend Lower = tolower(UserPrincipalName) Lowercase — normalise email addresses
strcat() extend Full = strcat(Computer, " / ", OSType) Concatenate strings
split() extend Parts = split(ResourceId, "/") Returns dynamic array of substrings
indexof() where indexof(Message, "Error") >= 0 Position of first occurrence (-1 if not found)

Production Queries for AZ-104 Scenarios

These are the exact query patterns you should be able to write from scratch in the exam and in production. Study each one until the logic is intuitive, not memorised.

Find all Azure resource deployments in the last 7 days

Uses the AzureActivity table which captures every ARM control-plane operation. The contains filter picks up both write and delete operations across all resource types.

AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue contains "write" or OperationNameValue contains "delete"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, Level
| order by TimeGenerated desc

VM CPU average over last hour (with time chart)

The Perf table stores Windows and Linux performance counters. ObjectName == "Processor" selects the CPU object; CounterName == "% Processor Time" picks total CPU. bin(TimeGenerated, 5m) creates 5-minute buckets. render timechart draws the line chart in the portal.

Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
| render timechart

Failed sign-in attempts ranked by failure count

Requires SigninLogs routing to the workspace via Entra ID diagnostic settings. ResultType != "0" captures all non-success codes. Grouping by user and IP reveals brute-force or spray patterns.

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailureCount = count() by UserPrincipalName, IPAddress, ResultDescription
| order by FailureCount desc

Agent heartbeat — find VMs that have stopped reporting

The Heartbeat table gets one record per minute from each monitored VM. This query finds machines whose last heartbeat is older than 5 minutes — indicating the agent is down or the VM is off.

Heartbeat
| where TimeGenerated > ago(5m)
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| where LastHeartbeat < ago(5m)
Heartbeat logic subtlety The outer where TimeGenerated > ago(5m) restricts the initial scan window. The inner where LastHeartbeat < ago(5m) then filters out any machine whose most recent heartbeat within that window is still older than 5 minutes. For machines that have been silent for longer, you may need to remove the outer filter or widen it.

Find who deleted a resource

Filter AzureActivity for successful delete operations. The Caller field contains the UPN of a user or the service principal appID that performed the action.

AzureActivity
| where OperationNameValue endswith "delete"
| where ActivityStatusValue == "Success"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, _ResourceId
| order by TimeGenerated desc
Exam scenario "A production VM was deleted. Find out who did it." — the answer is always: query AzureActivity, filter by OperationNameValue endswith "delete" and ActivityStatusValue == "Success", then read the Caller field. Memorise this pattern.

Log Analytics Workspace Design

At enterprise scale, how you design your workspace topology significantly impacts cost, query experience, and compliance posture.

Centralised vs. decentralised

Centralised (recommended): 1–2 workspaces per environment (Production, Non-Production). All resources route logs to one place. Advantages: simpler cross-resource correlation, single RBAC boundary for log access, lower workspace overhead.

Decentralised (antipattern at scale): one workspace per application or team. Creates proliferation — 50 workspaces means 50 separate query contexts, 50 sets of access controls, and cross-workspace queries everywhere.

Microsoft's recommendation: start with a centralised model. Add a second workspace only when you have a hard compliance boundary (e.g. a PCI environment that must isolate its logs from the general workspace).

Cross-workspace queries

When data is split across workspaces, use the workspace() function to include remote workspaces in a query. You can reference them by name, resource ID, or workspace GUID.

union
    workspace("prod-la-workspace").AzureActivity,
    workspace("staging-la-workspace").AzureActivity
| where TimeGenerated > ago(7d)
| where Caller contains "alice@contoso.com"
| order by TimeGenerated desc

Dedicated clusters

For workspaces ingesting more than 500 GB/day, a dedicated Log Analytics cluster provides:

  • Commitment tier pricing (significant cost savings vs. Pay-As-You-Go above this threshold)
  • Customer-managed encryption keys (CMK) — required for some compliance frameworks
  • Availability zone support
  • Multiple workspaces can be linked to a single cluster and share the commitment tier
Workspace deletion is irreversible Deleting a Log Analytics workspace deletes all ingested data permanently. There is a 14-day soft-delete period during which the workspace can be recovered, but after that the data is gone. Protect workspaces with resource locks (CanNotDelete) in production environments.

Workbooks and Log Search Alerts

Workbooks

Azure Monitor Workbooks are interactive, parameterised reports built on KQL. A workbook can contain multiple queries, text sections, charts, and grids — all driven by user-selectable time ranges, subscription filters, or resource scopes. Microsoft provides dozens of gallery templates for common scenarios (VM health, Security overview, Cost analysis).

Workbooks are the correct answer when the exam asks about "interactive reports" or "parameterised dashboards" — as opposed to static dashboards which are pinned chart tiles.

Log search alerts

A log search alert runs a KQL query on a schedule and fires an alert rule when the result meets a threshold condition. Key configuration parameters:

ParameterMeaningExample
KQL query The query that runs on schedule Count failed sign-ins per user
Measurement Table rows (count) vs. aggregate column value Count rows = number of events; aggregate = e.g. avg CPU value
Threshold The condition that triggers the alert Greater than 10 failures
Frequency How often the query runs Every 5 minutes
Evaluation window The time range the query covers each run Last 15 minutes (window should be ≥ frequency)
Action group What to do when alert fires Email, SMS, webhook, ITSM ticket
Frequency vs. evaluation window The evaluation window must be equal to or greater than the frequency. If a query runs every 5 minutes but the window is only 3 minutes, you will have a 2-minute blind spot between runs. Set the window to at least the frequency, typically 2–3x the frequency for overlap.

Check Your Understanding

Click any option to see immediate feedback.

1. What does this query do? AzureActivity | where TimeGenerated > ago(1d) | summarize count() by Caller

The pipe symbol passes the output of one operator as input to the next. where filters rows to the last 24 hours. summarize count() by Caller then groups the filtered rows by Caller and counts each group — returning one row per unique Caller with their operation count.

2. You want to produce a time-series chart of average CPU usage per VM in 1-hour buckets. Which KQL combination achieves this?

bin(TimeGenerated, 1h) rounds each timestamp down to the nearest hour boundary, creating the hourly buckets for aggregation. Combined with render timechart, this produces a time-series line chart. Without bin(), each unique timestamp would be its own bucket, producing thousands of isolated points instead of a smooth trend line.

3. What does ago(7d) evaluate to when a log search alert query runs on Monday at 09:00 UTC?

ago(7d) is a relative time expression evaluated at query execution time. Every time the alert runs, it recalculates 7 days before now. This means the query always covers a rolling 7-day window, not a fixed historical period. This is the correct behaviour for continuous monitoring.

4. You use join kind=inner to combine a Heartbeat summary with a Perf summary on the Computer field. A VM appears in Perf but has no recent Heartbeat record. What happens to that VM in the output?

kind=inner returns only rows where the join key (Computer) exists in both tables — identical to SQL INNER JOIN. A VM present in Perf but absent from Heartbeat will be silently dropped from the results. Use kind=leftouter if you want all VMs from the left table (Heartbeat summary) regardless of whether they appear in Perf.

5. An attacker deleted all backup items in your Recovery Services Vault. You need to find which account performed the deletion. What query approach should you use?

All Azure control-plane operations — including deleting backup items — are recorded in AzureActivity. Filtering by endswith "delete" and ActivityStatusValue == "Success" narrows to successful deletions. The Caller field contains the UPN of the user or service principal that performed the action. This is the canonical pattern for Azure activity attribution.

6. You need to query AzureActivity from two different Log Analytics workspaces in a single query. What is the correct KQL syntax?

Cross-workspace queries use the workspace() function with the workspace name, resource ID, or GUID. union workspace("workspace-prod").AzureActivity, AzureActivity appends rows from the remote workspace's AzureActivity table to the local workspace's AzureActivity table, giving you a combined result set for queries that span multiple workspaces.
Primary source for this lesson Kusto Query Language (KQL) overview — Microsoft Learn

Work through the KQL tutorial and the log queries samples in the Log Analytics portal. Real fluency comes from writing queries against live data — use the Log Analytics demo workspace at portal.azure.com if you do not have a subscription with significant log data yet.

Questions for your teacher (the AI agent)
This lesson covered the core KQL operators and common AZ-104 query patterns. Go deeper on:
  • Walk me through building a workbook that tracks VM CPU, memory, and disk across multiple subscriptions with a time-range parameter.
  • How do I write a log search alert that fires only when the same source IP fails sign-in more than 10 times in 5 minutes?
  • What is the difference between contains, has, and matches regex for string filtering — and which is most performant?
  • How do I use parse and mv-expand together to extract structured data from JSON embedded in a message field?
Coming up: Lesson 21 — Azure Backup & Recovery Services Vault Now that you can monitor and query your infrastructure, the next lesson covers protecting it. Azure Backup is the first layer of resilience — daily snapshots, application-consistent backups, soft delete, and the Recovery Services Vault that holds it all. We'll cover everything from backup policy design to the exact restore options you'll need on the exam.