This chapter covers Kusto Query Language (KQL) queries for security operations in Azure, a core skill tested in the AZ-500 exam's Security Operations domain (Objective 4.2). KQL is the primary language used to query Azure Sentinel (Microsoft Sentinel), Azure Log Analytics, and other Azure data sources for threat hunting, incident investigation, and monitoring. Approximately 15-20% of AZ-500 exam questions involve interpreting or writing KQL queries, making proficiency essential for success. This chapter will teach you the syntax, operators, and practical patterns used in security operations, with a focus on exam-relevant scenarios.
Jump to a section
Imagine you are a detective investigating a security incident in a large office building. The building has thousands of employees, each with a badge that logs every door they open, every time they swipe. The logs are stored in a massive filing cabinet, but the cabinet is not organized — it's just a pile of papers with timestamps, badge IDs, and door numbers. Your job is to find out who entered a specific room at 3 AM last Tuesday. You cannot read every paper manually. Instead, you use a notebook (KQL) where you write queries: 'Find all entries where the door is 'ServerRoom' and the time is between 2:00 and 4:00 AM on that date.' The notebook's language is strict: you must specify the exact table (log source), the time range, and the filters. You can also combine queries: 'Now, from those entries, show only badge IDs that appear more than once' to spot anomalies. KQL works the same way: you start with a table (like 'SigninLogs'), filter rows (| where TimeGenerated between ...), project columns (| project UserId, IPAddress), and aggregate (| summarize count() by UserId). Every pipe (|) is like a step in your investigation — passing a refined set of data to the next operation. If you make a syntax error, the notebook returns nothing, just as a detective who writes a bad query gets no leads. The power of KQL is its ability to join multiple sources (like badge logs and visitor logs) to connect the dots, exactly as you would cross-reference evidence in a case.
What is KQL and Why It Exists
Kusto Query Language (KQL) is a read-only query language designed for high-performance analytics on large datasets in Azure Data Explorer, Azure Monitor Logs, and Microsoft Sentinel. It is optimized for time-series data, log analysis, and security telemetry. Unlike SQL, KQL is pipeline-oriented: data flows from one operator to the next using the pipe (|) character. This makes it intuitive for sequential filtering, aggregation, and transformation.
How KQL Works Internally
KQL queries are executed against tables stored in Azure Data Explorer clusters. Each table has a schema with columns of specific data types (string, int, datetime, etc.). The query engine parses the query into a tree of operators, then executes them in order, leveraging distributed processing across nodes. The pipeline model means each operator consumes the entire dataset from the previous step, filters or transforms it, and passes the result downstream. Key operators include:
- filter: | where – keeps rows matching a condition.
- project: | project – selects specific columns.
- extend: | extend – adds computed columns.
- summarize: | summarize – groups rows and computes aggregates (count, sum, dcount, etc.).
- join: | join – merges rows from two tables based on a key.
- sort: | order by – sorts rows.
- take: | take – returns a specified number of rows.
Key Components, Values, Defaults, and Timers
Tables: Common security tables in Azure Sentinel include SigninLogs, AuditLogs, SecurityEvent, CommonSecurityLog, VMConnection, AzureActivity. Each table has a predefined schema.
Time range: Queries often use TimeGenerated column. Default time range in Azure Sentinel is last 24 hours, but you can override with | where TimeGenerated between (datetime(2023-01-01) .. datetime(2023-01-02)).
String operators: == (equals), != (not equals), contains (substring), startswith, endswith, matches regex. String comparisons are case-insensitive by default; use == with cs prefix for case-sensitive (e.g., == cs).
Numeric operators: ==, !=, <, >, <=, >=, between.
Aggregation functions: count(), sum(), avg(), min(), max(), dcount() (distinct count), make_set() (list of distinct values), make_list() (list of all values).
Time functions: ago(1d) (1 day ago), now(), datetime(2023-01-01), bin(TimeGenerated, 1h) (rounds to hour).
Join types: inner, leftouter, rightouter, fullouter, leftanti, rightanti, leftsemi, rightsemi. For security, leftanti is common to find records in left table not in right.
Configuration and Verification Commands
To test queries in Azure Sentinel, use the Logs blade. You can also use the Kusto Explorer desktop tool or the Azure Data Explorer web UI. There is no 'configuration' per se — KQL is ad-hoc. However, you can save queries as functions or workbooks for reuse. Common verification: run a simple query to check table existence:
SigninLogs
| take 10This returns 10 rows. If the table doesn't exist, you get an error.
How KQL Interacts with Related Technologies
KQL is the query language for Azure Monitor Logs, which feeds into Azure Sentinel. Sentinel uses KQL for detection rules (analytics), hunting queries, and workbooks. Azure Policy can also be used to audit log collection, but KQL is the analysis layer. KQL can also query data from Azure Storage (via Azure Data Lake) and Azure Event Hubs (streaming). In security operations, KQL queries often combine multiple tables — e.g., joining SigninLogs with AzureActivity to correlate sign-ins with resource changes.
KQL Syntax and Operators in Depth
#### Filtering with where
The where operator filters rows based on a boolean expression. Example:
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "0"This returns successful sign-ins in the last hour. You can combine conditions with and/or:
SecurityEvent
| where EventID == 4624 and AccountType == "User"#### Projecting Columns with project
project selects a subset of columns. Use project-away to exclude columns. Example:
SigninLogs
| project UserPrincipalName, IPAddress, TimeGenerated#### Extending with extend
extend adds computed columns. Example:
SigninLogs
| extend Hour = bin(TimeGenerated, 1h)
| project Hour, UserPrincipalName#### Aggregating with summarize
summarize groups rows and computes aggregates. Example:
SigninLogs
| summarize FailedCount = count() by UserPrincipalName, ResultType
| where ResultType != "0"
| order by FailedCount descThis counts failed sign-ins per user.
#### Sorting and Limiting
order by sorts, take limits rows. Example:
SecurityEvent
| where EventID == 4625
| order by TimeGenerated desc
| take 100#### Joining Tables
join merges rows from two tables. Example:
SigninLogs
| where TimeGenerated > ago(1d)
| join kind=inner (
AzureActivity
| where TimeGenerated > ago(1d)
) on $left.UserId == $right.CallerThis correlates sign-ins with Azure activity.
#### Using let Statements
let defines variables or functions. Example:
let threshold = 10;
SigninLogs
| where ResultType != "0"
| summarize Count = count() by IPAddress
| where Count > threshold#### Time Series and Binning
bin rounds timestamps to a given interval for time-series aggregation. Example:
SigninLogs
| where TimeGenerated > ago(7d)
| summarize Count = count() by bin(TimeGenerated, 1d)
| render timechart#### Working with Strings
Use contains, startswith, endswith, matches regex. Example:
SigninLogs
| where UserPrincipalName contains "admin"#### Common Security Queries - Failed sign-ins by user:
SigninLogs
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName
| order by FailedCount descBrute force detection:
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "50053" // account locked
| summarize Attempts = count() by IPAddress, UserPrincipalName
| where Attempts > 10Cross-table correlation:
SecurityEvent
| where EventID == 4688 // process creation
| where CommandLine contains "powershell"
| join kind=inner (
SigninLogs
| where ResultType == "0"
) on $left.Account == $right.UserPrincipalNameAnomalous geographic access:
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project UserPrincipalName, IPAddress, Location = tostring(parse_json(LocationDetails).countryOrRegion)
| where Location != "US"Performance Considerations
Always filter by time (TimeGenerated > ago(1d)) to reduce data scanned.
Use project early to drop unnecessary columns.
Prefer summarize over distinct for large datasets.
Avoid extend with expensive operations (e.g., parse_json) on huge datasets.
Use materialize() for subqueries used multiple times.
Exam Tips
Know common table names and their columns (SigninLogs, SecurityEvent, AzureActivity).
Understand summarize vs extend: summarize reduces rows, extend adds columns.
Be able to identify syntax errors: missing pipe, wrong case, invalid column.
Recognize patterns for brute force, lateral movement, and data exfiltration.
Know the difference between contains and has: has is faster (whole token match).
Identify the Data Source
Determine which Azure table contains the relevant security data. For sign-in failures, use `SigninLogs`. For Windows security events, use `SecurityEvent`. For Azure resource activity, use `AzureActivity`. For network flows, use `VMConnection`. Each table has a specific schema. Knowing the right table is the first step to writing an efficient query. The exam often tests whether you can choose the correct table for a given scenario. For example, to investigate a suspicious sign-in, you must use `SigninLogs`, not `AuditLogs`.
Filter by Time Range
Always start with a time filter to limit data volume. Use `TimeGenerated > ago(1d)` for the last day, or `TimeGenerated between (datetime(2023-01-01) .. datetime(2023-01-02))` for a specific range. The default time range in Azure Sentinel is 24 hours, but explicit filtering improves performance and accuracy. Exam questions often include a time filter as part of the query; missing it can cause incorrect results or timeouts.
Apply Initial Filters
Filter rows using `where` to isolate relevant events. For example, to find failed sign-ins: `| where ResultType != "0"`. For process creation events: `| where EventID == 4688`. Use multiple `where` clauses or combine with `and`/`or`. Be careful with string comparisons: use `==` for exact match, `contains` for substring. The exam tests your ability to choose the correct operator. For instance, `contains` is case-insensitive; if case matters, use `contains_cs`.
Project Relevant Columns
Use `project` to select only the columns you need. This reduces data size and improves readability. For example: `| project TimeGenerated, UserPrincipalName, IPAddress, ResultType`. You can also rename columns with `project-rename`. Avoid projecting unnecessary columns like raw JSON blobs. The exam may ask you to identify which columns are available in a table; knowing common columns is key.
Aggregate and Summarize
Use `summarize` to group data and compute statistics. For example, count failed sign-ins per user: `| summarize FailedCount = count() by UserPrincipalName`. Use `dcount()` for distinct counts, `make_set()` for lists of values. Aggregation is essential for threat hunting (e.g., finding IPs with many failed attempts). The exam tests your understanding of aggregation functions and the `by` clause. Remember that `summarize` reduces rows; after it, you cannot access individual event details.
Sort and Limit Results
Use `order by` to sort results (e.g., by count descending) and `take` to limit output. Example: `| order by FailedCount desc | take 10`. This helps identify top offenders. The exam may ask you to find the top N users or IPs. Sorting is often combined with aggregation.
Enterprise Scenario 1: Investigating a Brute Force Attack
A large enterprise uses Azure Sentinel to monitor sign-in logs. The SOC receives an alert for multiple failed sign-ins. The analyst writes a KQL query to identify the source IPs and targeted accounts:
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "50053" // account locked
| summarize Attempts = count() by IPAddress, UserPrincipalName
| where Attempts > 10
| order by Attempts descThis query filters for account lockout events in the last hour, groups by IP and user, and returns only those with more than 10 attempts. In production, this query runs on thousands of sign-ins per second. Performance is critical: the time filter reduces data to a few million rows, and aggregation is efficient. Misconfiguration: if the analyst uses contains instead of == for ResultType, they might include unrelated events, skewing results.
Enterprise Scenario 2: Detecting Lateral Movement
An organization monitors Windows security events. The SOC wants to detect when a user logs into multiple machines in a short time, a sign of lateral movement. The query:
SecurityEvent
| where EventID == 4624 // logon
| where TimeGenerated > ago(1d)
| project TimeGenerated, Account, Computer
| summarize Machines = make_set(Computer), LogonCount = count() by Account, bin(TimeGenerated, 1h)
| where array_length(Machines) > 3This groups logons by account and hour, creates a set of machines, and filters accounts that logged into more than 3 unique machines in an hour. In production, this query runs on millions of events. Common pitfall: not binning time properly can miss patterns or create false positives. Scale consideration: the make_set operator can be memory-intensive for accounts with many logons; limiting by time helps.
Enterprise Scenario 3: Correlating Azure Activity with Sign-Ins
A cloud security team wants to investigate a suspicious resource deletion. They join AzureActivity with SigninLogs to find which user performed the deletion and their sign-in details:
AzureActivity
| where OperationName == "Delete Virtual Machine"
| where TimeGenerated > ago(1d)
| join kind=inner (
SigninLogs
| where TimeGenerated > ago(1d)
) on $left.Caller == $right.UserPrincipalName
| project TimeGenerated, Caller, IPAddress, OperationName, ResourceIdThis correlates the user who triggered the Azure activity with their sign-in IP. In production, the join can be expensive if not filtered by time. Misconfiguration: using a wrong join type (e.g., leftouter instead of inner) may include unmatched rows, causing confusion. Performance tip: ensure both sides have time filters and that the join key is indexed.
What AZ-500 Tests on KQL (Objective 4.2)
The exam focuses on the ability to read, interpret, and write KQL queries for security operations. Specific areas:
- Log source identification: Choose the correct table (SigninLogs, SecurityEvent, AzureActivity, etc.) for a given scenario.
- Filtering and aggregation: Write queries that filter by time, event type, and user, then aggregate counts.
- Join operations: Understand when to use inner, leftouter, leftanti to correlate data.
- Time functions: Use ago(), bin(), between() correctly.
- String operators: Distinguish ==, contains, has, matches regex.
- Common security patterns: Brute force detection, failed logins, anomalous access.
Common Wrong Answers and Why Candidates Choose Them
Using `SigninLogs` for Windows security events: Candidates confuse SigninLogs (Azure AD sign-ins) with SecurityEvent (Windows event logs). The exam tests table knowledge.
Using `extend` instead of `summarize`: extend adds columns but does not aggregate; candidates may try to count with extend and fail.
Forgetting time filter: Queries without TimeGenerated filter may return too much data or time out. The exam expects explicit time filtering.
Wrong join type: Using inner when leftanti is needed (e.g., find sign-ins without corresponding Azure activity).
Case sensitivity: Using == when case-insensitive match is needed, or vice versa.
Specific Numbers, Values, and Terms
Event IDs: 4624 (successful logon), 4625 (failed logon), 4688 (process creation), 50053 (account locked).
ResultType values: "0" (success), "50053" (lockout), "50126" (invalid password).
Time functions: ago(1d), ago(7d), now(), datetime().
Aggregation functions: count(), dcount(), make_set(), make_list().
String operators: contains, has, startswith, endswith, matches regex.
Edge Cases and Exceptions
Null values: where does not filter out nulls; use isnotempty().
Case sensitivity: By default, string comparisons are case-insensitive. Use cs suffix for case-sensitive (e.g., == cs).
Escaping: Special characters in strings may need escaping (use \).
Join performance: Joins on non-indexed columns are slow; always filter before join.
Time zone: All timestamps are in UTC; be aware when converting.
How to Eliminate Wrong Answers
Identify the data source first: if the scenario is about Azure AD sign-ins, the table is SigninLogs; if Windows events, SecurityEvent.
Check the operator: if the query needs to count events per user, look for summarize count() by User.
Verify time range: a correct query will almost always have a time filter.
Look for join type: if the question asks for 'users who did not perform action', use leftanti or rightanti.
Test with small data: mentally run the query on a few rows to see if the logic holds.
KQL uses pipe (|) to pass data between operators; order matters.
Always filter by TimeGenerated to improve performance and accuracy.
Use `summarize` for aggregation; `extend` adds columns without reducing rows.
Common security tables: SigninLogs, SecurityEvent, AzureActivity, VMConnection.
String comparisons are case-insensitive by default; use `cs` suffix for case-sensitive.
Join types: `inner` (matching rows only), `leftouter` (all left rows), `leftanti` (left rows not in right).
Event IDs: 4624 (success logon), 4625 (failed logon), 4688 (process creation), 50053 (account locked).
Use `let` to define variables and functions for reusable queries.
`bin()` rounds timestamps for time-series aggregation.
KQL is read-only; cannot modify data.
These come up on the exam all the time. Here's how to tell them apart.
KQL (Kusto Query Language)
Pipeline-oriented: operators separated by pipe (|)
Designed for time-series and log analytics
No JOIN keyword; uses `join` operator with kind
Case-insensitive strings by default
Schema-on-read: tables have fixed schemas but can be extended
SQL (Structured Query Language)
Set-oriented: SELECT-FROM-WHERE-GROUP BY
Designed for transactional and relational data
JOIN keyword with INNER, LEFT, etc.
Case-sensitive strings depend on collation
Schema-on-write: strict schema defined at creation
Mistake
KQL is the same as SQL.
Correct
KQL is pipeline-oriented, not set-based like SQL. Operators are applied sequentially with pipes, and the order matters. SQL uses SELECT-FROM-WHERE-GROUP BY. KQL uses table | filter | project | summarize. The exam tests KQL-specific syntax.
Mistake
You can use `count` as a standalone function.
Correct
`count` is an aggregation function and must be used inside `summarize`. For example, `summarize count()` is correct; `count` alone is invalid.
Mistake
All tables have the same columns.
Correct
Each table has a unique schema. `SigninLogs` has `UserPrincipalName`, `IPAddress`, `ResultType`. `SecurityEvent` has `EventID`, `Account`, `Computer`. Using a column from the wrong table causes an error.
Mistake
String comparisons in KQL are case-sensitive by default.
Correct
They are case-insensitive by default. Use `== cs` or `contains_cs` for case-sensitive matching. The exam may test this distinction.
Mistake
You can modify data using KQL.
Correct
KQL is read-only. You cannot insert, update, or delete data. All queries are analytical. The exam emphasizes this: KQL is for querying, not modifying.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
`contains` performs a substring match (case-insensitive), while `has` matches a whole token (word boundary). `has` is faster because it uses an index. For example, `"hello world" contains "ello"` is true, but `"hello world" has "ello"` is false because 'ello' is not a whole token. Use `has` for exact word matching and `contains` for partial matches. In security queries, `has` is preferred for IP addresses or hostnames to avoid false positives.
Use the `join` operator with a kind (inner, leftouter, etc.) and an on clause. Example: `Table1 | join kind=inner (Table2) on $left.Key == $right.Key`. The left and right sides are the tables before and after the join. Always filter both tables by time before joining to improve performance. Common join kinds: `inner` (only matching rows), `leftouter` (all left rows, nulls for unmatched), `leftanti` (left rows not in right).
Use `SigninLogs | where TimeGenerated > ago(1d) | where ResultType != "0" | summarize FailedCount = count() by UserPrincipalName | order by FailedCount desc`. This filters for the last day, excludes successful sign-ins (ResultType == "0"), groups by user, and sorts by count descending. The exam expects you to know that ResultType "0" means success.
Use `SigninLogs | where TimeGenerated > ago(1d) | where ResultType != "0" | summarize Attempts = count() by IPAddress | top 10 by Attempts desc`. The `top` operator is a shorthand for `order by ... take`. Alternatively, use `order by Attempts desc | take 10`. This query is a common exam pattern.
Yes, Azure AD logs are ingested into Log Analytics and can be queried via tables like `SigninLogs` (Azure AD sign-ins) and `AuditLogs` (directory changes). You must have Azure AD Premium P1/P2 licenses to send logs to Log Analytics. The exam assumes these logs are available.
`bin` rounds a datetime value down to the nearest multiple of a given interval. Example: `bin(TimeGenerated, 1h)` rounds to the start of the hour. It is used in `summarize` to create time buckets. For example, `summarize count() by bin(TimeGenerated, 1h)` gives counts per hour. This is essential for time-series analysis.
Use `isnull()` or `isnotnull()` in `where` clauses. Example: `| where isnotnull(IPAddress)`. You can also use `coalesce()` to replace nulls with a default value: `| extend SafeIP = coalesce(IPAddress, "Unknown")`. The exam may test handling of missing data.
You've just covered KQL Queries for Security Operations — now see how well it sticks with free AZ-500 practice questions. Full explanations included, no account needed.
Done with this chapter?