azure-kusto

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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**:
   ```kql
   // 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**:
   ```kql
   // 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):
   ```kql
   // Find all failed logins in last 24 hours
   SecurityEvent
   | where TimeGenerated > 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**:

From manu14357/zskills, MIT.

More from manu14357/zskills