KQL Cheat Sheet

KQL (Kusto Query Language) is a powerful query language used in Microsoft Azure Monitor, Azure Data Explorer, and other Microsoft services for querying and analyzing data. Here’s a cheat sheet to help you with KQL:

Basic Query Structure

Query Elements:

[Table] | [Project] | [Filter] | [Sort] | [Limit]

Selecting Columns (Project)

Select All Columns:

[Table]

Select Specific Columns:

[Table] | project Column1, Column2

Filtering Data

Equal:

[Table] | where Column = Value

Not Equal:

[Table] | where Column != Value

In List:

[Table] | where Column in (Value1, Value2)

Like (Wildcard):

[Table] | where Column like "Pattern"

Sorting Data

Ascending Order:

[Table] | order by Column asc

Descending Order:

[Table] | order by Column desc

Limiting Results

Top N Records:

[Table] | top N

Bottom N Records:

[Table] | top N by Column desc

Aggregation and Grouping

Count Rows:

[Table] | summarize Count = count()

Grouping and Aggregating:

[Table] | summarize Count = count() by Column

Time-Based Queries

Filtering by Time Range:

[Table] | where Timestamp between(datetime(Start), datetime(End))

Aggregating by Time:

[Table] | summarize Count = count() by bin(Timestamp, TimeInterval)

Joining Tables

Inner Join:

[Table1] | join kind=inner ( [Table2] ) on $left.Column = $right.Column

Left Outer Join:

[Table1] | join kind=leftouter ( [Table2] ) on $left.Column = $right.Column

Logical Operators

AND:

[Table] | where Condition1 and Condition2

OR:

[Table] | where Condition1 or Condition2

Aliases and Renaming

Column Alias:

[Table] | project NewName = OldName

Table Alias:

[Table] as T | project T.Column

This cheat sheet covers some of the basic elements and commonly used operations in KQL. For more advanced queries and functions, refer to the official Microsoft KQL documentation.