kql

name

kql

description

Kusto Query Language authoring, debugging, optimization, translation, and tooling for Azure Monitor, Sentinel, ADX, and Application Insights. USE WHEN user mentions 'KQL', 'Kusto', 'Log Analytics query', 'Sentinel query', 'hunting query', 'ADX query', 'Application Insights query', 'translate SQL to KQL', 'Splunk to KQL', 'optimize query', 'KQL performance', '.kql file', 'detection rule', 'analytics rule', 'threat hunting', 'Azure monitor query', 'log query', 'summarize operator', 'where TimeGenerated', OR any request involving querying Azure log/telemetry data. Even if the user doesn't say "KQL" explicitly — if they're asking about querying Azure logs, security events, or telemetry data, this skill applies.

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.