Skip to content

Query Syntax

KQL Query Structure and Syntax

Kusto Query Language (KQL) is the foundation of data exploration and analysis in Microsoft Sentinel. Understanding its structure and syntax is critical for crafting effective threat-hunting queries. A typical KQL query follows a pipeline model, where data flows through a sequence of operations that filter, transform, and analyze it.

1. Core Query Structure

A KQL query begins with a table name and is followed by a chain of operators connected by the | (pipe) symbol. Each operator processes the output of the previous step.

Basic syntax:

TableName
| operator1
| operator2
| ...

Example:

SecurityEvent
| where EventID == 4624
| summarize count() by Computer
This query filters logon events (EventID 4624) and counts them by computer.

2. Filtering Data with where

The where clause filters rows based on conditions. Use logical operators (and, or, not) and comparison operators (==, !=, >, <, in, between) to refine results.

Example:

Alert
| where Status == "Active" and Severity >= 3
| project TimeGenerated, AlertID, Title
This query retrieves active alerts with severity 3 or higher and projects specific columns.

Tips:
- Use parentheses for complex conditions:

where (Severity == 3 and Status == "Active") or (Severity == 4)
- Avoid filtering on large datasets early in the pipeline to optimize performance.

3. Joining Tables

Use the join operator to combine data from multiple tables. Specify the join type (inner, left, right, full) and the matching columns with on.

Example:

SecurityEvent
| join (Process | where ProcessName == "explorer.exe") on EventID
This query joins event logs with process data where the process name is explorer.exe.

Key considerations:
- Ensure matching columns exist in both tables.
- Use join sparingly for large datasets; consider filtering first.
- Use extend to add computed fields before joining.

4. Aggregation and Summarization

Aggregation functions like count(), sum(), avg(), min(), and max() summarize data. Use summarize to group results by one or more fields.

Example:

SecurityEvent
| where EventID == 4624
| summarize count() by Computer, TimeGenerated
This query counts logon events (EventID 4,624) per computer and time.

Advanced aggregations:
- Use argmax()/argmin() to find rows with extreme values:

SecurityEvent
| summarize argmax(TimeGenerated, EventID) by Computer
- Combine with extend for custom calculations:
SecurityEvent
| extend Duration = endTime - startTime
| summarize avg(Duration) by Computer

5. Projecting and Transforming Data

Use project to select specific columns and extend to add computed fields.

Example:

SecurityEvent
| project TimeGenerated, Computer, EventID, Description
| extend RiskScore = if(EventID == 4624, 10, 0)
This query limits output to key fields and adds a risk score based on event type.

Key takeaways

  • Pipeline structure: Use | to chain operations and process data step-by-step.
  • Filtering: Leverage where with logical and comparison operators for precise data selection.
  • Joining: Combine tables with join and ensure matching columns for accurate results.
  • Aggregation: Use summarize with functions like count() and avg() to derive insights.
  • Optimization: Filter and project early in the pipeline to improve performance and clarity.