L9. KQL Fundamentals: Querying Security Data
Visual workflow edition
Worked example, diagrams and a question with time to think. Synthetic American-English narration; examples are fictional. Reviewed October 2026.
Read the video transcript
Which users have the most failed sign-ins in our sample? That is a useful question to answer with Kusto Query Language, or K Q L. We will use fictional rows so you can focus on the logic. The goal is to explain every transformation, not memorize a query you cannot validate.
A tabular query starts with a table or expression. The pipe passes its result to the next operator. Where filters rows. Project selects or computes columns. Summarize groups rows and calculates aggregates. Order by sorts the output. Keep each step small enough that you can inspect what changed before adding the next one.
Our sample has two failures for Ava and one success for Ben. Filtering for failures removes Ben's row. Grouping by user and counting the remaining rows produces Ava with a count of two. That simple expected result is your first test. If your query returns something else, investigate the logic before using real security data.
Real tables have different column names and data types. Confirm the schema and use a bounded time window before adapting this example. A string comparison that works in a fictional table may be wrong for a numeric result code. Also distinguish counting events from counting distinct users. Those measurements answer different questions.
Test a matching row, a nonmatching row, and an edge case. Check missing fields and the time boundary. Inspect the output before turning a query into a scheduled detection. A syntactically valid query can still answer the wrong question. Keep the sample and expected result with your notes so another analyst can reproduce your reasoning.
Ava has two failures and Ben has one success. After filtering failures and counting by user, does the result represent two users, or two failed events for Ava? Pause and trace the rows.
It represents two failed events for Ava. Ben was filtered out, and the grouping key was the user. For practice, add one failure for Ben and predict both output rows before running the query. The linked lesson includes the query text. Small expected-result tests build habits that matter when the dataset becomes much larger.
Try the fictional KQL example
let Events = datatable(User:string, Result:string)
[
"Ava", "Failure",
"Ava", "Failure",
"Ben", "Success"
];
Events
| where Result == "Failure"
| summarize Failures=count() by User
| order by Failures descExpected result: Ava, 2. Add one failure for Ben and predict the result before running it. Real security tables use different schemas and need a bounded time window.
Course outlineLesson 9 of 18
Kusto Query Language (KQL) is the foundation of threat hunting and analytics in Microsoft Sentinel and Defender XDR. This lesson covers essential operators, time-based filtering, joins, and the query patterns SC-200 candidates encounter most on the exam.
Why KQL Matters for SC-200
KQL is used throughout the Microsoft security stack: Sentinel analytics rules, hunting queries, workbooks, Defender XDR advanced hunting, and Log Analytics queries. The SC-200 exam includes KQL-based questions where you must read, write, or correct queries.
Essential Operators
Every KQL query starts with a table name and pipes data through operators:
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize FailedLogons = count() by Account
| sort by FailedLogons desc
| take 10Key operators for the exam:
| Operator | Purpose |
|---|---|
where | Filter rows based on conditions |
summarize | Aggregate data (count, sum, avg, dcount) |
project | Select specific columns |
extend | Add calculated columns |
sort by / order by | Sort results |
take / limit | Return up to N rows; order is not guaranteed without sorting |
join | Combine data from two tables |
union | Append rows from multiple tables |
render | Visualize results as charts |
parse | Extract values from string fields |
let | Define variables or reusable subqueries |
Time-Based Filtering
The ago() function is essential for security queries:
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"Common time literals: 1h (hour), 1d (day), 7d (seven days), 30d (thirty days, not a calendar month).
Practice tip: Filter by the correct time column early to reduce the data scanned. Sentinel tables commonly use TimeGenerated; Defender XDR advanced hunting tables such as DeviceProcessEvents use Timestamp. Verify the schema before adapting a query.
The summarize Operator
summarize groups rows and calculates aggregates:
SecurityEvent
| where EventID == 4624
| summarize LogonCount = count(),
UniqueAccounts = dcount(Account)
by Computer
| where LogonCount > 100Key aggregation functions: count(), dcount() (estimated distinct count), sum(), avg(), min(), max(), make_set() (unique values as array), make_list() (all values as array).
Joins
Joins combine data from two tables based on a shared key:
SecurityEvent
| where EventID == 4625
| project TimeGenerated, Account, Computer
| join kind=inner (
SigninLogs
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress
) on $left.Account == $right.UserPrincipalNameThe example joins by account only; it does not prove that events occurred together. Normalize identity keys, bound both time ranges, and define a time-correlation condition before using it for detection. The default join flavor is innerunique; choose an explicit kind for predictable duplicate handling.
Join kinds for the exam:
inner: Only matching rows from both tablesleftouter: All rows from the left table, matching from the rightrightouter: All rows from the right table, matching from the leftfullouter: All rows from both tablesleftanti: Rows from the left table with no match in the right
leftanti is commonly used to find entities that are NOT in a watchlist or threat intelligence list.
The let Statement
let defines reusable variables or subqueries:
let suspiciousIPs = dynamic(["10.0.0.5", "192.168.1.100"]);
SigninLogs
| where IPAddress in (suspiciousIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, LocationPractical Security Queries
Failed logon brute force detection:
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize Attempts = count() by Account, Computer
| where Attempts > 10Process events for a fictional host in Defender XDR advanced hunting (replace the example with the fully qualified device name from your data). Schema: Microsoft DeviceProcessEvents reference.
DeviceProcessEvents
| where Timestamp > ago(1d)
| where DeviceName == "workstation01.example.test"
| project Timestamp, FileName, ProcessCommandLine, AccountName
| sort by Timestamp descReferences
- ✓Filter by the correct time column early: verify TimeGenerated versus Timestamp in the table schema.
- ✓Use summarize with count(), dcount(), and make_set() to answer different aggregation questions.
- ✓leftanti join finds entities NOT present in a second table, useful for watchlist exclusions.
- ✓The let statement defines reusable variables and subqueries within a KQL query.
- ✓project selects columns, extend adds calculated columns, and parse extracts values from strings.
1. You need to find user accounts that appear in the SecurityEvent table but NOT in a watchlist table. Which join type should you use?
2. Which KQL aggregation function estimates the number of unique values in a column?
3. What is the most effective way to improve KQL query performance when querying large Sentinel tables?
Keep learning
Use the knowledge check above to test your understanding. You can browse every lesson without signing in; opening another lesson does not mark this one complete.
Next lesson: Analytics Rules: Scheduled, NRT and Fusion →