L9. KQL Fundamentals: Querying Security Data
Video generating
Check back soon for the video lesson on KQL Fundamentals: Querying Security Data
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 first N rows |
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 != 0Common time literals: 1h (hour), 1d (day), 7d (week), 30d (month).
Exam tip: Always filter by TimeGenerated early in the query. It is the most effective way to reduce the data scanned and improve query performance.
The summarize Operator
summarize is the most tested operator. It groups and aggregates data:
SecurityEvent
| where EventID == 4624
| summarize LogonCount = count(),
UniqueAccounts = dcount(Account)
by Computer
| where LogonCount > 100Key aggregation functions: count(), dcount() (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.UserPrincipalNameJoin 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 > 10New process execution on a specific host:
DeviceProcessEvents
| where TimeGenerated > ago(1d)
| where DeviceName == "workstation01"
| project TimeGenerated, FileName, ProcessCommandLine, AccountName
| sort by TimeGenerated desc- ✓Always filter by TimeGenerated early in the query for performance optimization.
- ✓The summarize operator with count(), dcount(), and make_set() is heavily tested on the exam.
- ✓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 returns 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?