Azure Kusto

Azure 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

1. 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)
   

2. 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)

1. 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, LogonType
   

2. Add aggregation:

   // Count failed logins by computer
   SecurityEvent
   | where TimeGenerated > ago(24h)
   | where Activity == "Failed"
   | summarize FailureCount = count() by Computer
   | sort by FailureCount desc
   

3. Add time-series:

   // Failed logins over time (hourly)
   SecurityEvent
   | where TimeGenerated > ago(7d)
   | where Activity == "Failed"
   | summarize FailureCount = count() by bin(TimeGenerated, 1h), Computer
   | render timechart
   

4. Join 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

1. 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!)
   

2. 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 | count
   

3. Avoid 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 == ResourceId
   

4. Use 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

1. 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 desc
   

2. API 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 desc
   

3. Error 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"
   

4. 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% failure
   

5. Cost 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

1. 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 days
   

2. Check for null values:

   // Missing or unexpected data
   SecurityEvent
   | where TimeGenerated > ago(24h)
   | where isempty(Account) or isnan(LogonType)
   | count
   

3. Validate 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

1. 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 anomalychart
   

2. Materialized 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

1. Query Code

   • KQL syntax (ready to copy-paste into Azure Portal)
   • Documented with comments explaining each step

2. Query Explanation

   • What data is queried?
   • What filters are applied?
   • What output is returned?
   • Expected result size?

3. Performance Notes

   • Query execution time estimate
   • Data volume scanned
   • Optimization tips if applicable

4. 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.