Kusto Query Language (KQL) Cheat Sheet

Updated 2026-04-27

Next Topic: Medallion Architecture Cheat Sheet

🧠Study flashcards on this topic146 cards · spaced repetition→

Kusto Query Language (KQL) is a read-only query language developed by Microsoft for analyzing large volumes of structured, semi-structured, and unstructured data. Named after oceanographer Jacques Cousteau, it powers Azure Data Explorer, Azure Monitor Logs, Microsoft Sentinel, Microsoft 365 Defender, and Application Insights. KQL is optimized for telemetry, metrics, logs, and time-series analysis, providing powerful operators for aggregation, filtering, visualization, and machine learning directly in the query layer. Unlike SQL, KQL uses a pipe-based syntax that flows data transformations left-to-right, making queries readable and composable. Understanding KQL's tabular operators, scalar functions, graph semantics, and query optimization patterns is essential for security analysts, data engineers, and cloud practitioners working with Microsoft's data analytics ecosystem.

What This Cheat Sheet Covers

This topic spans 26 focused tables and 214 indexed concepts, 146 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Basic Query StructureTable 2: Filtering and ComparisonTable 3: Aggregation FunctionsTable 4: Summarize OperatorTable 5: Conditional Scalar FunctionsTable 6: String FunctionsTable 7: Datetime FunctionsTable 8: Join OperationsTable 9: Union and Set OperationsTable 10: Dynamic Arrays and JSONTable 11: Window FunctionsTable 12: Time Series AnalysisTable 13: Graph SemanticsTable 14: Advanced OperatorsTable 15: Machine Learning PluginsTable 16: VisualizationTable 17: Data TypesTable 18: Conversion FunctionsTable 19: Mathematical FunctionsTable 20: IP and Network FunctionsTable 21: Geospatial FunctionsTable 22: Cross-Database and Cross-Cluster QueriesTable 23: User-Defined FunctionsTable 24: Query Optimization TechniquesTable 25: Common Use CasesTable 26: Common Pitfalls and Gotchas

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Basic Query Structure

Every KQL query begins with a table name and pipes data through a chain of operators that filter, shape, and trim the result left-to-right. These are the building blocks you reach for constantly — where to cut rows, project to pick columns, take/top to cap output — and ordering them well (filter early, project early) is the difference between a snappy query and one that scans everything.

Operator	Example	Description
table name	`SecurityEvent`	Returns all records from a table — the starting point for every KQL query.
pipe \|	`SecurityEvent \| where TimeGenerated > ago(1d)`	• Chains operators together • data flows left-to-right through transformations.
where	`T \| where EventID == 4625`	• Filters rows based on a boolean predicate • always apply early for performance.
project	`T \| project TimeGenerated, Account`	• Selects specific columns to include • reduces output width and improves query speed.
project-away	`T \| project-away TenantId, _ResourceId`	• Excludes specified columns from output • inverse of `project` — keeps all except the listed columns.
project-keep	`T \| project-keep Time*, Computer`	• Keeps specified columns (supports wildcards) • unlike `project`, preserves original column order.
project-rename	`T \| project-rename Device = Computer`	Renames columns without dropping or reordering any other columns.
project-reorder	`T \| project-reorder TimeGenerated, Computer, *`	• Moves specified columns to the front • `` fills in the remaining columns* in original order.
extend	`T \| extend Duration = EndTime - StartTime`	Creates calculated columns without removing existing ones.
search	`search in (SecurityEvent, Heartbeat) "error"`	• Searches for a term across all columns in specified tables • useful for exploration, slower than `where`.
take (limit)	`T \| take 100`	• Returns N rows (order not guaranteed) • `limit` is an alias with identical behavior.
top	`T \| top 10 by EventCount desc`	• Returns the top N rows by a sorted column • more efficient than sort + take.
sort (order by)	`T \| sort by TimeGenerated desc`	• Orders rows • `order by` is an alias with identical behavior.
distinct	`T \| distinct ComputerName`	• Returns unique combinations of specified columns • removes duplicate rows.
count	`T \| count`	Returns the total row count as a single value.
getschema	`T \| getschema`	• Returns the column names and data types of a table • useful for exploration and debugging.

Table 2: Filtering and Comparison

The predicates you hand to where — and the single biggest performance lever in KQL is choosing the right one. A recurring theme runs through these rows: case sensitivity (== vs =~) and the gulf between token-based has and substring contains, where has is dramatically faster because it matches on indexed word boundaries rather than scanning every character.

Operator	Example	Description
== (equals)	`where Status == "Success"`	Case-sensitive exact match.

The rest of this section is part of the full cheat sheet

Table 3: Aggregation Functions

These are the functions that collapse many rows into summary numbers, and almost all of them pair with summarize. Beyond the obvious count, sum, and avg, watch for the *if variants that fold a condition into the aggregate, the distinct-count family that trades exactness for speed via HyperLogLog, and arg_max/arg_min, which return the whole winning row rather than just the extreme value.

Function	Example	Description
count()	`summarize count()`	Counts the total number of rows in each group.

The rest of this section is part of the full cheat sheet

Table 4: Summarize Operator

summarize is where aggregation actually happens — KQL's answer to SQL's GROUP BY. The patterns here show how to group by one column or several, run multiple aggregations side by side under custom names, and the time-series workhorse bin(), which buckets timestamps into fixed intervals so you can chart counts per hour or per day.

Technique	Example	Description
summarize	`T \| summarize count() by Computer`	• Groups data and applies aggregation functions • similar to SQL `GROUP BY`.

The rest of this section is part of the full cheat sheet

Table 1: Basic Query Structure

Operator	Example	Description
table name	`SecurityEvent`	Returns all records from a table — the starting point for every KQL query.
pipe \|	`SecurityEvent \| where TimeGenerated > ago(1d)`	• Chains operators together • data flows left-to-right through transformations.
where	`T \| where EventID == 4625`	• Filters rows based on a boolean predicate • always apply early for performance.
project	`T \| project TimeGenerated, Account`	• Selects specific columns to include • reduces output width and improves query speed.
project-away	`T \| project-away TenantId, _ResourceId`	• Excludes specified columns from output • inverse of `project` — keeps all except the listed columns.
project-keep	`T \| project-keep Time*, Computer`	• Keeps specified columns (supports wildcards) • unlike `project`, preserves original column order.
project-rename	`T \| project-rename Device = Computer`	Renames columns without dropping or reordering any other columns.
project-reorder	`T \| project-reorder TimeGenerated, Computer, *`	• Moves specified columns to the front • `` fills in the remaining columns* in original order.
extend	`T \| extend Duration = EndTime - StartTime`	Creates calculated columns without removing existing ones.
search	`search in (SecurityEvent, Heartbeat) "error"`	• Searches for a term across all columns in specified tables • useful for exploration, slower than `where`.
take (limit)	`T \| take 100`	• Returns N rows (order not guaranteed) • `limit` is an alias with identical behavior.
top	`T \| top 10 by EventCount desc`	• Returns the top N rows by a sorted column • more efficient than sort + take.
sort (order by)	`T \| sort by TimeGenerated desc`	• Orders rows • `order by` is an alias with identical behavior.
distinct	`T \| distinct ComputerName`	• Returns unique combinations of specified columns • removes duplicate rows.
count	`T \| count`	Returns the total row count as a single value.
getschema	`T \| getschema`	• Returns the column names and data types of a table • useful for exploration and debugging.

Table 2: Filtering and Comparison

Operator	Example	Description
== (equals)	`where Status == "Success"`	Case-sensitive exact match.

Table 3: Aggregation Functions

Function	Example	Description
count()	`summarize count()`	Counts the total number of rows in each group.

Table 4: Summarize Operator

Technique	Example	Description
summarize	`T \| summarize count() by Computer`	• Groups data and applies aggregation functions • similar to SQL `GROUP BY`.