kql

What this skill does

# KQL Skill

Write, debug, optimize, translate, and automate KQL queries across Azure data platforms.

## Workflow Routing

| Workflow | Trigger | File |
|----------|---------|------|
| WriteQuery | "write a query", "create KQL", "query for", "find events where", "show me" | `workflows/WriteQuery.md` |
| DebugOptimize | "optimize", "slow query", "fix this KQL", "improve performance", "debug query" | `workflows/DebugOptimize.md` |
| Translate | "SQL to KQL", "Splunk to KQL", "SPL to KQL", "convert this query" | `workflows/Translate.md` |
| Tooling | "validate KQL", "run query via CLI", "automate query", "schedule alert", "REST API" | `workflows/Tooling.md` |

If no specific workflow matches, default to **WriteQuery**.

## Reference Files

Read these as needed — don't load everything upfront:

| Reference | When to Read | File |
|-----------|-------------|------|
| Operators & Functions | Writing or reviewing any query | `references/operators.md` |
| Service Tables | Need to know available tables for a specific service | `references/service-tables.md` |
| Patterns & Anti-patterns | Optimizing queries or reviewing for best practices | `references/patterns.md` |
| SQL-to-KQL Map | Translating from SQL | `references/sql-to-kql.md` |

## Sample Queries

The `samples/` directory contains production-ready `.kql` files organized by service. Reference these when writing similar queries — they demonstrate the expected file format and conventions.

## Output Format

Every generated query MUST use this `.kql` file format:

```kql
// ============================================================
// Title: <descriptive title>
// Service: <Log Analytics | Sentinel | ADX | App Insights>
// Tables: <comma-separated list of tables used>
// Description: <what this query does and when to use it>
// Parameters: <any variables the user should customize>
// Complexity: <Beginner | Intermediate | Advanced>
// ============================================================

// <the query, with inline comments for non-obvious logic>
```

## File Naming

Use kebab-case: `failed-sign-ins-by-location.kql`, `high-cpu-vms-last-24h.kql`

## Core Principles

1. **Always specify the target service** — KQL varies across Azure services. A query for Sentinel won't necessarily work in ADX.
2. **Time-bound by default** — Include `TimeGenerated` filters (or equivalent) to prevent full-table scans. Default to last 24 hours unless the user specifies otherwise.
3. **Performance first** — Filter early (`where` before `join`/`summarize`), use `has` over `contains` for string matching, avoid `*` projections on wide tables.
4. **Parameterize** — Use `let` statements for values the user will customize (time ranges, thresholds, resource names).
5. **Explain the query** — Add inline comments for non-trivial logic, especially `mv-expand`, `parse`, regex, and complex `summarize` expressions.

## Service-Specific Notes

- **Log Analytics**: No management commands (`.create`, `.alter`). Tables like `Heartbeat`, `Perf`, `Event`, `Syslog`, `AzureActivity`.
- **Sentinel**: Extends Log Analytics with `SecurityEvent`, `SecurityAlert`, `SigninLogs`, `ThreatIntelligenceIndicator`, plus custom analytics rule functions.
- **ADX**: Full KQL engine — supports management commands, materialized views, continuous exports, external tables. Most powerful but queries may not be portable.
- **Application Insights**: Shares Log Analytics engine. Key tables: `requests`, `dependencies`, `exceptions`, `traces`, `customEvents`, `performanceCounters`.

## Examples

**Example 1 — Write Query:**
> "Write a KQL query to find failed sign-ins from outside the US in the last 7 days"
> Routes to: `workflows/WriteQuery.md` → targets Sentinel/Log Analytics, uses `SigninLogs`

**Example 2 — Optimize:**
> "This query takes forever to run, can you make it faster?" (pastes KQL)
> Routes to: `workflows/DebugOptimize.md`

**Example 3 — Translate:**
> "Convert this SQL query to KQL: SELECT * FROM events WHERE severity > 3 GROUP BY source"
> Routes to: `workflows/Translate.md`

**Example 4 — Tooling:**
> "How do I run this query from Azure CLI and export to CSV?"
> Routes to: `workflows/Tooling.md`

---

## Gotchas

- **`ago()` is evaluated at query parse time, not row time** — `where TimeGenerated > ago(1h)` and `| extend Age = now() - TimeGenerated` use different `now()` snapshots by milliseconds. Cache the value in a `let now_ = now();` if comparison matters.
- **`contains` is case-insensitive AND non-indexed; `has` is indexed but token-boundary only:** `where Message has "error"` won't match `errors` (different token). For substring matches use `contains_cs` / `contains` knowing they full-scan.
- **`summarize` without `by` returns one row, hiding all grouping bugs** — if you forgot the `by` clause and got 1 row, that's why. Always project at least one dimension during dev.
- **Cross-cluster `join` is fine; cross-workspace `join` silently truncates** to the first workspace's data set if the table name collides. Use `workspace("foo").Table` aliases on both sides.
- **`extend` is evaluated lazily** — a `where` after it filters BEFORE the extend computes, which is fast but means columns referenced in `where` must already exist. Reorder: `where` first, then `extend`.
- **`SigninLogs` and `AADSignInEventsBeta` are different tables with different schemas** in Sentinel — queries built for one fail on the other with cryptic "column not found" errors. Check `print Tables = "<expected>"` against `getschema`.
- **Sentinel analytics rules cap at 10,000 results per run** silently — your hunting query that returned 50K rows interactively will alert on only 10K. Use `| take 10000` explicitly to surface the limit during testing.