SC-200 KQL explained
For SC-200 you need to read and write short KQL queries: filter a table, pick columns, count and group, join two tables and complete a query with a missing operator. KQL is not a separate domain, but it supports advanced hunting, custom detection rules, Sentinel analytics rules, hunting queries and workbooks, so it affects well over half the exam. This guide covers the patterns that matter.
How a KQL query reads
A query starts with a table and passes rows through operators separated by a pipe. Each operator receives the output of the one before it.
DeviceProcessEvents
| where Timestamp > ago(1d)
| where FileName =~ "powershell.exe"
| project Timestamp, DeviceName, ProcessCommandLine
Read it top to bottom: take process events, keep the last day, keep PowerShell, show three columns. Filter early, because it makes queries faster and cheaper.
The operators to know
| Operator | What it does |
|---|---|
where | Keeps rows that match a condition |
project | Chooses and renames columns |
extend | Adds a calculated column |
summarize | Aggregates, for example count() or dcount(), optionally by a column |
distinct | Returns unique values |
top / order by | Sorts and limits results |
take | Returns any N rows, useful for exploring a table |
join | Combines two tables on a key |
union | Stacks tables with similar columns |
let | Names a value or sub-query for reuse |
parse | Extracts values from a string |
bin() | Rounds values, usually time, into buckets |
String operators
== is an exact, case-sensitive match; =~ ignores case. has matches whole terms and is fast; contains matches any substring and is slower. in matches a list. startswith and endswith do what they say. A question that asks for the most efficient filter often wants has rather than contains.
Time
Use ago(1h), ago(7d) and between (start .. end). Defender XDR hunting tables use Timestamp; Sentinel tables use TimeGenerated. Mixing them up is a classic wrong answer.
Patterns that appear again and again
Count by something
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| summarize Failures = count() by UserPrincipalName
| top 10 by Failures desc
Failures followed by success. Summarize per user and IP with countif() for failures and for successes, then keep rows where both are above zero.
Rare things. Count a value per device or across the tenant and keep the lowest counts, which is a quick way to find unusual process names.
Time series. summarize count() by bin(Timestamp, 1h) gives hourly counts you can chart.
Join. Match events from two tables on a key, such as an account name. Know the kinds: inner keeps only matches, leftanti keeps rows from the left that have no match on the right (useful for “logged on but never did X”), leftouter keeps all left rows.
Choosing the table
Many KQL questions are really table questions. Defender XDR advanced hunting has the Device*, Email*, Identity* and CloudApp* families; Sentinel has tables such as SecurityEvent, SigninLogs, AzureActivity, Syslog, CommonSecurityLog and ThreatIntelligenceIndicator. Work out the data source first, then the table, then the filter.
Example questions
Question 1. You want the ten user accounts with the most failed sign-ins in the last day from the SigninLogs table. Which query is correct?
- A. SigninLogs | where TimeGenerated > ago(1d) | take 10
- B. SigninLogs | where ResultType != 0 | order by Failures desc
- C. SigninLogs | where TimeGenerated > ago(1d) and ResultType != 0 | summarize Failures = count() by UserPrincipalName | top 10 by Failures desc
- D. DeviceLogonEvents | where Timestamp > ago(1d) | summarize count() by AccountName
Show answer
Answer: C
It filters to the last day and failures (ResultType not equal to 0), counts per account with summarize, and takes the top ten by that count. Option A has no aggregation, so it returns rows rather than counts. Option B orders by an undefined column. Option D uses a Defender XDR table that does not hold Entra sign-in logs.
Want more questions like this? Full SC-200 practice tests →
Question 2. You have a list of devices in one table and want those that appear in the device table but have no matching row in a second table of patched devices. Which join kind returns exactly those devices?
- A. inner
- B. leftanti
- C. rightanti
- D. union
Show answer
Answer: B
A leftanti join returns rows from the left table that have no match in the right table. An inner join returns only matching rows, which is the opposite. A rightanti join returns rows from the right table without a match. A union just stacks the two tables without comparing them.
Want more questions like this? Full SC-200 practice tests →
Question 3. A query must search command lines for the term mimikatz, and the search should be as efficient as possible because the table is huge. Which filter is best?
- A. where ProcessCommandLine contains ‘mimikatz’
- B. where ProcessCommandLine == ‘mimikatz’
- C. where FileName == ‘Mimikatz’
- D. where ProcessCommandLine has ‘mimikatz’
Show answer
Answer: D
has matches whole terms using the index and is more efficient than contains, which scans for substrings. Option A with contains works but is slower. Option B with exact equality would miss command lines that contain extra text. Option C uses a case-sensitive exact match on a different column.
Want more questions like this? Full SC-200 practice tests →
How to practise
Spend 20 minutes a day writing queries in the Defender advanced hunting page and the Sentinel Logs page. Use the official KQL reference to look up any operator you do not recognise, then rewrite the query without looking. The threat hunting guide uses these patterns, and the study plan puts KQL in week one.