Free SC-200 study kit

SC-200 KQL cheat sheet

Threat hunting is 20–25% of SC-200, and the skills list starts with "identify the appropriate table to use in a KQL query". This sheet covers the tables, the operators and five queries to read and adapt.

Test yourself with the free diagnostic

Which table?

Table	Where	What it holds
`DeviceProcessEvents`	Defender XDR	Process creation and related events on onboarded devices: command lines, parent processes, hashes. The first table for suspicious PowerShell or LOLBins.
`DeviceNetworkEvents`	Defender XDR	Network connections from devices: remote IP, port, URL and the initiating process. Use it for beaconing and connections to bad infrastructure.
`DeviceLogonEvents`	Defender XDR	Sign-ins and other authentication events on devices, with logon type (interactive, network, remote interactive).
`DeviceFileEvents`	Defender XDR	File creation, modification, renaming and deletion. Use it to trace dropped payloads or mass renames from ransomware.
`DeviceRegistryEvents`	Defender XDR	Registry key and value changes, such as Run keys added for persistence.
`EmailEvents`	Defender XDR	Email delivery and blocking events from Defender for Office 365: sender, recipient, subject, delivery action and threat verdicts.
`EmailUrlInfo / EmailAttachmentInfo`	Defender XDR	The URLs and attachments in those emails, joined to EmailEvents on NetworkMessageId.
`IdentityLogonEvents`	Defender XDR	Authentication against on-premises Active Directory (Defender for Identity) and interactive Entra ID / Microsoft online sign-ins (Defender for Cloud Apps), by protocol and outcome.
`CloudAppEvents`	Defender XDR	Activity in cloud apps and services, such as Microsoft 365, captured by Defender for Cloud Apps.
`AlertInfo / AlertEvidence`	Defender XDR	Alerts and the entities attached to them (devices, users, files, IPs), joined on AlertId.
`SigninLogs`	Sentinel	Interactive Entra ID sign-ins: user, app, IP, location, result and conditional access outcome. Non-interactive sign-ins are in AADNonInteractiveUserSignInLogs.
`AuditLogs`	Sentinel	Entra ID directory changes: user and group changes, role assignments, app consent.
`SecurityEvent`	Sentinel	Windows Security event log collected by the agent, filtered by EventID (4624 logon, 4625 failed logon, 4688 process creation, 4720 user created).
`AzureActivity`	Sentinel	Azure subscription control-plane operations: who created, changed or deleted which resource.
`Syslog / CommonSecurityLog`	Sentinel	Linux syslog messages, and CEF events from firewalls and other appliances.
`OfficeActivity`	Sentinel	Microsoft 365 audit activity: Exchange, SharePoint, OneDrive and Teams operations.
`SecurityAlert / SecurityIncident`	Sentinel	Alerts from connected products and analytics rules, and the incidents that group them.

Operators you need

where: Filters rows. Put time and the most selective filters first.
| where Timestamp > ago(7d)
project: Keeps, renames or reorders columns. project-away drops columns.
| project Timestamp, DeviceName, FileName
extend: Adds a calculated column.
| extend Hour = hourofday(Timestamp)
summarize: Aggregates by groups: count(), dcount(), make_set(), arg_max().
| summarize Failures = count() by IPAddress
bin(): Rounds time into buckets for trends and charts.
| summarize count() by bin(TimeGenerated, 1h)
top / order by: top returns the first N rows by a column; order by (or sort by) sorts all rows.
| top 10 by Failures desc
take: Returns any N rows, unordered. Use it to look at a table's columns, not for analysis.
SigninLogs | take 10
distinct: Returns unique combinations of the listed columns.
| distinct AccountName, DeviceName
join: Combines two tables on a key. The default kind is innerunique; specify kind=inner, leftouter or leftanti explicitly.
| join kind=inner (EmailUrlInfo) on NetworkMessageId
union: Stacks rows from several tables.
union DeviceProcessEvents, DeviceFileEvents
let: Names a value, list or subquery to reuse later in the query.
let BadIPs = dynamic(["203.0.113.7"]);
has / contains: has matches a whole term and uses the index, so it is faster; contains matches any substring. Both are case-insensitive.
| where ProcessCommandLine has "-enc"
in / in~: Matches a list of values; in~ ignores case.
| where FileName in~ ("powershell.exe", "pwsh.exe")
parse / extract(): Pulls fields out of free text, with a pattern or a regular expression.
| extend Domain = extract(@"@(.+) quot;, 1, SenderFromAddress)
mv-expand: Turns each element of an array or bag into its own row.
| mv-expand Entity = todynamic(Entities)
render: Draws the result as a chart in the portal.
| render timechart

Five hunting queries

Encoded PowerShell on devices

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)\s[-/]e(c|n\w*)?\s"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName

Password spray against Entra ID

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "50126"
| summarize Users = dcount(UserPrincipalName), Attempts = count() by IPAddress, bin(TimeGenerated, 1h)
| where Users > 10
| order by Users desc

Who clicked a delivered phishing URL

EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Phish" and DeliveryAction == "Delivered"
| extend Recipient = tolower(RecipientEmailAddress)
| join kind=inner (UrlClickEvents
    | where ActionType == "ClickAllowed"
    | extend Recipient = tolower(AccountUpn)) on NetworkMessageId, Recipient
| project ClickTime = Timestamp1, RecipientEmailAddress, Subject, Url

Accounts added to the local Administrators group

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4732 and TargetSid == "S-1-5-32-544"
| project TimeGenerated, Computer, SubjectAccount, MemberName, MemberSid

Latest sign-in per user

SigninLogs
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, IPAddress, Location, AppDisplayName) by UserPrincipalName

Exam traps

Timestamp or TimeGenerated?: Advanced Hunting tables use Timestamp. Sentinel tables use TimeGenerated, including Defender tables streamed into Sentinel.
Advanced Hunting window: Advanced Hunting covers 30 days of native Defender XDR data; onboarded Sentinel tables follow their analytics-tier retention. Data kept only in the Sentinel data lake isn't available in Advanced Hunting: use data lake exploration for it.
Custom detection rules: Microsoft recommends returning Timestamp (or TimeGenerated) and ReportId from the same event, plus DeviceId for Defender for Endpoint tables, and at least one impacted-asset column such as DeviceId, AccountObjectId or RecipientEmailAddress. Email actions need NetworkMessageId and RecipientEmailAddress.
Scheduled vs NRT rules: Scheduled rules run on an interval over a lookback window. Sentinel NRT rules run once every minute on recently ingested data (up to 50 per tenant). In Defender custom detections, the Continuous (NRT) frequency needs a single table with no joins or unions.
Filter early: Filter on time first, then on the most selective column, before summarize or join. It is faster and stays under query limits.

Skills from the Microsoft Learn SC-200 study guide; syntax from the Kusto Query Language documentation. Checked 2 November 2026.

Reading KQL is half the job

The exam also asks what to do with the result. Practise with 483 SC-200 questions; the first 20 are free.

Start free