# KQL Queries for Security Operations

> Chapter 63 of the Courseiva AZ-500 curriculum — https://courseiva.com/learn/az-500/kql-security-500

**Official objective:** 4.2 — Security Operations

## Introduction

Kusto Query Language (KQL) queries for security operations in Azure form 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.

## KQL is a Detective's Investigation Notebook

How would a detective investigate a security incident in a large office building filled with thousands of employees? 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.

## Core explanation

### 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:
```kusto
SigninLogs
| take 10
```
This 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:
```kusto
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "0"
```
This returns successful sign-ins in the last hour. You can combine conditions with `and`/`or`:
```kusto
SecurityEvent
| where EventID == 4624 and AccountType == "User"
```
#### Projecting Columns with `project`
`project` selects a subset of columns. Use `project-away` to exclude columns. Example:
```kusto
SigninLogs
| project UserPrincipalName, IPAddress, TimeGenerated
```
#### Extending with `extend`
`extend` adds computed columns. Example:
```kusto
SigninLogs
| extend Hour = bin(TimeGenerated, 1h)
| project Hour, UserPrincipalName
```
#### Aggregating with `summarize`
`summarize` groups rows and computes aggregates. Example:
```kusto
SigninLogs
| summarize FailedCount = count() by UserPrincipalName, ResultType
| where ResultType != "0"
| order by FailedCount desc
```
This counts failed sign-ins per user.
#### Sorting and Limiting
`order by` sorts, `take` limits rows. Example:
```kusto
SecurityEvent
| where EventID == 4625
| order by TimeGenerated desc
| take 100
```
#### Joining Tables
`join` merges rows from two tables. Example:
```kusto
SigninLogs
| where TimeGenerated > ago(1d)
| join kind=inner (
    AzureActivity
    | where TimeGenerated > ago(1d)
) on $left.UserId == $right.Caller
```
This correlates sign-ins with Azure activity.
#### Using `let` Statements
`let` defines variables or functions. Example:
```kusto
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:
```kusto
SigninLogs
| where TimeGenerated > ago(7d)
| summarize Count = count() by bin(TimeGenerated, 1d)
| render timechart
```
#### Working with Strings
Use `contains`, `startswith`, `endswith`, `matches regex`. Example:
```kusto
SigninLogs
| where UserPrincipalName contains "admin"
```
#### Common Security Queries
- **Failed sign-ins by user**:
```kusto
SigninLogs
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName
| order by FailedCount desc
```
- **Brute force detection**:
```kusto
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "50053"  // account locked
| summarize Attempts = count() by IPAddress, UserPrincipalName
| where Attempts > 10
```
- **Cross-table correlation**:
```kusto
SecurityEvent
| where EventID == 4688  // process creation
| where CommandLine contains "powershell"
| join kind=inner (
    SigninLogs
    | where ResultType == "0"
) on $left.Account == $right.UserPrincipalName
```
- **Anomalous geographic access**:
```kusto
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).

## Real-world context

### 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:
```kusto
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "50053"  // account locked
| summarize Attempts = count() by IPAddress, UserPrincipalName
| where Attempts > 10
| order by Attempts desc
```
This 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:
```kusto
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) > 3
```
This 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:
```kusto
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, ResourceId
```
This 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.

## Exam focus

### 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
1. **Using `SigninLogs` for Windows security events**: Candidates confuse `SigninLogs` (Azure AD sign-ins) with `SecurityEvent` (Windows event logs). The exam tests table knowledge.
2. **Using `extend` instead of `summarize`**: `extend` adds columns but does not aggregate; candidates may try to count with `extend` and fail.
3. **Forgetting time filter**: Queries without `TimeGenerated` filter may return too much data or time out. The exam expects explicit time filtering.
4. **Wrong join type**: Using `inner` when `leftanti` is needed (e.g., find sign-ins without corresponding Azure activity).
5. **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.

## Step by step

1. **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`.
2. **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.
3. **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`.
4. **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.
5. **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.
6. **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.

## Comparisons

### KQL (Kusto Query Language) vs SQL (Structured Query Language)

**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

## Common misconceptions

- **Misconception:** KQL is the same as SQL. **Reality:** 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.
- **Misconception:** You can use `count` as a standalone function. **Reality:** `count` is an aggregation function and must be used inside `summarize`. For example, `summarize count()` is correct; `count` alone is invalid.
- **Misconception:** All tables have the same columns. **Reality:** 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.
- **Misconception:** String comparisons in KQL are case-sensitive by default. **Reality:** They are case-insensitive by default. Use `== cs` or `contains_cs` for case-sensitive matching. The exam may test this distinction.
- **Misconception:** You can modify data using KQL. **Reality:** 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.

## Key takeaways

- 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.

## FAQ

**What is the difference between `contains` and `has` in KQL?**

`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.

**How do I join two tables in KQL?**

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).

**What is the correct syntax to count failed sign-ins per user in the last 24 hours?**

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.

**How do I find the top 10 IPs with the most failed logon attempts?**

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.

**Can I use KQL to query Azure AD logs?**

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.

**What does the `bin` function do in KQL?**

`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.

**How do I handle null values in KQL?**

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.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/az-500/kql-security-500