# Basic KQL Queries in Sentinel

> Chapter 101 of the Courseiva SC-900 curriculum — https://courseiva.com/learn/sc-900/kql-basics-sc900

**Official objective:** 3.2 — Security Solutions

## Introduction

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.

## KQL as a Library Card Catalog

A vast library, shelves overflowing with millions of books — each one 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.

## Core explanation

### 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:

```kusto
TableName
| operator1 [parameters]
| operator2 [parameters]
...
```

For example:

```kusto
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| project Account, Computer, TimeGenerated
```

This 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:
```kusto
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:
```kusto
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:
```kusto
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:
```kusto
SigninLogs
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where FailedAttempts > 10
```
This 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:

```kusto
Table1 | join kind=inner (Table2) on KeyColumn
```

Example:
```kusto
SecurityEvent
| where EventID == 4688  // Process creation
| join kind=inner (
    SecurityEvent
    | where EventID == 4624  // Logon
) on $left.Account == $right.TargetUserName
```
This 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:
```kusto
let threshold = 10;
let suspiciousUsers = SigninLogs
    | where ResultType != "0"
    | summarize count() by UserPrincipalName
    | where count_ > threshold;
suspiciousUsers
| join kind=inner (SigninLogs) on UserPrincipalName
```

### Time 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:
```kusto
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:
```kusto
SigninLogs
| where TimeGenerated > ago(1d)
| order by TimeGenerated desc
| take 10
```
This 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.

## Real-world context

### 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:
```kusto
SigninLogs
| where TimeGenerated > ago(5m)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, IPAddress
| where FailedCount > 10
```
This 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:
```kusto
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:
```kusto
let privilegedUsers = Identity
    | where Group == "Domain Admins"
    | project UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName in (privilegedUsers)
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType
```
Performance consideration: The `in` operator with a large list can be slow; use `let` with a subquery or materialize for efficiency.

## Exam focus

### 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
1. **Confusing `project` with `extend`**: Many candidates think `project` adds columns. In reality, `project` selects existing columns and drops others; `extend` adds new columns.
2. **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.
3. **Misunderstanding `summarize`**: Some think `summarize` returns all rows with an additional column. Actually, it groups rows and returns one row per group.
4. **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(...)`.

## Step by step

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

## Comparisons

### project vs extend

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

## Common misconceptions

- **Misconception:** KQL is the same as SQL. **Reality:** 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.
- **Misconception:** The `project` operator adds new columns. **Reality:** `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`.
- **Misconception:** The `summarize` operator returns all original rows plus an aggregated column. **Reality:** `summarize` groups rows by the `by` columns and returns one row per group, with aggregated values. It does not return individual rows.
- **Misconception:** A time filter is optional and has no impact on performance. **Reality:** Time filters are critical. Without them, the query scans the entire table, potentially causing timeouts and high costs. Always use `where TimeGenerated > ago(...)`.
- **Misconception:** The `contains` operator is case-sensitive. **Reality:** `contains` is case-insensitive. For case-sensitive matching, use `contains_cs`.

## Key takeaways

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

## FAQ

**What is KQL and why is it used in Microsoft Sentinel?**

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.

**What does the `where` operator do in KQL?**

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.

**What is the difference between `project` and `extend`?**

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

**How do you count events by user in KQL?**

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.

**What does `ago(1d)` mean in KQL?**

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

**Can you use SQL to query Sentinel logs?**

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.

**What is the purpose of the `summarize` operator?**

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.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/sc-900/kql-basics-sc900