KQL Foundations for Threat Hunters

Why KQL matters for SC-200

The most important tables

Knowing which table to query is half the battle. Here are the key tables in Defender XDR Advanced Hunting:

Endpoint tables (Defender for Endpoint)

Table	What It Contains	When to Use
DeviceEvents	Miscellaneous device events (ASR triggers, tamper protection, etc.)	Catch-all for non-standard events
DeviceProcessEvents	Process creation with command lines	Hunting for malicious processes, LOLBins
DeviceNetworkEvents	Network connections from devices	C2 communication, data exfiltration
DeviceFileEvents	File creation, modification, deletion	Malware drops, data staging
DeviceRegistryEvents	Registry modifications	Persistence mechanisms
DeviceLogonEvents	Logon events on devices	Lateral movement, credential use
DeviceImageLoadEvents	DLL loading events	DLL sideloading, injection

Email tables (Defender for Office 365)

Table	What It Contains
EmailEvents	Email metadata (sender, recipient, subject, delivery action)
EmailAttachmentInfo	Attachment details (name, type, hash)
EmailUrlInfo	URLs found in emails
EmailPostDeliveryEvents	Post-delivery actions (ZAP removals, user reports)

Identity tables

Table	What It Contains
IdentityLogonEvents	Sign-in events from Entra ID and on-prem AD
IdentityQueryEvents	AD queries (LDAP, DNS) — reconnaissance detection
IdentityDirectoryEvents	AD directory changes (group membership, password resets)

Cloud app tables

Table	What It Contains
CloudAppEvents	Activities in connected cloud apps (Defender for Cloud Apps)

💡 Exam tip: table selection is heavily tested

The exam often describes a hunting scenario and asks “which table should you query?” Match the data type:

• “Find processes running on endpoints” → DeviceProcessEvents
• “Find network connections from devices” → DeviceNetworkEvents
• “Find phishing emails” → EmailEvents + EmailUrlInfo
• “Find suspicious sign-ins” → IdentityLogonEvents
• “Find file downloads on devices” → DeviceFileEvents
• “Find LDAP reconnaissance” → IdentityQueryEvents

Essential KQL operators

KQL queries flow from left to right through pipe operators:

Table
| where TimeGenerated > ago(24h)
| where ColumnName == "value"
| project Column1, Column2, Column3
| sort by Column1 desc
| take 10

The operators you must know

Operator	What It Does	Example
where	Filter rows matching a condition	where FileName == "powershell.exe"
project	Select specific columns	project Timestamp, DeviceName, FileName
extend	Create calculated columns	extend DurationMin = Duration / 60
summarize	Aggregate data (count, sum, avg)	summarize Count = count() by DeviceName
sort by / order by	Order results	sort by Timestamp desc
take / limit	Return first N rows	take 100
join	Combine two tables on a common column	join kind=inner (Table2) on CommonColumn
render	Visualise results as chart	render timechart

String operators

Operator	What It Does	Case-Sensitive?
==	Exact match	Yes
=~	Exact match (case-insensitive)	No
has	Contains a whole word token	No
contains	Contains substring anywhere	No
startswith	Starts with string	No
matches regex	Regular expression match	Yes
has_any	Contains any of the listed values	No
in	Matches any value in a list	Yes

💡 Exam tip: has vs contains

has is faster than contains because it matches whole word tokens (indexed search). contains does substring matching (full scan).

• "powershell" has "power" → false (not a whole token)
• "powershell" contains "power" → true (substring match)
• "Microsoft PowerShell" has "PowerShell" → true (whole word token)

For the exam: use has when looking for whole terms, contains when looking for substrings. has is the recommended default for performance.

Your first hunting queries

Find suspicious PowerShell activity

DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| where ProcessCommandLine has_any ("Invoke-WebRequest", "wget", "curl", "DownloadString", "DownloadFile")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| sort by Timestamp desc

Find failed sign-ins from unusual locations

IdentityLogonEvents
| where Timestamp > ago(7d)
| where ActionType == "LogonFailed"
| summarize FailedCount = count() by AccountUpn, Location
| where FailedCount > 10
| sort by FailedCount desc

Find emails with suspicious attachments

EmailAttachmentInfo
| where Timestamp > ago(24h)
| where FileType in ("exe", "scr", "js", "vbs", "ps1", "bat", "cmd")
| join kind=inner (EmailEvents) on NetworkMessageId
| project Timestamp, SenderFromAddress, RecipientEmailAddress, Subject, FileName, FileType

Question

What is the difference between 'has' and 'contains' in KQL?

Click or press Enter to reveal answer

Answer

'has' matches whole word tokens (indexed, fast). 'contains' matches substrings anywhere (full scan, slower). Example: 'PowerShell' has 'Power' = false. 'PowerShell' contains 'Power' = true. Use 'has' as default for performance.

Click to flip back

Question

Which table would you query to find lateral movement via RDP?

Click or press Enter to reveal answer

Answer

DeviceLogonEvents — filter for LogonType = RemoteInteractive. This table captures all logon events on devices including interactive, network, and remote (RDP) logons.

Click to flip back

Question

What does the 'summarize' operator do in KQL?

Click or press Enter to reveal answer

Answer

Aggregates rows by grouping columns. Common functions: count() (number of rows), sum(), avg(), min(), max(), dcount() (distinct count), make_set() (unique values as array). Example: summarize FailedLogins = count() by UserName groups failed logins per user.

Click to flip back

Knowledge Check

Tyler wants to find all devices that connected to a specific C2 IP address (198.51.100.42) in the last 24 hours. Which table should he query?

ADeviceProcessEventsBIdentityLogonEventsCDeviceFileEventsDDeviceNetworkEvents

Check Answer

Knowledge Check

Anika writes a query: `DeviceProcessEvents | where FileName has 'power'`. What results will this return?

AAll processes with 'power' anywhere in the filename (e.g., powershell.exe, powerpoint.exe)BOnly exact matches for 'power' as a whole word token — not powershell.exe or powerpoint.exeCAn error — 'has' does not work on file namesDAll processes where the filename starts with 'power'

Check Answer

Next up: KQL foundations are set. Now let’s write real-world Advanced Hunting queries in Defender XDR — detecting threats across endpoints, email, and identity.