Azure Log Analytics and KQL Queries

Log Analytics workspaces

A Log Analytics workspace is the storage and query container for your log data. Think of it as a purpose-built database for operational data. Every piece of log data in Azure Monitor lives in a workspace, organized into tables.

Key workspace decisions:

• Region — Workspaces are regional. Data ingested into a workspace stays in that region, which matters for compliance.
• Retention — Default interactive retention is 30 days (free within the workspace cost). You can extend to 730 days. Beyond that, data can be archived at a lower cost tier.
• Workspace consolidation — Most organizations benefit from a small number of centralized workspaces (one per environment or one per region) rather than one workspace per resource. Cross-resource queries are easier in a single workspace.

# Create a Log Analytics workspace
az monitor log-analytics workspace create \
  --resource-group myRG \
  --workspace-name myWorkspace \
  --location eastus \
  --sku PerGB2018 \
  --retention-time 90

# Show workspace details including Customer ID (used in agent config)
az monitor log-analytics workspace show \
  --resource-group myRG \
  --workspace-name myWorkspace \
  --query "{name:name, customerId:customerId, retentionInDays:retentionInDays}" \
  --output table

KQL fundamentals

KQL uses a pipeline model. You start with a table, then apply a series of operators separated by the pipe character (|). Each operator receives the output of the previous one and transforms it.

The most frequently used operators are:

• where — Filter rows based on a condition
• project — Select specific columns (like SQL SELECT)
• summarize — Aggregate data (like SQL GROUP BY)
• order by — Sort results
• limit / take — Limit number of rows returned
• extend — Add computed columns
• join — Join two tables
• render — Visualize results as a chart

KQL query walkthrough: investigating a spike in HTTP errors

Let’s walk through a real investigation scenario. Your alerting system fires at 14:23 — HTTP 5xx errors on your App Service exceeded 20 per minute. Here is the sequence of KQL queries you would run.

Step 1: Confirm the spike and its timing.

// Confirm when errors started and how many there were
AppRequests
| where TimeGenerated > ago(2h)
| where ResultCode startswith "5"
| summarize ErrorCount = count() by bin(TimeGenerated, 1m)
| render timechart

Step 2: Break down by URL to find the affected endpoint.

// Which endpoints are generating 5xx errors?
AppRequests
| where TimeGenerated between (datetime(2026-03-19 14:20:00) .. datetime(2026-03-19 14:35:00))
| where ResultCode startswith "5"
| summarize ErrorCount = count(), AvgDuration = avg(DurationMs) by Url, ResultCode
| order by ErrorCount desc
| limit 20

Step 3: Find correlated exceptions.

// Find exceptions that happened at the same time as the error spike
AppExceptions
| where TimeGenerated between (datetime(2026-03-19 14:20:00) .. datetime(2026-03-19 14:35:00))
| summarize ExceptionCount = count() by ExceptionType, OuterMessage
| order by ExceptionCount desc
| limit 10

Step 4: Trace the operation end-to-end.

// Get the full trace for a specific failed operation
let failedOpId = "abc123def456";
union AppRequests, AppDependencies, AppExceptions
| where OperationId == failedOpId
| project TimeGenerated, ItemType = itemType, Name, DurationMs, Success, ResultCode, OuterMessage
| order by TimeGenerated asc

Key tables to know

Useful KQL patterns

These patterns come up frequently in real monitoring work.

// Pattern 1: Error rate as a percentage over time
AppRequests
| where TimeGenerated > ago(1h)
| summarize
    Total = count(),
    Errors = countif(Success == false)
    by bin(TimeGenerated, 5m)
| extend ErrorRate = round(100.0 * Errors / Total, 2)
| render timechart

// Pattern 2: P95 and P99 response time (percentiles)
AppRequests
| where TimeGenerated > ago(1h)
| where Name == "GET /api/orders"
| summarize
    P50 = percentile(DurationMs, 50),
    P95 = percentile(DurationMs, 95),
    P99 = percentile(DurationMs, 99)
    by bin(TimeGenerated, 5m)
| render timechart

// Pattern 3: Top slow dependencies
AppDependencies
| where TimeGenerated > ago(1h)
| where Success == false or DurationMs > 2000
| summarize
    CallCount = count(),
    AvgDuration = avg(DurationMs),
    FailCount = countif(Success == false)
    by Target, DependencyType
| order by FailCount desc, AvgDuration desc

// Pattern 4: Users affected by errors
AppRequests
| where TimeGenerated > ago(1h)
| where Success == false
| summarize
    ErrorCount = count(),
    AffectedUsers = dcount(UserId)
    by bin(TimeGenerated, 10m)
| render timechart

Saved queries and query packs

You can save KQL queries in the Log Analytics workspace for reuse by your team. Saved queries appear in the query browser alongside Microsoft’s built-in example queries. For larger teams, query packs let you package and share a collection of queries across workspaces — useful for standardizing the queries your on-call team uses during incidents.

# Create a query pack
az monitor log-analytics query-pack create \
  --resource-group myRG \
  --query-pack-name "OnCallQueryPack" \
  --location eastus

# Add a query to the pack (via REST since CLI has limited query-pack support)
# Typically done through the portal or ARM template