azure-kusto
Write and optimize Kusto Query Language (KQL) for Azure Data Explorer, Log Analytics, and monitoring workflows. Use this skill when users ask for KQL queries, telemetry analysis, incident investigatio
SKILL.mdskills/azure-kusto/SKILL.md
GitHubAzure Kusto
Produce clear, performant KQL queries for diagnostics, reporting, operational insights, and incident investigations.
Use This Skill When
- The user asks to write a KQL query for diagnostics or reporting
- The user needs query optimization or performance tuning
- The user is investigating incidents using Azure logs
- The user wants to aggregate telemetry, analyze trends, or build alerts
Context: Query Maturity
Immature: Ad hoc queries, unbounded scans, timeouts
Developing: Filtered queries, basic aggregation
Managed: Optimized execution, good performance, reusable queries → Target
Optimized: Query compilation caching, materialized views, automated insights
Required Inputs
- Data source: Azure Monitor (Log Analytics), Application Insights, Data Explorer?
- Table names: Exact names (AzureActivity, SecurityEvent, CustomEvents)?
- Time range: Last hour? Last 7 days? Specific dates?
- Filters: Specific resources, servers, error codes, user IDs?
- Output needed: Row count? Aggregation? Time series chart? Alert condition?
- Performance constraints: Must complete in 5 seconds? 30 seconds OK?
Decision Tree
What's the primary objective?
├─ Count/summarize → Use summarize operator, aggregate early
├─ Find specific records → Use where filter first, then project
├─ Time-series analysis → Use bin(timestamp), render timechart
├─ Correlation/join → Join multiple tables, but use small left table
└─ Alert condition → Simple condition with threshold
What's the expected data volume?
├─ < 1 million rows → Simple query, no optimization needed
├─ 1-100 million rows → Filter early, use summarize
└─ > 100 million rows → Partition by time, use external tools
How fast does this need to run?
├─ <5 seconds (dashboard) → Aggressive filtering, pre-aggregation
├─ <30 seconds (report) → Standard optimization
└─ <5 minutes (batch analysis) → Can be less optimized
Workflow
Phase 1: Start with Schema Understanding
Know your tables:
// Azure Monitor / Log Analytics tables AzureActivity // Azure Control Plane events (Create, Delete, Update) SecurityEvent // Windows Security logs (logins, privilege escalation) Syslog // Linux syslog entries CommonSecurityLog // Third-party firewall, IDS logs StorageBlob // Azure Storage access logs AppServiceHTTPLogs // App Service HTTP requests // Application Insights tables customEvents // App-generated events pageViews // Web page navigation requests // HTTP requests (API calls) dependencies // External service calls (DB, HTTP) exceptions // Application exceptions traces // Custom telemetry (logging) performance // Performance counters (CPU, memory)Inspect schema:
// See columns and types SecurityEvent | getschema // Output ColumnName ColumnType TimeGenerated datetime Computer string Account string EventID int Activity string LogonType int
Phase 2: Query Construction (Simple → Complex)
Basic query (single table, filter):
// Find all failed logins in last 24 hours SecurityEvent | where TimeGenerated > ago(24h) | where Activity == "Failed" | project TimeGenerated, Computer, Account, LogonTypeAdd aggregation:
// Count failed logins by computer SecurityEvent | where TimeGenerated > ago(24h) | where Activity == "Failed" | summarize FailureCount = count() by Computer | sort by FailureCount descAdd time-series:
// Failed logins over time (hourly) SecurityEvent | where TimeGenerated > ago(7d) | where Activity == "Failed" | summarize FailureCount = count() by bin(TimeGenerated, 1h), Computer | render timechartJoin multiple tables:
// Correlate failed logins with failed API requests SecurityEvent | where Activity == "Failed" | join ( AppServiceHTTPLogs | where HttpStatus >= 400 | project TimeGenerated, UserId, HttpStatus ) on $left.Account == $right.UserId | project TimeGenerated, Account, HttpStatus
Phase 3: Performance Optimization
Filter early (most important):
// ✓ GOOD: Filter first (scans 1000 rows) SecurityEvent | where TimeGenerated > ago(1h) // Filters by time (fast) | where Computer == "server-prod-1" // Filters by resource | where Activity == "Failed" // Filters by value | summarize count() // ✗ BAD: Filter late (scans 10 million rows, then filters) SecurityEvent | summarize count() by Activity // Aggregates all rows first | where Activity == "Failed" // Then filters (slow!)Project only needed columns:
// ✓ GOOD: Get 3 columns (small result set) SecurityEvent | project TimeGenerated, Account, Activity | count // ✗ BAD: Get all columns (large result set) SecurityEvent | countAvoid N:N joins:
// ✗ BAD: Cartesian product (10K × 10K = 100M rows) SecurityEvent | join AppServiceHTTPLogs on Computer == ServerName // Many matches per row // ✓ GOOD: One-to-many join (dimension table small) SecurityEvent | join kind=leftouter ( Resources // Small reference table | project ResourceId, Owner ) on Computer == ResourceIdUse summarize instead of distinct:
// ✗ SLOWER: Get all rows, then dedup SecurityEvent | distinct Account // ✓ FASTER: Aggregate by key SecurityEvent | summarize by Account
Phase 4: Common Query Patterns
Failed authentication analysis:
// Find brute-force attacks (5+ failures in 5 min per account) SecurityEvent | where TimeGenerated > ago(7d) | where Activity == "Failed" | summarize FailureCount = count() by Account, bin(TimeGenerated, 5m) | where FailureCount >= 5 | project TimeGenerated, Account, FailureCount | sort by FailureCount descAPI performance analysis:
// Response time percentiles by endpoint requests | where TimeGenerated > ago(24h) | summarize p50 = percentile(duration, 50), p95 = percentile(duration, 95), p99 = percentile(duration, 99), failureRate = todouble(sum(resultCode >= 400)) / count() * 100 by name | project EndPoint = name, P50_ms = p50, P95_ms = p95, P99_ms = p99, FailureRate_pct = failureRate | sort by P99_ms descError spike detection:
// Errors today vs. yesterday (baseline comparison) let today = exceptions | where TimeGenerated > ago(24h) | summarize ErrorCount = count() by bin(TimeGenerated, 1h), type; let yesterday = exceptions | where TimeGenerated between(ago(48h) .. ago(24h)) | summarize ErrorCount = count() by bin(TimeGenerated, 1h), type; today | join yesterday on type | extend Spike = (ErrorCount < ErrorCount1) ? "Normal" : "SPIKE" | where Spike == "SPIKE"Dependency failures (DB, external API):
// Calls to external service failing dependencies | where TimeGenerated > ago(1h) | where type == "HTTP" or type == "SQL" | summarize TotalCalls = count(), FailedCalls = sum(success == false), P95_ms = percentile(duration, 95) by target, type | extend FailureRate_pct = (FailedCalls / TotalCalls) * 100 | where FailureRate_pct > 5 // Alert if >5% failureCost analysis (query efficiency):
// Data ingestion volume by table (for cost management) union withsource=TableName * | where TimeGenerated > ago(1d) | summarize bytes = sum(estimate_data_size(*)) by TableName | extend GB = bytes / (1024 * 1024 * 1024) | sort by GB desc | render barchart
Phase 5: Query Testing & Validation
Test with time range:
// Check query on small time window first SecurityEvent | where TimeGenerated > ago(1h) // Test 1 hour | summarize count() // Expand after validation | where TimeGenerated > ago(30d) // Now query 30 daysCheck for null values:
// Missing or unexpected data SecurityEvent | where TimeGenerated > ago(24h) | where isempty(Account) or isnan(LogonType) | countValidate result reasonableness:
Query: Failed logins per hour Expected: 100-500 per hour on typical day Got: 50M rows → Issue: Likely a join explosion or missing filter
Phase 6: Advanced Patterns
Anomaly detection (using ML):
// Automatic anomaly in request latency requests | where TimeGenerated > ago(7d) | where isnotempty(duration) | summarize RequestCount = count(), AvgDuration = avg(duration) by bin(TimeGenerated, 1h), name | render anomalychartMaterialized views (pre-aggregate for speed):
// Define view once .create materialized-view HourlyErrors as SecurityEvent | where Activity == "Failed" | summarize FailureCount = count() by bin(TimeGenerated, 1h), Computer // Query pre-aggregated view (very fast) HourlyErrors | where TimeGenerated > ago(7d)
Output Contract
Query Code
- KQL syntax (ready to copy-paste into Azure Portal)
- Documented with comments explaining each step
Query Explanation
- What data is queried?
- What filters are applied?
- What output is returned?
- Expected result size?
Performance Notes
- Query execution time estimate
- Data volume scanned
- Optimization tips if applicable
Optional Variants
- Time range variations (1 day, 7 days, 30 days)
- Alternative filters (by resource, error code, user)
- Alert condition (if/then thresholds)
Guardrails
- State assumptions when schema is unknown: "I'm assuming table X has column Y; please verify."
- Prefer readable composition over dense one-liners: Use clear variable names, line breaks.
- Avoid unbounded scans: Always include time filter (TimeGenerated > ago(X)).
- Test on small time ranges first: Verify query before running on 90-day data.
- Project early: Remove unnecessary columns to reduce result size and latency.
- Filter before join: Minimize left table size to prevent memory issues.
- Monitor for cost: Large data volumes in Log Analytics cost money per GB ingested.