\n\nTry it yourself: All
\n✅examples in this skill can be run against the public help cluster:\nhttps://help.kusto.windows.net, databaseSamples(containsStormEvents,SimpleGraph_Nodes/Edges,nyc_taxi, and more).
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.
\nKQL queries are a chain of operators separated by |. Data flows left to right:
StormEvents // start with a table\n| where State == \"TEXAS\" // filter rows\n| summarize count() by EventType // aggregate\n| top 5 by count_ desc // limit results\nKQL has two execution planes:
\n| Plane | \nStarts with | \nExamples | \n
|---|---|---|
| Query | \nTable name, let, print, datatable | \nStormEvents \\| where State == \"TEXAS\" | \n
| Management | \n.show, .create, .set, .drop, .alter | \n.show tables, .show table T schema | \n
Management commands can be followed by query operators (the output is tabular), but the entire request runs on the management plane. You cannot start with a query and pipe into a management command.
\n// ✅ WORKS — management command piped to query operators\n.show tables | project TableName | where TableName has \"Events\"\n\n// ❌ WRONG — query piped into management command\nStormEvents | take 5 | .show tables\nWhen in doubt: if the first token starts with ., it's a management command. For a full catalog of schema exploration commands, see references/discovery-queries.md.
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 ... is of a 'dynamic' type\"\nStormEvents | summarize count() by StormSummary.Details.Location\n\n// ✅ FIX\nStormEvents | summarize count() by tostring(StormSummary.Details.Location)\n// ❌ ERROR: \"order operator: key can't be of dynamic type\"\nStormEvents | order by StormSummary.TotalDamages desc\n\n// ✅ FIX\nStormEvents | order by tolong(StormSummary.TotalDamages) desc\n// ❌ ERROR in join: dynamic join key\nStormEvents | join kind=inner (PopulationData) on $left.StormSummary == $right.State\n\n// ✅ FIX — cast both sides\nStormEvents\n| extend State_str = tostring(StormSummary.Details.Location)\n| join kind=inner (PopulationData) on $left.State_str == $right.State\nSelf-correction: When you see \"is of a 'dynamic' type\" in an error, add tostring(), tolong(), or todouble().
KQL joins have constraints that differ from SQL.
\nKQL join conditions support only ==. No <, >, !=, or function calls in join predicates.
// ❌ ERROR: \"Only equality is allowed in this context\"\nStormEvents | join (nyc_taxi) on geo_distance_2points(BeginLon, BeginLat, pickup_longitude, pickup_latitude) < 1000\n\n// ✅ WORKAROUND — pre-bucket into spatial cells, then join on cell ID\nStormEvents\n| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)\n| join kind=inner (nyc_taxi | extend cell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 8)) on cell\nFor range joins, pre-bin values: | extend bin_val = bin(Value, 100), then join on bin_val. Note: values near bin boundaries may land in adjacent bins — consider checking neighboring bins or overlapping the range for precision.
Both sides of a join on clause must reference column entities only — not expressions, not aggregates.
// ❌ ERROR: \"for each left attribute, right attribute should be selected\"\nStormEvents | join kind=inner (PopulationData) on $left.State\n\n// ✅ FIX — specify both sides explicitly\nStormEvents | join kind=inner (PopulationData) on $left.State == $right.State\nAlways check cardinality before joining tables with >10K rows. A cross-join explosion was the source of the single E_RUNAWAY_QUERY error (25K × 195 = potential 4.8M rows).
// Before joining, check how many rows each side contributes\nStormEvents | summarize dcount(State) // → 67 distinct states\nPopulationData | summarize dcount(State) // → 52 — safe to join\nKQL handles regex natively — no need for Python.
\nextract_all gotchaUnlike Python's re.findall(), KQL's extract_all requires capturing groups in the regex:
// ❌ ERROR: \"extractall(): argument 2 must be a valid regex with [1..16] matching groups\"\nStormEvents | extend words = extract_all(@\"[a-zA-Z]{3,}\", EventNarrative)\n\n// ✅ FIX — add parentheses around the pattern\nStormEvents | extend words = extract_all(@\"([a-zA-Z]{3,})\", EventNarrative)\n| Function | \nUse case | \nExample | \n
|---|---|---|
extract(regex, group, source) | \nSingle match | \nextract(@\"User '([^']+)'\", 1, Msg) | \n
extract_all(regex, source) | \nAll matches (needs ()) | \nextract_all(@\"(\\w+)\", Text) | \n
parse | \nStructured extraction | \nparse Msg with * \"User '\" Sender \"' sent\" * | \n
matches regex | \nBoolean filter | \nwhere Url matches regex @\"^https?://\" | \n
replace_regex | \nFind and replace | \nreplace_regex(Text, @\"\\s+\", \" \") | \n
Window functions need serialized (ordered) input.
\n// ❌ ERROR: \"Function 'row_cumsum' cannot be invoked. The row set must be serialized.\"\nStormEvents\n| where State == \"TEXAS\"\n| summarize DailyCount = count() by bin(StartTime, 1d)\n| extend CumulativeCount = row_cumsum(DailyCount)\n\n// ✅ FIX — add | serialize (or | order by, which implicitly serializes)\nStormEvents\n| where State == \"TEXAS\"\n| summarize DailyCount = count() by bin(StartTime, 1d)\n| order by StartTime asc\n| extend CumulativeCount = row_cumsum(DailyCount)\nFunctions requiring serialization: row_number(), row_cumsum(), prev(), next(), row_window_session().
The most common memory error. Caused by scanning too much data without pre-filtering.
\nSafest ──────────────────────────────────────────────── Most dangerous\n| count | take 10 | where + summarize | summarize (no filter) | full scan\n| count to understand table size| where before | summarize — filter time range, partition key, or category firstdcount() on high-cardinality columns without pre-filteringmaterialize() for subqueries referenced multiple times// ❌ OUT OF MEMORY — large table, no filter, many group-by columns\nStormEvents\n| summarize dcount(EventType), count() by StartTime, State, Source\n| where dcount_EventType > 1\n\n// ✅ SAFE — filter first, then aggregate\nStormEvents\n| where StartTime between (datetime(2007-04-15) .. datetime(2007-04-16))\n| summarize dcount(EventType) by State, Source\n| where dcount_EventType > 1\nE_LOW_MEMORY_CONDITIONThe query touched too much data. Your options:
\n| where filters (time range, partition key)by columns in summarize| sample 10000 for exploratory work instead of full scansE_RUNAWAY_QUERYA join or aggregation produced too many output rows. Check join cardinality — one or both sides is too large.
\nLarge results slow down analysis. Prevention:
\n| Query type | \nSafeguard | \n
|---|---|
| Exploratory | \nAlways end with \\| take 10 or \\| take 20 | \n
| Aggregation | \nUse \\| top 20 by ... not unbounded summarize | \n
| Wide rows (vectors, JSON) | \n\\| project only needed columns | \n
make_list() / make_set() | \nAvoid on high-cardinality groups (produces huge cells) | \n
| Unknown size | \nRun \\| count first | \n
The vector trap: Tables with embedding columns (1536-dim float arrays) produce ~30KB per row. Even | take 20 yields 600KB. Always | project away vector columns unless you specifically need them.
KQL sometimes requires explicit casts when comparing computed string values — even when both sides are already strings.
\n// ❌ ERROR: \"Cannot compare values of types string and string. Try adding explicit casts\"\nStormEvents | where geo_point_to_s2cell(BeginLon, BeginLat, 16) == other_cell\n\n// ✅ FIX — wrap both sides in tostring()\nStormEvents | where tostring(geo_point_to_s2cell(BeginLon, BeginLat, 16)) == tostring(other_cell)\nThis is most common with computed values from geo_point_to_s2cell() and strcat() comparisons. When in doubt, cast with tostring().
KQL handles these natively — no need for Python:
\n// try it! — cosine similarity on Iris feature vectors\nlet target = pack_array(5.1, 3.5, 1.4, 0.2);\nIris\n| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)\n| extend sim = series_cosine_similarity(Vec, target)\n| top 5 by sim desc\n// Distance between two points (meters)\nStormEvents | extend dist = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)\n\n// Spatial bucketing for joins\nStormEvents | extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)\n// Persistent graph model — try it on the help cluster!\ngraph(\"Simple\")\n| graph-match (src)-[e*1..3]->(dst)\n where src.name == \"Alice\"\n project src.name, dst.name, path_length = array_length(e)\n\n// Transient graph — build inline with make-graph\nSimpleGraph_Edges\n| make-graph source --> target with SimpleGraph_Nodes on id\n| graph-match (src)-[e*1..5]->(dst)\n where src.name == \"Alice\"\n project src.name, dst.name, path_length = array_length(e)\n// try it! — create a time series and detect anomalies\nStormEvents\n| make-series count() default=0 on StartTime step 1d\n| extend anomalies = series_decompose_anomalies(count_)\nFor detailed examples and patterns, consult references/advanced-patterns.md.
When you encounter an error, look it up here before retrying:
\n| Error message contains | \nLikely cause | \nFix | \n
|---|---|---|
is of a 'dynamic' type | \nDynamic column in by/on/order by | \nWrap in tostring()/tolong() | \n
Only equality is allowed | \nRange predicate in join condition | \nPre-bucket with S2/H3 cells or bin() | \n
extractall(): matching groups | \nMissing () in regex | \nAdd (): @\"(\\w+)\" not @\"\\w+\" | \n
row set must be serialized | \nWindow function on unsorted data | \nAdd \\| serialize or \\| order by before it | \n
Cannot compare values of types string and string | \nComputed string comparison | \nAdd tostring() on both sides | \n
Failed to resolve column named 'X' | \nWrong column name or wrong table | \nRun .show table T schema to check column names | \n
E_LOW_MEMORY_CONDITION | \nQuery touched too much data | \nAdd \\| where filters, reduce time range, break into steps | \n
E_RUNAWAY_QUERY | \nJoin/aggregation produced too many rows | \nCheck cardinality before joining; add pre-filters | \n
for each left attribute, right attribute | \nJoin on clause incomplete | \nUse explicit form: on $left.X == $right.Y | \n
needs to be bracketed | \nReserved word used as identifier | \nUse ['keyword'] syntax | \n
plugin doesn't exist | \nUnavailable plugin on this cluster | \nFall back to equivalent function or Python | \n
Expected string literal in datetime() | \nBare integer in datetime literal | \nUse datetime(2024-01-01) not datetime(2024) | \n
Unexpected token after by | \nComplex expression in summarize by-clause | \nextend the expression first, then summarize by the column | \n
not recognized / unknown operator | \nOperator not available on this engine | \nCheck operator support; try equivalent (order by = sort by) | \n
Datetime literals are a common source of errors. A wrong literal format can cascade into completely different approaches instead of fixing the small issue.
\n// ❌ WRONG — bare year is not a valid datetime\nStormEvents | where StartTime > datetime(2007)\n\n// ✅ RIGHT — always use full date format\nStormEvents | where StartTime > datetime(2007-01-01)\n// ❌ WRONG — comparing datetime column to integer\nStormEvents | where StartTime == 2007\n\n// ✅ RIGHT — use datetime_part() to extract components\nStormEvents | where datetime_part(\"year\", StartTime) == 2007\n\n// ✅ ALSO RIGHT — use between with datetime range\nStormEvents | where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31T23:59:59))\n// This works, but can be harder to read and reuse in complex queries\nStormEvents | summarize count() by startofmonth(StartTime)\n\n// Clearer — extend first, then summarize by the computed column\nStormEvents\n| extend Month = startofmonth(StartTime)\n| summarize count() by Month\n| order by Month asc\n| Function | \nPurpose | \nExample | \n
|---|---|---|
bin(ts, 1h) | \nRound down to bucket boundary | \nbin(Timestamp, 1d) | \n
startofmonth(ts) | \nFirst day of month | \nstartofmonth(Timestamp) | \n
datetime_part(\"hour\", ts) | \nExtract component | \ndatetime_part(\"year\", Timestamp) | \n
format_datetime(ts, fmt) | \nFormat as string | \nformat_datetime(Timestamp, \"yyyy-MM\") | \n
ago(1d) | \nRelative time | \nwhere Timestamp > ago(1d) | \n
between(a .. b) | \nRange filter (inclusive) | \nwhere Timestamp between (datetime(2024-01-01) .. datetime(2024-01-31T23:59:59)) | \n
todatetime(str) | \nParse string → datetime | \ntodatetime(\"2024-01-15T10:30:00Z\") | \n
totimespan(str) | \nParse string → timespan | \ntotimespan(\"01:30:00\") | \n
KQL has subtle differences from SQL syntax.
\n| Entity | \nConvention | \nExample | \n
|---|---|---|
| Tables | \nUpperCamelCase | \nStormEvents, NetworkLogs | \n
| Columns | \nUpperCamelCase | \nStartTime, EventType | \n
Variables (let) | \nsnake_case | \nlet filtered_events = ... | \n
| Built-in functions | \nsnake_case | \nformat_bytes(), geo_distance_2points() | \n
| Stored functions | \nUpperCamelCase | \n.create function GetTopUsers | \n
// In where clauses, == is case-sensitive, =~ is case-insensitive\nStormEvents | where State == \"TEXAS\" | count // exact match\nStormEvents | where State =~ \"texas\" | count // case-insensitive\n\n// In joins, use == only\nStormEvents | join kind=inner (PopulationData) on State\nBoth sort by and order by work identically in KQL — they are aliases. Use whichever you prefer, but be consistent.
// contains: substring match (slower)\nStormEvents | where EventNarrative contains \"tree\" // finds \"trees\", \"treetop\" too\n\n// has: term/word match (faster, uses index)\nStormEvents | where EventNarrative has \"tree\" // matches word boundaries only\n\n// For exact prefix/suffix\nStormEvents | where EventType startswith \"Thunder\"\nStormEvents | where Source endswith \"Spotter\"\nWhen a first KQL query fails, the temptation is to abandon the entire approach and try something completely different. The correct response is almost always to fix the specific error, not change strategy.
\nQuery 1: extract(@\"pattern\", 1, col) → Parse error\nQuery 2: todynamic(col) → Different error \nQuery 3: parse_json(col) → Another error\nQuery 4: Python script → Works but 10x tokens\nQuery 1: extract(@\"pattern\", 1, col) → Parse error (bad escaping)\nQuery 2: extract(@\"pattern\", 1, col) → Fix the specific escaping issue → Success\nRules for error recovery:
\nparse operator is often simpler than extract() for structured text:// Instead of complex regex on TraceLogs:\n// extract(@\"file path: \\\"\\\"([^\\\"]+)\\\"\\\"\", 1, Message)\n\n// Use parse for structured extraction (try it on help cluster, SampleLogs db):\ncluster(\"help\").database(\"SampleLogs\").TraceLogs\n| where Message has \"file path\"\n| parse Message with * \"file path: \\\"\\\"\" FilePath \"\\\"\\\"\" *\n| project Timestamp, FilePath\n| take 5\nBefore running any KQL query, mentally check:
\n| where before any | summarize| take N or | top Nby/on/order by is wrappedextract_all patterns have () around what you want to capturedcount() before joining| project to drop unneeded columnsdatetime(2024-01-01) not datetime(2024) or bare integers| extend first, then | summarize by the computed column