This blog post introduces a concise Kusto Query Language (KQL) cheat sheet for Azure Data Explorer, designed as a quick reference for querying large datasets. Each item in the cheat sheet is explained below with practical examples to help you write effective KQL queries. Download the cheat sheet above for easy access.
Operators
where
Filters rows based on a condition, narrowing down results.
Example: StormEvents | where DamageProperty > 10000 returns events with property damage exceeding $10,000.
project
Selects specific columns to include in the output, reducing data clutter.
Example: Logs | project Timestamp, User outputs only the Timestamp and User columns from the Logs table.
join
Combines two tables based on a common key, with options like inner, leftouter, etc.
Example: Requests | join kind=inner Logs on RequestId merges Requests and Logs tables where RequestId matches.
union
Combines rows from multiple tables or queries into a single result set.
Example: union Errors, Warnings combines rows from Errors and Warnings tables.
search
Searches for a term across all columns, ideal for exploratory queries.
Example: Logs | search "error" finds "error" in any column of the Logs table.
== (Equality)
Tests if a column exactly matches a value.
Example: Users | where Name == "john" returns rows where the Name column is exactly "john".
!= (Inequality)
Filters rows where a column does not match a value.
Example: Logs | where Status != 200 excludes successful HTTP responses.
in
Checks if a column value is in a specified set, simplifying multiple comparisons.
Example: Logs | where Status in (400, 404, 500) matches bad request, not found, or server error status codes.
has
Matches rows where a column contains a term (case-sensitive with has_cs).
Example: Logs | where Message has "error" finds rows with "error" in the Message column.
Functions
count()
Counts rows in a group or result set, useful for summarizing data.
Example: Logs | summarize count() by Status counts occurrences of each HTTP status code.
avg()
Calculates the average of a numeric column.
Example: Requests | summarize avg(Duration) computes the average request duration.
sum()
Sums values in a numeric column.
Example: Logs | summarize sum(Bytes) totals the bytes transferred in the Logs table.
max()
Finds the highest value in a column.
Example: Events | summarize max(Duration) identifies the longest event duration.
min()
Finds the lowest value in a column.
Example: Requests | summarize min(Latency) returns the shortest latency.
ago()
Specifies a time relative to the current time for filtering.
Example: Logs | where Timestamp > ago(1h) filters logs from the last hour.
tostring()
Converts a value to a string for display or comparison.
Example: Logs | project tostring(Status) converts the Status column to strings.
Syntax
| (Pipe)
Chains operations, passing results from one step to the next.
Example: Logs | where Status == 200 | project User filters for successful responses and selects the User column.
summarize
Groups data and applies aggregations like count or sum.
Example: Logs | summarize count() by User counts log entries per user.
order by
Sorts results by a column in ascending (asc) or descending (desc) order.
Example: Logs | order by Timestamp desc sorts logs by timestamp, newest first.
take
Limits the number of rows returned, useful for sampling.
Example: Logs | take 100 returns the first 100 rows of the Logs table.
extend
Adds calculated columns to the result set.
Example: Requests | extend ResponseTimeSec = Duration / 1000 creates a new column with duration in seconds.
Range Queries
Filters values within a range using between or comparison operators.
Example: Logs | where Status between(400 .. 499) matches client error status codes (400–499).
Case Sensitivity
KQL operators are case-insensitive, but string functions like has have case-sensitive variants (has_cs).
Example: Logs | where Message has "ERROR" may match "error" unless using has_cs.
Comments
Single-line comments start with //, ignored during query execution.
Example: Logs | where Status == 200 // Filter successful requests includes a comment for clarity.
This KQL cheat sheet and its explanations provide a quick reference for querying data in Azure Data Explorer. Download the cheat sheet above and keep it handy for your next KQL session!