What are some popular KQL query elements?

Kusto Query Language (KQL) is an impressively powerful tool for analyzing data (especially logs). In many respects, the query basics are similar to SQL syntax. I'll have more in-depth articles about this subject in the future but in the meantime, here's a brief cheat sheet with common/popular conditions and operators.

FILTERING:

• | where Column == "value" // Exact match (case-sensitive)
• | where Column =~ "value" // Case-insensitive exact match
• | where Column contains "text" // Substring (case-insensitive)
• | where Column has "word" // Word boundary match (faster)
• | where Column startswith "pre" // Starts with
• | where Column endswith "suf" // Ends with
• | where Column matches regex "^A.*" // Regular expression

TIME OPERATIONS:

• | where TimeGenerated > ago(1h)     // Last hour
• | where TimeGenerated > ago(1d)     // Last day (24 hours)
• | where TimeGenerated > ago(7d)     // Last 7 days
• | where TimeGenerated > ago(30d)    // Last 30 days
• | where TimeGenerated between (ago(7d) .. ago(1d))  // Range

LIMITING & SORTING:

• | take 100                          // First 100 rows (unsorted)
• | top 10 by Column desc             // Top 10 sorted by column
• | order by Column asc               // Sort ascending
• | order by Column desc              // Sort descending

AGGREGATION:

• | summarize count() by Column       // Count by group
• | summarize dcount(Column)          // Distinct count
• | summarize sum(Column)             // Sum values
• | summarize avg(Column)             // Average
• | summarize min(Column), max(Column) // Min and max

PROJECTION & TRANSFORMATION:

• | project Col1, Col2                // Select specific columns
• | project-away Col1                 // Exclude columns
• | extend NewCol = expression        // Add calculated column
• | extend Hour = bin(TimeGenerated, 1h)  // Time binning

For more information, check out these resources:

SQL to Kusto query translation - Kusto

Learn about the Kusto Query Language equivalent of SQL queries.

Microsoft Learnspelluru

KQL Quick Reference - Kusto

Learn how to use KQL functions like `where`, `summarize`, and `render` with syntax examples to streamline your data queries.

Microsoft Learnspelluru

Tutorial: Learn common Kusto Query Language operators - Kusto

This tutorial describes how to write queries using common operators in the Kusto Query Language to meet common query needs.

Microsoft Learnspelluru