If you are preparing for SC-200, you do not need to memorize every KQL command. You do need to get comfortable with the query patterns that show up again and again during threat hunting. That is the real skill the exam tests, and it is also what analysts use on the job. A good cheat sheet helps, but only if you know when to use each operator, how to narrow noisy results, and how to pivot from one clue to the next. This guide walks through the KQL queries and hunting patterns you should actually practice, with examples you can adapt inside Microsoft Sentinel and Microsoft 365 Defender.
Why KQL matters in SC-200 threat hunting
Kusto Query Language, or KQL, is how you ask security data questions. In SC-200, that usually means finding suspicious logons, unusual process execution, risky network activity, or signs of persistence. The exam expects more than syntax. It expects judgment.
For example, if you see multiple failed sign-ins followed by one success, the important question is not just how do I query failed sign-ins. It is how do I detect a pattern that suggests password spraying without drowning in normal user mistakes. That is where KQL becomes useful.
The best way to prepare is to practice a small set of operators until they feel natural. Most hunting tasks rely on these building blocks:
- where to filter noise
- project to keep only useful columns
- summarize to spot spikes and outliers
- extend to create calculated fields
- sort by and top to prioritize results
- distinct to reduce duplication
- join to connect related events
- bin to group activity into time buckets
If you build confidence with those, you can solve a large share of hunting questions.
Start with the tables you will use most
KQL becomes easier when you know what kind of data lives where. In SC-200 labs and real hunting, several tables appear often:
- SecurityEvent for Windows security logs
- SigninLogs for Azure AD sign-in activity
- DeviceProcessEvents for process creation on endpoints
- DeviceNetworkEvents for outbound and inbound connections
- DeviceFileEvents for file creation, rename, and modification
- DeviceLogonEvents for endpoint logons
- DeviceRegistryEvents for registry changes
You do not need to learn every column in every table. You should know how to inspect a table quickly and identify useful fields like timestamp, account, device name, IP address, process command line, and action type.
A simple starter query:
SigninLogs
| where TimeGenerated > ago(24h)
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, ResultType
| sort by TimeGenerated desc
This is basic, but it teaches an important habit: filter by time first, then keep only the columns you need. That improves speed and makes results easier to read.
Core filters and search patterns you should practice
Threat hunting usually begins with a filter. The goal is to remove normal activity so suspicious patterns can stand out.
Practice exact matching:
DeviceProcessEvents
| where FileName == “powershell.exe”
| project Timestamp, DeviceName, InitiatingProcessAccountName, ProcessCommandLine
Use this when you know the exact value. It is stricter and often faster than loose matching.
Practice partial matching:
DeviceProcessEvents
| where ProcessCommandLine contains “EncodedCommand”
| project Timestamp, DeviceName, FileName, ProcessCommandLine
This helps when attackers may add extra arguments around a suspicious string.
Practice case-insensitive token searches:
DeviceProcessEvents
| where ProcessCommandLine has “DownloadString”
| project Timestamp, DeviceName, ProcessCommandLine
has is often better than contains when you are searching for a meaningful token. It reduces accidental matches inside longer strings.
Practice inclusion lists:
SecurityEvent
| where EventID in (4624, 4625)
| project TimeGenerated, Computer, Account, EventID, IpAddress
This is common in exam questions. You often need two or three event types together to understand the sequence.
Practice exclusions too:
DeviceProcessEvents
| where FileName in~ (“powershell.exe”, “cmd.exe”, “wscript.exe”, “cscript.exe”)
| where InitiatingProcessAccountName !in~ (“SYSTEM”, “LOCAL SERVICE”, “NETWORK SERVICE”)
| project Timestamp, DeviceName, InitiatingProcessAccountName, FileName, ProcessCommandLine
The second filter matters because system accounts often generate large amounts of expected noise.
Use summarize to find spikes, outliers, and repeated failures
Many hunts are really counting exercises. You are looking for one IP hitting many users, one device spawning too many scripts, or one account failing repeatedly across a short period.
Here is a useful sign-in pattern:
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| summarize FailedAttempts = count(), UsersTargeted = dcount(UserPrincipalName) by IPAddress
| where FailedAttempts > 20 or UsersTargeted > 5
| sort by FailedAttempts desc
This can reveal password spraying. The reason it works is simple: normal users tend to fail against one account, not many accounts from the same IP.
Another good pattern is suspicious process frequency:
DeviceProcessEvents
| where Timestamp > ago(12h)
| where FileName in~ (“powershell.exe”, “rundll32.exe”, “mshta.exe”)
| summarize Executions = count() by DeviceName, FileName
| where Executions > 10
| sort by Executions desc
This does not prove malicious behavior. It gives you a short list of systems worth checking. That is what hunting often looks like: reduce a giant dataset into a manageable review queue.
Time-window tuning is a skill, not an afterthought
A weak query often fails because the time range is wrong. Too broad, and you get noise. Too narrow, and you miss the pattern.
Practice changing your time window on purpose:
- 1 hour for bursty behavior like brute force or scripted execution
- 24 hours for daily anomalies and user activity review
- 7 days for persistence, low-and-slow activity, and baseline comparison
Use bin() to see how activity behaves over time:
SigninLogs
| where TimeGenerated > ago(24h)
| summarize Attempts = count() by bin(TimeGenerated, 1h), IPAddress
| sort by TimeGenerated asc
This helps you see bursts. A password spray often appears as a cluster of attempts in a short interval, sometimes repeating every hour as the attacker rotates infrastructure.
You can also compare current behavior with a longer baseline:
DeviceNetworkEvents
| where Timestamp > ago(7d)
| summarize Connections = count() by DeviceName, RemoteIP, bin(Timestamp, 1d)
| sort by Connections desc
This is useful for identifying a remote IP that suddenly appears for one host after days of silence.
Pivoting from one indicator to another
Good hunting is iterative. You start with one clue, then pivot. Maybe you begin with an IP address from a suspicious sign-in. Then you want related users, devices, and processes.
Start with the first clue:
SigninLogs
| where IPAddress == “203.0.113.10”
| project TimeGenerated, UserPrincipalName, AppDisplayName, ResultType, ConditionalAccessStatus
Now pivot to users affected by that IP:
SigninLogs
| where IPAddress == “203.0.113.10”
| distinct UserPrincipalName
Then pivot into endpoint activity for one user:
DeviceLogonEvents
| where AccountName == “user1”
| project Timestamp, DeviceName, LogonType, ActionType
Then inspect processes on that device around the same time:
DeviceProcessEvents
| where DeviceName == “HOST-22”
| where Timestamp between (datetime(2025-05-13 09:00:00) .. datetime(2025-05-13 11:00:00))
| project Timestamp, FileName, ProcessCommandLine, InitiatingProcessFileName
This is the mindset SC-200 rewards. One result should lead to the next question.
Join queries you should know for the exam and real hunts
join is one of the most important KQL skills because real incidents rarely stay inside one table. You often need to connect a logon with a process, or a process with a network connection.
Example: find devices where PowerShell executed and then made network connections shortly after.
let SuspiciousPowerShell = DeviceProcessEvents
| where Timestamp > ago(1d)
| where FileName == “powershell.exe”
| where ProcessCommandLine contains “EncodedCommand”
| project DeviceId, DeviceName, PS_Time = Timestamp, ProcessCommandLine;
SuspiciousPowerShell
| join kind=inner (
DeviceNetworkEvents
| where Timestamp > ago(1d)
| project DeviceId, Net_Time = Timestamp, RemoteIP, RemotePort, RemoteUrl
) on DeviceId
| where Net_Time between (PS_Time .. PS_Time + 10m)
| project DeviceName, PS_Time, Net_Time, RemoteIP, RemotePort, RemoteUrl, ProcessCommandLine
| sort by PS_Time desc
Why this matters: a suspicious script is more concerning if it is followed by outbound traffic. The join gives context that a single table cannot.
Another useful join pattern is matching failed sign-ins to later successes from the same IP:
let Failures = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| project IPAddress, UserPrincipalName, FailTime = TimeGenerated;
let Successes = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| project IPAddress, UserPrincipalName, SuccessTime = TimeGenerated;
Failures
| join kind=inner Successes on IPAddress, UserPrincipalName
| where SuccessTime between (FailTime .. FailTime + 30m)
| project UserPrincipalName, IPAddress, FailTime, SuccessTime
This can surface successful access after repeated failures. That pattern deserves review, especially for privileged accounts.
Useful hunting prompts and query ideas to practice
If you want to improve fast, practice with prompts that force you to think like an analyst. Here are strong examples:
- Find rare child processes launched by Office applications.
- Find users with failed sign-ins from multiple countries in one day.
- Find devices that connected to a new external IP and then created a scheduled task.
- Find accounts added to privileged groups outside business hours.
- Find command lines that use base64, encoded PowerShell, or suspicious LOLBins.
Example for Office spawning scripts:
DeviceProcessEvents
| where Timestamp > ago(3d)
| where InitiatingProcessFileName in~ (“winword.exe”, “excel.exe”, “outlook.exe”)
| where FileName in~ (“powershell.exe”, “cmd.exe”, “wscript.exe”, “mshta.exe”)
| project Timestamp, DeviceName, InitiatingProcessFileName, FileName, ProcessCommandLine
| sort by Timestamp desc
Example for unusual countries:
SigninLogs
| where TimeGenerated > ago(1d)
| summarize Countries = dcount(Location), CountryList = make_set(Location) by UserPrincipalName
| where Countries > 1
This is not enough by itself because travel and VPN use can explain it. But it is a useful first pass for impossible travel review.
How to make your queries cleaner and easier to defend in the exam
In SC-200, a technically correct query is not always the best answer. The better answer is usually the one that is easier to read, targets the threat pattern directly, and removes obvious noise.
These habits help:
- Filter early. Time range and key conditions should come near the top.
- Project only what you need. This reduces clutter.
- Name calculated fields clearly. Use labels like FailedAttempts or PS_Time.
- Use let statements for multi-step hunts. They make joins easier to understand.
- Test broad, then narrow. Start with visibility, then add exclusions.
For exam prep, it also helps to work through scenario-based questions where KQL is tied to investigation logic. If you want realistic SC-200-style practice, the Microsoft SC-200 practice test is a useful way to check whether you can apply query concepts under pressure, not just recognize syntax.
A practical printable cheat sheet to build for yourself
A printable KQL cheat sheet is worth making, but keep it compact. One page is ideal. If it grows too long, you will not use it.
Include these sections:
- Core operators: where, project, summarize, extend, distinct, top, sort by
- String matching: contains, has, startswith, endswith, in, in~
- Time functions: ago(), between, bin()
- Aggregation: count(), dcount(), make_set(), min(), max()
- Join patterns: process to network, failure to success, user to device
- Common suspicious items: PowerShell with EncodedCommand, Office spawning scripts, unusual logon spikes
The key is not to make a giant reference sheet. It is to create a short decision aid you can glance at while practicing. For example:
- Need to reduce noise? Use where and project.
- Need to spot abnormal volume? Use summarize with count or dcount.
- Need to connect evidence? Use join.
- Need to see bursts? Use bin with time windows.
- Need to pivot? Use distinct, then query the next table.
Final takeaway
The best SC-200 KQL practice is not memorizing isolated commands. It is learning a repeatable hunting flow: filter, count, pivot, correlate, and tune the time window. If you can do that with sign-ins, processes, and network events, you will be in good shape for the exam and for real investigations.
Start with a few queries from this cheat sheet. Run them. Change the time range. Swap one suspicious process for another. Add one more condition and see how the results change. That is how KQL starts to feel natural. And once it does, threat hunting becomes much more structured and much less overwhelming.
