KQL for Security Analysts

Kusto Query Language (KQL) is a read-only query language designed for querying large semi-structured datasets in Azure Data Explorer (ADX). Microsoft Sentinel stores all its data in Log Analytics workspaces, which are built on ADX. Therefore, every query you run in Sentinel — whether in the Logs blade, hunting queries, analytics rules, or workbooks — is KQL. The SC-200 exam expects you to be proficient in reading and modifying KQL queries to investigate security incidents.

Unlike SQL, KQL is designed for stream processing and is case-sensitive. The core syntax is: source | operator1 | operator2 | ... where the pipe (|) passes the result of one operator to the next. The source is typically a table name (e.g., SecurityEvent, SigninLogs, AlertEvidence).

When you submit a KQL query, the ADX engine parses it into a tree of operators. Each operator consumes a tabular input and produces a tabular output. The engine optimizes the order of operators (e.g., pushing filters down to reduce data early). The key is that operators are applied sequentially, but the engine may reorder them as long as the result is equivalent.

For example:

SecurityEvent
| where EventID == 4625
| where Account == "jdoe"
| project TimeGenerated, Account, IpAddress

The engine may combine the two where clauses into a single filter. The project operator then reduces the columns. The result is a table with three columns.

Each table in Sentinel has a schema: a set of columns with specific data types. Common data types include: - string: text (case-sensitive comparisons!) - int, long: integer numbers - datetime: date and time (always in UTC in Sentinel) - dynamic: JSON-like structures (e.g., AdditionalDetails in AlertEvidence) - timespan: time intervals

You can view table schemas using:

SecurityEvent
| getschema

Or in the Logs blade, you can expand the Tables pane.

#### where — Filtering Rows The most common operator. Use it to narrow down to relevant events. Always filter early to reduce data.

SecurityEvent
| where EventID == 4625  // Failed logon
| where TimeGenerated > ago(1d)

Note: == for equality, != for inequality, contains for substring (case-insensitive), has for exact term (case-insensitive). For case-sensitive, use contains_cs and has_cs.

#### project — Selecting Columns Use to reduce output columns. Also used to rename columns with project-rename.

SigninLogs
| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress

#### extend — Adding Computed Columns Create new columns based on existing ones.

SecurityEvent
| extend AccountDomain = split(Account, '\\')[0]
| extend AccountName = split(Account, '\\')[1]

#### summarize — Aggregating Data Group rows and compute aggregates. Common functions: count(), dcount(), sum(), avg(), min(), max(), make_set(), make_list().

SigninLogs
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where FailedCount > 10

The bin function rounds time to the nearest hour (or any interval). This is crucial for time-series analysis.

#### join — Combining Tables Merge rows from two tables based on a key. Types: inner, leftouter, rightouter, fullouter, leftanti, rightanti, leftsemi, rightsemi.

AlertEvidence
| where AlertId == "some-id"
| join kind=inner (
    SecurityEvent
    | where TimeGenerated > ago(1d)
) on $left.EntityId == $right.TargetUserName

Security analysts often use leftanti to find events in one table that don't have a match in another (e.g., sign-ins without alerts).

#### let — Defining Variables Use to create reusable queries or constants.

let threshold = 10;
let suspiciousUsers = (
    SigninLogs
    | where ResultType == "50057"
    | summarize Count = count() by UserPrincipalName
    | where Count > threshold
);
suspiciousUsers
| join kind=inner (IdentityLogonEvents) on UserPrincipalName

KQL has built-in functions for security: ago(), startofday(), endofday(), parse_json(), tostring(), todatetime(). The parse operator extracts patterns from strings.

SecurityEvent
| parse EventData with * '<Data Name="TargetUserName">' UserName '</Data>' *
| project UserName

SecurityEvent
| where EventID == 4625
| summarize FailedCount = count() by Account, IpAddress, bin(TimeGenerated, 5m)
| where FailedCount > 5

AlertEvidence
| where AlertId in (AlertInfo | where Severity == "High" | project AlertId)
| join kind=inner (DeviceEvents) on $left.EntityId == $right.DeviceName

VMConnection
| where Direction == "outbound"
| summarize TotalBytes = sum(BytesSent) by SourceIp, DestinationIp, bin(TimeGenerated, 1h)
| where TotalBytes > 1000000

Example of mv-expand:

AlertEvidence
| where AlertId == "..."
| mv-expand AdditionalDetails
| evaluate bag_unpack(AdditionalDetails)

Use take 10 to see sample data. Use count to see row counts. Use getschema to verify columns. In the Logs blade, you can see query statistics (CPU, data scanned) to identify performance issues.

A SOC analyst detects a high number of failed logon events (EventID 4625) from a single IP address targeting multiple domain admin accounts. The analyst writes a KQL query to identify the scope:

SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(1h)
| summarize FailedCount = count() by IpAddress, Account, bin(TimeGenerated, 5m)
| where FailedCount > 10
| order by FailedCount desc

The query reveals the attacking IP, the targeted accounts, and the time pattern. The analyst then uses join to correlate with SecurityEvent for successful logons (EventID 4624) from the same IP to determine if any accounts were compromised. In production, this query runs across thousands of domain controllers; performance is critical. The team optimizes by filtering on LogonType and using has for IP comparisons. Common misconfiguration: forgetting to bin TimeGenerated, leading to an overwhelming number of rows.

A Sentinel analytics rule fires a high-severity alert for malware detected on a device. The analyst needs to see all processes executed on that device around the alert time. They use:

let alertTime = AlertInfo | where AlertId == "..." | project TimeGenerated;
let deviceName = AlertEvidence | where AlertId == "..." | where EntityType == "Device" | project EntityId;
DeviceEvents
| where DeviceName in (deviceName)
| where TimeGenerated between (alertTime - 1h .. alertTime + 1h)
| project TimeGenerated, ActionType, FileName, ProcessCommandLine

This query uses let to define reusable variables, making it readable. In production, the between operator is essential for time windows. A common mistake is using ago() with a fixed duration instead of a dynamic window based on the alert time. The team also uses materialize() for large datasets to cache results.

A security engineer monitors unusual download activity from SharePoint. They query:

OfficeActivity
| where Operation == "FileDownloaded"
| where TimeGenerated > ago(7d)
| summarize DownloadCount = count() by UserId, ClientIP, bin(TimeGenerated, 1h)
| where DownloadCount > 50
| join kind=inner (IdentityInfo) on $left.UserId == $right.UserPrincipalName
| project UserId, UserDisplayName, ClientIP, DownloadCount, TimeGenerated

This query identifies users with excessive downloads, then joins with user identity data for context. In large enterprises, the OfficeActivity table can be huge; filtering by operation and time early is crucial. The team uses dcount() to estimate distinct files downloaded. A pitfall: the ClientIP column may contain IPv6 addresses; using tostring() and parse_ipv4() helps normalize.