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?
├─  100 million rows → Partition by time, use external tools

How fast does this need to run?
├─  ago(24h)
   | where Activity == "Failed"
   | project TimeGenerated, Computer, Account, LogonType
   ```

2. **Add aggregation**:
   ```kql
   // 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**:
   ```kql
   // 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**:
   ```kql
   // 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):
   ```kql
   // ✓ 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**:
   ```kql
   // ✓ 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**:
   ```kql
   // ✗ 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**:
   ```kql
   // ✗ 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**:
   ```kql
   // 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**:
   ```kql
   // 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**:
   ```kql
   // 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  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):
   ```kql
   // 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**:
   ```kql
   // 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**:
   ```kql
   // 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):
   ```kql
   // 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):
   ```kql
   // 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.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [manu14357](https://github.com/manu14357)
- **Source:** [manu14357/zskills](https://github.com/manu14357/zskills)
- **License:** MIT
- **Homepage:** https://zskills.vercel.app

Install and usage instructions live in the source repository linked above.