kql

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:

// ============================================================
// 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.