kql

KQL Mastery

1. KQL Basics

Kusto Query Language (KQL) is a pipe-forward query language for exploring data. It is the native query language for Azure Data Explorer (ADX), Microsoft Fabric Real-Time Intelligence (EventHouse), Azure Monitor Log Analytics, Microsoft Sentinel, and other Microsoft data services.

Pipe-forward syntax

KQL queries are a chain of operators separated by |. Data flows left to right:

StormEvents                          // start with a table
| where State == "TEXAS"             // filter rows
| summarize count() by EventType     // aggregate
| top 5 by count_ desc              // limit results

Query vs management commands

KQL has two execution planes:

Plane	Starts with	Examples
Query	Table name, let, print, datatable	StormEvents | where State == "TEXAS"
Management	.show, .create, .set, .drop, .alter	.show tables, .show table T schema

Management commands cannot be piped into query operators:

// ❌ WRONG — .show is management, | project is query
.show tables | project TableName

// ✅ RIGHT — run management and query separately
// Step 1: .show tables
// Step 2: MyTable | take 5

When in doubt: if the first token starts with ., it's a management command.

2. Dynamic Type Discipline

KQL's dynamic type is flexible but strict in certain contexts. A common mistake is using a dynamic column in summarize by, order by, or join on without casting.

The rule: Any time you use a dynamic-typed column in by, on, or order by, wrap it in an explicit cast.

// ❌ ERROR: "Summarize group key 'Partners' is of a 'dynamic' type"
| summarize count() by Partners

// ✅ FIX
| summarize count() by tostring(Partners)

// ❌ ERROR: "order operator: key can't be of dynamic type"
| order by Area desc

// ✅ FIX
| order by tostring(Area) desc

// ❌ ERROR in join: dynamic join key
| join kind=inner other on $left.Area == $right.Area

// ✅ FIX — cast both sides
| extend Area_str = tostring(Area)
| join kind=inner (other | extend Area_str = tostring(Area)) on Area_str