Microsoft Azure — Log Analytics & KQL

Azure is Microsoft's cloud computing platform offering over 200 services. This article covers log analytics & kql with production-ready configurations, best practices, and hands-on examples.

Why Log Analytics and KQL Matter in Production

In production Azure environments, logs are the single source of truth for diagnosing failures, understanding performance, and auditing security. Log Analytics workspaces aggregate logs from Azure resources, VMs, containers, and custom sources. Kusto Query Language (KQL) is the query language used to extract insights from these logs. Without KQL, you're flying blind. This article assumes you have a Log Analytics workspace and some logs flowing in. We'll cover the essential KQL patterns that every DevOps engineer needs to know, from basic filtering to advanced time-series analysis.

// Get all errors from the last hour
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Level == "Error"
| project TimeGenerated, Resource, Message
| take 10

Essential KQL Operators: Where, Project, Extend

The three most common operators are where (filter rows), project (select columns), and extend (create computed columns). In production, you'll use them constantly. Where filters based on conditions; project reduces the result set to relevant columns, improving performance; extend adds new columns derived from existing ones. Always project early to minimize data transfer. Avoid using project-away unless you're sure the excluded columns are not needed downstream.

// Filter, project, and extend
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "MICROSOFT.WEB/SITES"
| project TimeGenerated, Resource, StatusCode, DurationMs
| extend StatusCategory = strcat("HTTP ", strcat(StatusCode))
| take 10

Aggregating Logs with Summarize

Summarize is KQL's GROUP BY equivalent. It aggregates data over groups using functions like count(), sum(), avg(), percentile(), and make_list(). In production, you'll use it to compute error rates, average response times, or top error messages. Always include a time bin (bin()) to avoid unbounded aggregation. The bin() function rounds timestamps to a given interval — use it to create time series.

// Count errors per hour
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where Level == "Error"
| summarize ErrorCount = count() by bin(TimeGenerated, 1h), Resource
| order by TimeGenerated asc

Joining Tables for Cross-Resource Analysis

Production incidents often span multiple resources. Joins let you correlate logs from different tables (e.g., AzureDiagnostics and AzureActivity). KQL supports lookup, join, and union. Use join with kind=leftouter or kind=inner. For performance, ensure the left table is the smaller one and that both sides have indexed columns (like TimeGenerated and CorrelationId). Avoid cross-joins at all costs.

// Correlate errors with activity logs
let errors = AzureDiagnostics
    | where TimeGenerated > ago(1h)
    | where Level == "Error"
    | project TimeGenerated, Resource, CorrelationId;
let activities = AzureActivity
    | where TimeGenerated > ago(1h)
    | where OperationName == "MICROSOFT.WEB/SITES/STOP/ACTION"
    | project TimeGenerated, Resource, CorrelationId;
errors
| join kind=inner activities on CorrelationId
| project-away errors_CorrelationId, activities_CorrelationId
| take 10

Time Series Analysis with Series Functions

KQL has built-in functions for time series analysis: series_decompose(), series_fit_line(), and series_periods_detect(). These are invaluable for anomaly detection and trend analysis. For example, series_decompose() splits a time series into trend, seasonal, and residual components. In production, use it to detect sudden spikes in error rates or drops in throughput. The function expects a dynamic array — use make_list() with summarize to create it.

// Detect anomalies in error count over the last 7 days
let errors = AzureDiagnostics
    | where TimeGenerated > ago(7d)
    | where Level == "Error"
    | summarize ErrorCount = count() by bin(TimeGenerated, 1h)
    | order by TimeGenerated asc
    | summarize make_list(ErrorCount) by bin(TimeGenerated, 1h); // incorrect — see callout
// Correct approach:
let raw = materialize(
    AzureDiagnostics
    | where TimeGenerated > ago(7d)
    | where Level == "Error"
    | summarize ErrorCount = count() by bin(TimeGenerated, 1h)
    | order by TimeGenerated asc
);
let series = raw | summarize Timestamps = make_list(TimeGenerated), Values = make_list(ErrorCount);
series
| extend anomalies = series_decompose(Values, -1, 'spikes')
| mv-expand Timestamps, Values, anomalies
| where anomalies == 1

Creating Alerts with KQL Query Results

Log Analytics alerts run KQL queries on a schedule and fire when results meet a threshold. The query must return a numeric metric (e.g., count of errors). Use the alert rule to set frequency, threshold, and action groups. In production, always include a time filter relative to now (ago()) to avoid stale data. Test your query in the Log Analytics UI before creating the alert. Use dimensions to split alerts by resource.

// Alert if more than 10 errors in the last 5 minutes
AzureDiagnostics
| where TimeGenerated > ago(5m)
| where Level == "Error"
| summarize ErrorCount = count()
| where ErrorCount > 10

Performance Tuning KQL Queries

Slow queries waste time and money. Key tuning techniques: filter early (where on indexed columns like TimeGenerated), project early, avoid excessive string operations, use materialize() for reused subqueries, and prefer summarize over join when possible. Use the query statistics pane to identify bottlenecks. In production, set a query timeout (default 10 minutes) and use take/limit to sample during development.

// Slow version
AzureDiagnostics
| where Resource == "myWebApp"
| where TimeGenerated > ago(1d)
| extend UpperMessage = toupper(Message)
| where UpperMessage contains "ERROR"
| project TimeGenerated, Message

// Fast version
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where Resource == "myWebApp"
| where Message has_cs "Error"  // case-sensitive, uses index
| project TimeGenerated, Message

Advanced: Cross-Workspace Queries

In large organizations, logs are spread across multiple Log Analytics workspaces. Use the workspace() function to query across workspaces in a single query. This is essential for global views or when consolidating logs from different environments. The syntax: workspace('workspace-id'). You can also use union with workspace() to combine results. Be aware of cross-region latency and cost.

// Query two workspaces for a unified error view
union
    workspace('prod-workspace').AzureDiagnostics,
    workspace('staging-workspace').AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Level == "Error"
| project TimeGenerated, Resource, WorkspaceName = 'prod' // add a literal to distinguish
| take 10

Using KQL with Azure Workbooks

Azure Workbooks provide interactive reports powered by KQL. They combine queries, visualizations, and text into a single dashboard. In production, workbooks are used for post-mortems, capacity planning, and real-time monitoring. You can parameterize queries (e.g., time range, resource name) to make them reusable. Use the workbook's built-in charts (time chart, bar chart, grid) to visualize results.

// Parameterized query for a workbook
let timeRange = {TimeRange:timespan};
let resource = {Resource:string};
AzureDiagnostics
| where TimeGenerated > ago(timeRange)
| where Resource == resource
| where Level == "Error"
| summarize ErrorCount = count() by bin(TimeGenerated, 1h)
| render timechart

Common Pitfalls and How to Avoid Them

Even experienced engineers make mistakes. Top pitfalls: forgetting time filters, using contains instead of has_cs, not using materialize() for reused subqueries, joining on non-indexed columns, and ignoring query limits (500,000 rows default). Always test with a small time range first. Use the query editor's 'Analyze' button to see execution stats. In production, set up a log query audit to catch expensive queries.

// Pitfall: Missing time filter
AzureDiagnostics
| where Level == "Error"
| summarize count() by Resource
// This scans all data — slow and expensive.

// Fix:
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where Level == "Error"
| summarize count() by Resource

Production Monitoring with KQL Dashboards

Dashboards in Azure Monitor are built from KQL queries pinned from Log Analytics or Workbooks. They provide at-a-glance status of your production environment. Best practices: limit each tile to a single metric, use relative time ranges (e.g., last 24 hours), and set auto-refresh (5 minutes). Use conditional formatting to highlight anomalies. In production, dashboards should be the first thing an on-call engineer checks.

// Tile: Error rate over last 24h
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Level == "Error"
| summarize ErrorCount = count()
| extend Status = iff(ErrorCount > 100, "Critical", "OK")

Next Steps: Automating KQL with APIs

KQL queries can be executed programmatically via the Azure Monitor Log Analytics API. This enables automation: run queries from CI/CD pipelines, generate reports, or trigger remediation. Use the REST API with a service principal for authentication. The response is JSON. In production, we use this to automatically create Jira tickets when certain error patterns appear. Be mindful of API rate limits (300 requests per minute per workspace).

import requests
import json

workspace_id = 'your-workspace-id'
query = """
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Level == "Error"
| count
"""

url = f"https://api.loganalytics.io/v1/workspaces/{workspace_id}/query"
headers = {
    'Authorization': 'Bearer YOUR_ACCESS_TOKEN',
    'Content-Type': 'application/json'
}
body = {"query": query}
response = requests.post(url, headers=headers, json=body)
print(response.json())

KQL with Azure Resource Graph: Query Without Log Analytics

Not all KQL queries require ingested log data. Azure Resource Graph (ARG) uses KQL to query live Azure resource metadata directly from Azure Resource Manager. This enables inventory, governance, and compliance checks without enabling Log Analytics. Use it to find untagged resources, exposed management ports, orphaned disks, or configuration drift. ARG is read-only and can be accessed via portal (Resource Graph Explorer), CLI, PowerShell, or SDK. Unlike Log Analytics queries that run against ingested data, ARG queries reflect the current state of your resources in near real-time. In production, we use ARG to generate a daily report of all resources missing required tags — it catches compliance gaps within minutes of deployment.

// Find resources missing required tags
let required = dynamic(['Environment', 'CostCenter', 'Owner']);
Resources
| extend missing = required
| mv-expand k = missing
| where isnull(tags[tostring(k)]) or tostring(tags[tostring(k)]) == ''
| summarize MissingTags = make_set(tostring(k)) by subscriptionId, resourceGroup, name, type
| order by array_length(MissingTags) desc

Workspace Architecture: Single vs. Multiple Workspaces

Choosing the right Log Analytics workspace architecture is a foundational decision. A single workspace simplifies cross-resource queries, alert management, and cost optimization via commitment tiers. Multiple workspaces are justified when you need data isolation across tenants, regions, or compliance boundaries. Best practice: start with one workspace per environment (dev, staging, prod) and only split if you have specific security, billing, or retention requirements. Consider using a dedicated cluster for workspaces ingesting more than 1 TB/day to leverage Customer-Managed Keys (CMK) and commitment tier discounts. Use Azure Policy to enforce diagnostic settings and ensure consistent data collection across subscriptions. In production, we use a federated model with regional workspaces feeding into a central query layer via cross-workspace queries.

// Query across regional workspaces for a unified view
union
    workspace('eus-workspace').AzureDiagnostics,
    workspace('wus-workspace').AzureDiagnostics,
    workspace('weu-workspace').AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Level == "Error"
| extend Region = case(
    _ResourceId contains "eus", "East US",
    _ResourceId contains "wus", "West US",
    _ResourceId contains "weu", "West Europe",
    "Unknown")
| summarize ErrorCount = count() by Region

Log Analytics Security: RBAC, Private Link, and Data Protection

Securing your Log Analytics workspace is critical when logs contain sensitive data. Implement defense-in-depth: use resource-context access mode so resource owners see only their logs without explicit workspace permissions. Apply table-level RBAC to restrict access to sensitive tables like SigninLogs or AuditLogs. For high-security environments, use Azure Private Link to block public network access and force data ingestion through ExpressRoute or VPN. Enable Customer-Managed Keys (CMK) on dedicated clusters for full control over encryption. Configure data retention and archiving policies to comply with regulatory requirements — retain interactive data for 90 days, archive up to 12 years. Use the 'h' literal in KQL to obfuscate sensitive fields in workbook queries. In production, audit all query activity via the LAQueryLogs table and alert on suspicious patterns.

// Detect suspicious queries (high CPU, large scan)
LAQueryLogs
| where TimeGenerated > ago(7d)
| where Stats?.QueryCPUSeconds > 1000 or Stats?.ScannedGB > 100
| project TimeGenerated, User = RequestContext?.AccountName, QueryText, CPUSeconds = Stats?.QueryCPUSeconds, ScannedGB = Stats?.ScannedGB
| order by CPUSeconds desc