KQL Essentials — where, summarize, project, join

KQL (Kusto Query Language) is the query language for Microsoft Sentinel, Microsoft Defender, and Azure Monitor Log Analytics. It’s also the basis for Azure Data Explorer. KQL is increasingly important as Microsoft security products dominate the enterprise market.

KQL Fundamentals

KQL is a read-only query language — you can only retrieve data, not modify it. The query starts with a table name and is built using pipe operators:

SecurityEvent                           // table (= source)
| where EventID == 4625                 // filter
| where TimeGenerated > ago(1h)         // time filter
| where Account !contains "ANONYMOUS"   // string filter
| summarize FailCount=count() by Account, Computer    // aggregate
| where FailCount > 10                  // filter on aggregate
| order by FailCount desc               // sort
| project Computer, Account, FailCount  // select columns

Time is special in KQL

// Relative time
TimeGenerated > ago(1h)    // last hour
TimeGenerated > ago(24h)   // last 24 hours
TimeGenerated > now() - 7d // last 7 days

// Absolute time range
TimeGenerated between (datetime(2024-03-15) .. datetime(2024-03-16))

Always include a time filter. Without one, Sentinel will query potentially years of data.

The Essential KQL Operators

where — filter

SecurityEvent
| where EventID == 4688
| where NewProcessName has "powershell"           // case-insensitive contains
| where CommandLine contains_cs "Invoke-Mimikatz" // case-sensitive
| where NewProcessName matches regex @"\\cmd\.exe$" // regex
| where Process in ("cmd.exe", "powershell.exe", "wscript.exe") // multi-value
| where isnotempty(CommandLine)                   // null check

String operators:

contains / !contains — substring match (case-insensitive)
contains_cs — case-sensitive contains
startswith / endswith — position-specific
matches regex — full regex
has — whole-word match
in / !in — membership in list

summarize — aggregate

SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(1h)
| summarize
    FailCount = count(),
    UniqueAccounts = dcount(Account),
    UniqueHosts = dcount(Computer),
    FirstAttempt = min(TimeGenerated),
    LastAttempt = max(TimeGenerated)
  by tostring(IpAddress)
| where FailCount > 20 and UniqueAccounts > 5

Key aggregate functions:

count() — total rows
dcount(field) — distinct count (approximate)
countif(condition) — conditional count
avg(field), min(field), max(field), sum(field)
make_set(field) — list of unique values
make_list(field) — list of all values

extend — add computed fields

SecurityEvent
| where EventID == 4688
| extend ProcessName = tostring(split(NewProcessName, "\\")[-1])
| extend IsScriptEngine = ProcessName in ("powershell.exe", "wscript.exe", "cscript.exe", "mshta.exe")
| where IsScriptEngine
| project TimeGenerated, Computer, Account, ProcessName, CommandLine, ParentProcessName

project — select columns

// project: select specific columns (drops the rest)
| project TimeGenerated, Computer, Account, EventID

// project-rename: select and rename
| project-rename Timestamp=TimeGenerated, Host=Computer

// project-away: drop specific columns
| project-away TenantId, SourceSystem, _ResourceId

Joining for Correlation

Inner join: accounts that failed then succeeded

let Failures = SecurityEvent
    | where EventID == 4625 and TimeGenerated > ago(1h)
    | summarize FailCount=count() by Account, Computer;
let Successes = SecurityEvent
    | where EventID == 4624 and TimeGenerated > ago(1h)
    | project Account, Computer, SuccessTime=TimeGenerated;
Failures
| join kind=inner Successes on Account, Computer
| where FailCount > 5
| project Account, Computer, FailCount, SuccessTime
| order by FailCount desc

Left anti-join: find accounts that ONLY failed (never succeeded)

let Failures = SecurityEvent
    | where EventID == 4625 and TimeGenerated > ago(24h)
    | summarize FailCount=count() by Account;
let Successes = SecurityEvent
    | where EventID == 4624 and TimeGenerated > ago(24h)
    | distinct Account;
Failures
| join kind=leftanti Successes on Account
| where FailCount > 50

This finds accounts with many failures but no successes — likely locked out or non-existent accounts being brute-forced.

Detection Pattern: Impossible Travel

// Same account logging in from two geographically distant IPs within a short time
let LoginEvents = SigninLogs
    | where TimeGenerated > ago(24h)
    | where ResultType == 0  // success
    | project TimeGenerated, UserPrincipalName, IPAddress, Location;
LoginEvents
| join kind=inner LoginEvents on UserPrincipalName
| where TimeGenerated1 > TimeGenerated
| where abs(datetime_diff('minute', TimeGenerated1, TimeGenerated)) < 60
| where IPAddress != IPAddress1
| where Location != Location1
| project
    Account = UserPrincipalName,
    Login1_Time = TimeGenerated, Login1_IP = IPAddress, Login1_Location = Location,
    Login2_Time = TimeGenerated1, Login2_IP = IPAddress1, Login2_Location = Location1,
    MinutesBetween = abs(datetime_diff('minute', TimeGenerated1, TimeGenerated))
| where MinutesBetween < 60
| order by MinutesBetween asc

Worked Example

Translating the failed logon SPL query to KQL

SPL version (from the previous lesson):

index=windows EventCode=4625 earliest=-1h
| stats count as FailCount, dc(TargetUserName) as UniqueAccounts by IpAddress
| where FailCount > 10 AND UniqueAccounts > 5

KQL equivalent:

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize FailCount=count(), UniqueAccounts=dcount(Account) by IpAddress
| where FailCount > 10 and UniqueAccounts > 5
| order by UniqueAccounts desc

Note the direct mapping: index=windows EventCode=4625 → SecurityEvent | where EventID == 4625. stats count by → summarize count() by. dc() → dcount(). where → where. The logic is identical; the syntax differs.

KQL Reference— Microsoft Docs Sentinel KQL Cheat Sheet— Microsoft

Retrieval Practice

Write a KQL query to find the top 10 most common parent-child process pairs (ParentProcessName → NewProcessName) from Windows process creation events in the last 24 hours.

Reveal answer

`SecurityEvent | where EventID == 4688 | where TimeGenerated > ago(24h) | extend ParentName = tostring(split(ParentProcessName, '\\\\')[-1]), ChildName = tostring(split(NewProcessName, '\\\\')[-1]) | summarize Count=count() by ParentName, ChildName | order by Count desc | take 10`. Key points: split() on backslash (needs double-escape in KQL: '\\\\') then [-1] gets the last element (filename from path); summarize groups by the pair; take 10 returns the top N. This query is useful for baselining — run it on 30 days of data, identify normal pairs (explorer.exe→cmd.exe), then query for pairs that never appear in the baseline but appear now.

Retrieval Practice

A KQL query using join is running slowly and timing out. The analyst says: 'Both tables have millions of rows.' What are two ways to optimize the query?

Reveal answer

(1) Pre-filter both tables aggressively before the join: add time filters, EventID filters, and any field filters that reduce row count. The join operator in KQL works best when both input sets are small — ideally < 100k rows each. (2) Use the hint.strategy='shuffle' or hint.strategy='broadcast' join hints: broadcast is faster when one table is very small (< 10MB) — it distributes the small table to all nodes. Shuffle distributes both tables by the join key — better for large-large joins. Also: check if you actually need join — sometimes a left anti-join or leftsemi can replace a full join with much less overhead.