This chapter covers the fundamentals of Kusto Query Language (KQL) as used in Microsoft Sentinel. KQL is the primary language for querying security logs and threat hunting in Sentinel. While SC-900 does not require writing complex queries, you must understand KQL syntax, operators, and common patterns to interpret queries and understand Sentinel's capabilities. Approximately 5-10% of exam questions touch on KQL basics, typically in the context of identifying what a query does or selecting the correct operator for a scenario. This chapter will prepare you to read and explain basic KQL queries confidently.
Jump to a section
Imagine a massive library with millions of books, each representing a security log entry. The library has a card catalog system where each card has fields: Title (event type), Author (user), Date (timestamp), Subject (resource), and Location (source IP). A librarian (KQL) can search this catalog using structured queries. For example, if you want all books checked out by 'John' in the last week, you write a query: 'Find cards where Author='John' and Date after last Monday'. The librarian then walks through the catalog, pulls matching cards, and returns a stack. But KQL is more powerful: it can also compute statistics, like 'How many books did John check out per day?' by grouping cards by date and counting. Or find patterns: 'Which books were checked out by the same person who checked out 'Malware 101'?' by joining cards on Author. The key is that the catalog is indexed by timestamp and other fields, so the librarian can quickly skip irrelevant sections—just like KQL uses time filters to scan only relevant data. Without a time filter, the librarian would have to walk the entire catalog, which could take hours. In Sentinel, logs are stored in tables (like catalog sections), and KQL queries specify which table, time range, and filters to retrieve only relevant events. This is why KQL is essential: it turns a haystack of logs into a needle-finding machine.
What is KQL and Why Does Sentinel Use It?
Kusto Query Language (KQL) is a read-only language designed for querying large datasets in Azure Data Explorer (ADX) and, by extension, Microsoft Sentinel. Sentinel stores its security data in Log Analytics workspaces, which are built on ADX. KQL is optimized for time-series data, making it ideal for security logs that are timestamped and high-volume. Unlike SQL, KQL is pipeline-based: data flows from one operator to the next using the pipe (|) symbol. This allows you to filter, sort, group, and join data in a linear, readable way. On the SC-900 exam, you will not be asked to write a query from scratch, but you may be asked to identify the purpose of a query, choose the correct operator (e.g., where vs. extend), or understand the result of a simple query.
Basic KQL Syntax Structure
A KQL query begins with a table name (or a query that returns a table), followed by one or more operators separated by pipes. The general form is:
TableName
| operator1 [parameters]
| operator2 [parameters]
...For example:
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| project Account, Computer, TimeGeneratedThis query starts with the SecurityEvent table, filters for events in the last hour, keeps only EventID 4624 (successful logon), and then projects (selects) only the Account, Computer, and TimeGenerated columns. The result is a table with those three columns and rows matching the filters.
Key Operators You Must Know
#### where Operator
The where operator filters rows based on a condition. It is the most common operator. Conditions can use comparison operators (==, !=, >, <, >=, <=), string operators (contains, startswith, endswith, matches regex), and logical operators (and, or, not).
Example:
SigninLogs
| where UserPrincipalName contains "admin"
| where ResultType == "0"This retrieves successful sign-ins where the UPN contains "admin".
#### project Operator
The project operator selects a subset of columns. It can also rename columns using project NewName = OldName.
Example:
SecurityAlert
| project AlertName, TimeGenerated, Severity#### extend Operator
The extend operator adds new calculated columns to the result set. It does not remove existing columns.
Example:
SigninLogs
| extend RiskLevel = case( RiskDetail == "none", "Low", RiskDetail == "signInRisk", "Medium", "High" )#### summarize Operator
The summarize operator groups rows by one or more columns and applies an aggregation function (e.g., count(), sum(), avg(), dcount(), make_list()). This is used for statistics and pattern detection.
Example:
SigninLogs
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where FailedAttempts > 10This counts failed sign-ins per user per hour and returns only those with more than 10 failures—a brute-force attack indicator.
#### join Operator
The join operator merges rows from two tables based on a matching key. Types include inner, leftouter, rightouter, fullouter, leftanti, rightanti, etc. The syntax is:
Table1 | join kind=inner (Table2) on KeyColumnExample:
SecurityEvent
| where EventID == 4688 // Process creation
| join kind=inner (
SecurityEvent
| where EventID == 4624 // Logon
) on $left.Account == $right.TargetUserNameThis joins process creation events with logon events on the account name to find processes created right after a logon.
#### let Statement
The let statement defines a variable that can hold a scalar value, a table, or a function. It is useful for reusing values or breaking down complex queries.
Example:
let threshold = 10;
let suspiciousUsers = SigninLogs
| where ResultType != "0"
| summarize count() by UserPrincipalName
| where count_ > threshold;
suspiciousUsers
| join kind=inner (SigninLogs) on UserPrincipalNameTime Filters and the ago() Function
Time filters are critical for performance and relevance. The ago() function returns a datetime offset from the current UTC time. Common uses:
- ago(1h) — last hour
- ago(7d) — last 7 days
- ago(30m) — last 30 minutes
Using a time filter is best practice because Sentinel tables can contain terabytes of data. Without a time filter, the query may scan the entire table, causing timeouts or excessive costs.
String Operators
== — exact match (case-sensitive)
contains — substring match (case-insensitive)
startswith — string starts with (case-insensitive)
endswith — string ends with (case-insensitive)
matches regex — regular expression match
has — token match (word boundary, case-insensitive)
Example:
SecurityEvent
| where Account has "admin"has is faster than contains because it checks for whole words.
Common Aggregation Functions
count() — number of rows
dcount() — approximate distinct count (faster than distinct+count)
sum() — sum of a numeric column
avg() — average of a numeric column
min() / max() — minimum/maximum value
make_list() — creates a JSON array of values
make_set() — creates a JSON array of distinct values
Sorting and Limiting
order by (or sort by) — sorts rows ascending or descending
project-away — removes columns (opposite of project)
take — returns the first N rows (non-deterministic without sort)
limit — alias for take
Example:
SigninLogs
| where TimeGenerated > ago(1d)
| order by TimeGenerated desc
| take 10This returns the 10 most recent sign-in events.
How Sentinel Integrates KQL
Sentinel uses KQL in several places: - Analytics rules: Scheduled queries that run periodically to generate alerts. - Hunting queries: Pre-built or custom queries to proactively search for threats. - Workbooks: Visualizations powered by KQL queries. - Investigation graph: Queries that fetch related entities. - Threat hunting: Live queries using the KQL editor.
Performance Considerations
Always include a time filter using TimeGenerated column.
Use has instead of contains when possible.
Use project early to reduce columns.
Use summarize with bin() to bucket time series.
Avoid join on large tables without filtering first.
SC-900 Relevance
The exam expects you to:
Recognize the purpose of common operators (where, project, summarize, join).
Understand that KQL is the query language for Sentinel.
Identify that KQL queries start with a table name and use pipes.
Know that ago() is used for time filtering.
Differentiate between extend (adds column) and project (selects columns).
Understand that summarize with count() can detect brute-force attacks.
Not required to write queries, but must interpret simple ones.
Identify the data source
Every KQL query begins with a table name. In Sentinel, tables correspond to log types like `SecurityEvent`, `SigninLogs`, `CommonSecurityLog`, `Alert`, etc. The table name is the first token in the query. For example, `SecurityEvent` refers to Windows security events collected from monitored machines. You must know the table name to start querying. The exam may present a query like `SecurityEvent | ...` and ask what data source it uses. Always look at the first word after the pipe (if any) — but the first word before any pipe is the source table.
Filter rows with where
The `where` operator filters rows based on a Boolean expression. It is the most common operator. For performance, apply time filters first using the `TimeGenerated` column and the `ago()` function. For example, `| where TimeGenerated > ago(1d)` limits results to the last day. Then add other conditions like `EventID == 4624` or `UserPrincipalName contains 'admin'`. Multiple conditions can be combined with `and`/`or`. The `where` operator reduces the dataset before subsequent operations, improving speed. On the exam, you may be asked what a query does; if it has a `where` clause, it is filtering.
Select columns with project
The `project` operator selects a subset of columns to display. This reduces the result size and focuses on relevant fields. For example, `| project TimeGenerated, Account, Computer` returns only those three columns. You can also rename columns using `project NewName = OldName`. Unlike `extend`, `project` drops all other columns. On the exam, a query with `project` is likely aiming to narrow down the output. A common trap is confusing `project` with `extend` — `project` removes columns, `extend` adds columns.
Add calculated columns with extend
The `extend` operator creates new columns based on expressions. It does not remove existing columns. For example, `| extend RiskLevel = case(Severity == 0, 'Low', Severity == 1, 'Medium', 'High')` adds a human-readable risk level. This is useful for enrichment. On the exam, if you see `extend`, it means new columns are being added. A common wrong answer is saying `project` adds columns — it does not; it selects.
Aggregate with summarize
The `summarize` operator groups rows by one or more columns and applies aggregation functions. The syntax is `summarize [aggregation] by [grouping columns]`. For example, `| summarize count() by Account` counts events per account. Use `bin()` to group time into buckets: `summarize count() by bin(TimeGenerated, 1h)`. Common aggregations include `count()`, `dcount()`, `sum()`, `avg()`. On the exam, `summarize` indicates statistical analysis or pattern detection, like counting failed logins to detect brute force.
Enterprise Scenario 1: Brute-Force Detection
A large enterprise uses Sentinel to monitor sign-in logs. They create a scheduled analytics rule that runs every 5 minutes and queries SigninLogs for failed sign-ins (ResultType != '0') in the last 5 minutes. The query groups by UserPrincipalName and IPAddress, counts failures, and alerts if the count exceeds 10. The query:
SigninLogs
| where TimeGenerated > ago(5m)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, IPAddress
| where FailedCount > 10This detects brute-force attacks in near real-time. Misconfiguration: If the time filter is omitted, the query scans all historical data, causing high latency and cost. If the threshold is too low, false positives flood the SOC.
Enterprise Scenario 2: Lateral Movement Hunting
A security analyst hunts for lateral movement by correlating process creation events (EventID 4688) with logon events (EventID 4624) across multiple machines. The query joins SecurityEvent on Account and TargetUserName to find processes spawned immediately after a logon from a different machine. The query:
let logons = SecurityEvent
| where EventID == 4624
| where TimeGenerated > ago(1d)
| project LogonTime = TimeGenerated, Account = TargetUserName, Computer;
let processes = SecurityEvent
| where EventID == 4688
| where TimeGenerated > ago(1d)
| project ProcessTime = TimeGenerated, Account = SubjectUserName, ProcessName, Computer;
logons
| join kind=inner processes on Account
| where ProcessTime between (LogonTime .. LogonTime + 5m)This helps identify compromised accounts. Common pitfall: Not using project to reduce columns before join, causing memory issues.
Enterprise Scenario 3: Compliance Reporting
A financial institution must report all privileged account logons weekly. They run a query on SigninLogs filtering for users in a specific group (using Identity table join) and export results. The query:
let privilegedUsers = Identity
| where Group == "Domain Admins"
| project UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName in (privilegedUsers)
| project TimeGenerated, UserPrincipalName, IPAddress, ResultTypePerformance consideration: The in operator with a large list can be slow; use let with a subquery or materialize for efficiency.
SC-900 Exam Focus on Basic KQL Queries
The SC-900 exam (Microsoft Security, Compliance, and Identity Fundamentals) covers KQL basics under objective 3.2: Describe the capabilities of Microsoft Sentinel. Specifically, you should be able to:
Identify that KQL is the query language used in Sentinel.
Recognize the purpose of common operators: where, project, extend, summarize, join.
Understand that ago() is used for time filtering.
Interpret the output of a simple KQL query.
Differentiate between project and extend.
Common Wrong Answers
Confusing `project` with `extend`: Many candidates think project adds columns. In reality, project selects existing columns and drops others; extend adds new columns.
Thinking KQL is SQL: KQL is not SQL; it uses pipes and different syntax (e.g., where instead of WHERE, summarize instead of GROUP BY). The exam may present a SQL-like query as a distractor.
Misunderstanding `summarize`: Some think summarize returns all rows with an additional column. Actually, it groups rows and returns one row per group.
Ignoring time filters: A query without a time filter is inefficient. The exam may ask why a query is slow; the answer is often "no time filter."
Specific Numbers and Terms
ago(1d) for one day, ago(7d) for seven days, ago(30m) for 30 minutes.
count() returns the number of rows.
dcount() returns approximate distinct count.
bin(TimeGenerated, 1h) buckets by hour.
EventID == 4624 is successful logon; EventID == 4625 is failed logon.
Edge Cases
ago(0d) returns events from the beginning of the current UTC day.
take without order by returns arbitrary rows.
join without filtering can cause memory exhaustion.
Eliminating Wrong Answers
If a question asks what operator adds a column, look for extend.
If a question asks what operator selects columns, look for project.
If a question asks how to count events by user, look for summarize count() by User.
If a question asks how to filter by time, look for where TimeGenerated > ago(...).
KQL is the query language for Microsoft Sentinel, using a pipeline (|) syntax.
Always start a query with a table name (e.g., SecurityEvent, SigninLogs).
Use `where` to filter rows; always include a time filter with `TimeGenerated > ago(...)`.
Use `project` to select columns; use `extend` to add calculated columns.
Use `summarize` with aggregation functions like `count()` to group and aggregate data.
The `ago()` function returns a datetime offset from current UTC time (e.g., ago(1d) for last day).
Common EventIDs: 4624 (successful logon), 4625 (failed logon), 4688 (process creation).
SC-900 expects you to interpret simple KQL queries, not write them from scratch.
A query without a time filter is inefficient and may time out.
The `join` operator merges two tables on a key; use `kind` to specify join type.
These come up on the exam all the time. Here's how to tell them apart.
project
Selects a subset of existing columns
Drops all columns not listed
Can rename columns (project NewName = OldName)
Cannot create new columns
Result has fewer columns than input
extend
Adds new calculated columns
Keeps all existing columns
Cannot rename existing columns
Creates new columns based on expressions
Result has more columns than input
Mistake
KQL is the same as SQL.
Correct
KQL is a different language. It uses a pipeline syntax with pipes (|), and operators like `where`, `summarize`, and `project`. SQL uses SELECT, FROM, WHERE, GROUP BY. KQL is read-only and optimized for time-series data.
Mistake
The `project` operator adds new columns.
Correct
`project` selects a subset of existing columns and drops all others. It can rename columns but cannot create new ones. To add columns, use `extend`.
Mistake
The `summarize` operator returns all original rows plus an aggregated column.
Correct
`summarize` groups rows by the `by` columns and returns one row per group, with aggregated values. It does not return individual rows.
Mistake
A time filter is optional and has no impact on performance.
Correct
Time filters are critical. Without them, the query scans the entire table, potentially causing timeouts and high costs. Always use `where TimeGenerated > ago(...)`.
Mistake
The `contains` operator is case-sensitive.
Correct
`contains` is case-insensitive. For case-sensitive matching, use `contains_cs`.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
KQL (Kusto Query Language) is a read-only query language designed for large datasets in Azure Data Explorer. Sentinel uses KQL because its logs are stored in Log Analytics workspaces built on ADX. KQL is optimized for time-series data and uses a pipeline syntax (|) that makes it easy to filter, aggregate, and join security events. On the SC-900 exam, know that KQL is the primary query language for Sentinel.
The `where` operator filters rows based on a Boolean condition. For example, `| where EventID == 4624` keeps only rows where EventID equals 4624. You can combine conditions with `and`, `or`, and `not`. Always include a time filter like `| where TimeGenerated > ago(1d)` to limit the data scanned. The `where` operator is the most common and should be applied early in the query.
`project` selects a subset of existing columns and drops all others. It can rename columns but cannot create new ones. `extend` adds new calculated columns while keeping all existing columns. For example, `project User, TimeGenerated` returns only those two columns; `extend RiskLevel = "High"` adds a new column. The exam may ask which operator adds a column (extend) or which selects columns (project).
Use the `summarize` operator with `count()` by the user column. Example: `SecurityEvent | summarize count() by Account`. This groups events by Account and returns the count per account. You can also bin by time: `summarize count() by Account, bin(TimeGenerated, 1h)`. The `summarize` operator is essential for statistical analysis and pattern detection.
`ago(1d)` returns a datetime value representing the current UTC time minus one day. It is used in time filters to query recent data. For example, `where TimeGenerated > ago(1d)` retrieves events from the last 24 hours. Common values: `ago(30m)` for 30 minutes, `ago(7d)` for 7 days. The exam may test that `ago()` is the function for time offsets.
No, Sentinel uses KQL, not SQL. While there are some similarities (e.g., both use SELECT-like projections), KQL has a different syntax and pipeline model. You cannot run SQL queries directly in Sentinel. The exam may present a SQL query as a distractor; the correct answer will involve KQL.
The `summarize` operator groups rows by one or more columns and applies aggregation functions (e.g., count(), sum(), avg()). It returns one row per group. For example, to count failed logins per user: `SigninLogs | where ResultType != "0" | summarize FailedCount = count() by UserPrincipalName`. This is commonly used in security analytics to detect anomalies like brute-force attacks.
You've just covered Basic KQL Queries in Sentinel — now see how well it sticks with free SC-900 practice questions. Full explanations included, no account needed.
Done with this chapter?