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
- Always specify the target service — KQL varies across Azure services. A query for Sentinel won't necessarily work in ADX.
- Time-bound by default — Include
TimeGenerated filters (or equivalent) to prevent full-table scans. Default to last 24 hours unless the user specifies otherwise.
- Performance first — Filter early (
where before join/summarize), use has over contains for string matching, avoid * projections on wide tables.
- Parameterize — Use
let statements for values the user will customize (time ranges, thresholds, resource names).
- 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.
1---2name: kql3description: 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.4---5
6# KQL Skill
7
8Write, debug, optimize, translate, and automate KQL queries across Azure data platforms.
9
10## Workflow Routing
11
12| Workflow | Trigger | File |
13|----------|---------|------|
14| WriteQuery | "write a query", "create KQL", "query for", "find events where", "show me" | `workflows/WriteQuery.md` |
15| DebugOptimize | "optimize", "slow query", "fix this KQL", "improve performance", "debug query" | `workflows/DebugOptimize.md` |
16| Translate | "SQL to KQL", "Splunk to KQL", "SPL to KQL", "convert this query" | `workflows/Translate.md` |
17| Tooling | "validate KQL", "run query via CLI", "automate query", "schedule alert", "REST API" | `workflows/Tooling.md` |
18
19If no specific workflow matches, default to **WriteQuery**.
20
21## Reference Files
22
23Read these as needed — don't load everything upfront:
24
25| Reference | When to Read | File |
26|-----------|-------------|------|
27| Operators & Functions | Writing or reviewing any query | `references/operators.md` |
28| Service Tables | Need to know available tables for a specific service | `references/service-tables.md` |
29| Patterns & Anti-patterns | Optimizing queries or reviewing for best practices | `references/patterns.md` |
30| SQL-to-KQL Map | Translating from SQL | `references/sql-to-kql.md` |
31
32## Sample Queries
33
34The `samples/` directory contains production-ready `.kql` files organized by service. Reference these when writing similar queries — they demonstrate the expected file format and conventions.
35
36## Output Format
37
38Every generated query MUST use this `.kql` file format:
39
40```kql
41// ============================================================
42// Title: <descriptive title>
43// Service: <Log Analytics | Sentinel | ADX | App Insights>
44// Tables: <comma-separated list of tables used>
45// Description: <what this query does and when to use it>
46// Parameters: <any variables the user should customize>
47// Complexity: <Beginner | Intermediate | Advanced>
48// ============================================================
49
50// <the query, with inline comments for non-obvious logic>
51```
52
53## File Naming
54
55Use kebab-case: `failed-sign-ins-by-location.kql`, `high-cpu-vms-last-24h.kql`
56
57## Core Principles
58
591. **Always specify the target service** — KQL varies across Azure services. A query for Sentinel won't necessarily work in ADX.
602. **Time-bound by default** — Include `TimeGenerated` filters (or equivalent) to prevent full-table scans. Default to last 24 hours unless the user specifies otherwise.
613. **Performance first** — Filter early (`where` before `join`/`summarize`), use `has` over `contains` for string matching, avoid `*` projections on wide tables.
624. **Parameterize** — Use `let` statements for values the user will customize (time ranges, thresholds, resource names).
635. **Explain the query** — Add inline comments for non-trivial logic, especially `mv-expand`, `parse`, regex, and complex `summarize` expressions.
64
65## Service-Specific Notes
66
67- **Log Analytics**: No management commands (`.create`, `.alter`). Tables like `Heartbeat`, `Perf`, `Event`, `Syslog`, `AzureActivity`.
68- **Sentinel**: Extends Log Analytics with `SecurityEvent`, `SecurityAlert`, `SigninLogs`, `ThreatIntelligenceIndicator`, plus custom analytics rule functions.
69- **ADX**: Full KQL engine — supports management commands, materialized views, continuous exports, external tables. Most powerful but queries may not be portable.
70- **Application Insights**: Shares Log Analytics engine. Key tables: `requests`, `dependencies`, `exceptions`, `traces`, `customEvents`, `performanceCounters`.
71
72## Examples
73
74**Example 1 — Write Query:**
75> "Write a KQL query to find failed sign-ins from outside the US in the last 7 days"
76> Routes to: `workflows/WriteQuery.md` → targets Sentinel/Log Analytics, uses `SigninLogs`
77
78**Example 2 — Optimize:**
79> "This query takes forever to run, can you make it faster?" (pastes KQL)
80> Routes to: `workflows/DebugOptimize.md`
81
82**Example 3 — Translate:**
83> "Convert this SQL query to KQL: SELECT * FROM events WHERE severity > 3 GROUP BY source"
84> Routes to: `workflows/Translate.md`
85
86**Example 4 — Tooling:**
87> "How do I run this query from Azure CLI and export to CSV?"
88> Routes to: `workflows/Tooling.md`
89
90---
91
92## Gotchas
93
94- **`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.
95- **`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.
96- **`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.
97- **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.
98- **`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`.
99- **`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`.
100- **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.
julianobarbosa