6.1 Construct KQL Statements for Microsoft Sentinel

Construct KQL Statements for Microsoft Sentinel

Introduction

This subsection teaches you the fundamental building blocks of KQL — the operators you will use in every query you write for the rest of this course and your career as a SOC analyst.

KQL (Kusto Query Language) was designed by Microsoft for querying large volumes of structured log data. It is the query language behind Microsoft Sentinel, Microsoft Defender XDR Advanced Hunting, Azure Monitor, and Azure Data Explorer. If you have used SQL before, KQL will feel partially familiar — both query tabular data — but KQL is read-only (you cannot insert, update, or delete data) and its syntax flows differently.

If you have never used a query language before, that is fine. This subsection assumes no prior query experience. You will write your first query within the next few minutes, and by the end of this subsection you will construct a complete investigation query that traces a brute-force attack from first failure to successful compromise.

Here is what you will learn in this subsection, in order:

• The pipe model — how KQL processes data step by step, and why understanding this model is the key to writing any query
• The where operator — filtering rows to find the data relevant to your investigation, with every comparison operator you need for security work
• The project operator — controlling which columns appear in your output, and why column order matters
• The extend operator — adding new calculated columns that transform raw log data into investigation-ready information
• The let statement — naming values and sub-queries for clarity and reuse, including a critical detection pattern you will use in Module 9
• The order by operator — sorting results for timeline reconstruction
• The search operator — finding data when you do not know which table contains it
• A complete investigation query — built step by step from a realistic alert scenario, combining every operator into a single query that tells the story of an attack

Each operator is introduced with its purpose, taught through a security investigation example, and practiced in your lab environment. You will run every query yourself, see the output, and understand what it means before moving to the next operator.

Open your Sentinel workspace now (portal.azure.com → Microsoft Sentinel → Logs) and keep it alongside this page. You will use it throughout.

The pipe model — how KQL thinks

Every KQL query follows the same pattern: start with a table, then transform the data step by step using the pipe character (|). Each step takes the output of the previous step and does something to it — filtering, shaping, computing, or sorting.

Think of it like a funnel:

1. Start wide — select a table with millions of rows
2. Filter down — keep only the rows that matter
3. Shape the output — choose which columns to display
4. Summarize or sort — organize the results for analysis

Here is the simplest possible KQL query:

1

SigninLogs

This returns every row in the SigninLogs table. In a production environment with 500 users, that could be hundreds of thousands of rows for a single day. It is technically valid KQL, but useless for investigation — you would drown in data.

Now add the pipe to start filtering:

1
2

SigninLogs
| where TimeGenerated > ago(1h)

This says: “Start with everything in SigninLogs. Then, keep only the rows where the timestamp is within the last hour.” The pipe (|) means “take the output from the line above and pass it to the next operation.”

Run this in your lab environment now. Paste the query into the Sentinel Logs editor and click Run.

The key mental model: every line in a KQL query transforms the data from the line above. The table name is your starting point. Every pipe adds a transformation. The final output is what appears in your results pane. This pipe-by-pipe flow is how you should read, write, and debug every KQL query.

The where operator — filtering to what matters

What it is

where evaluates a condition against every row and keeps only the rows where the condition is true. Everything else is discarded. It is the most frequently used operator in security KQL — nearly every query starts with one or more where clauses that narrow millions of rows down to the handful relevant to your investigation.

Why it matters

A production Sentinel workspace ingests tens of millions of events per day. Without where, every query returns all of them. With where, you ask precise questions: “Show me failed sign-ins from outside our network in the last hour.” Each where clause narrows the dataset further, like adding filters to a camera lens until only the subject is in focus.

How it works

Syntax: | where ColumnName Operator Value

Start with the query you already ran, and add a filter for failed sign-ins:

1
2
3

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"

Run this now. You are looking at failed sign-ins in the last 24 hours. ResultType != "0" means “not successful” — every non-zero ResultType code represents a specific failure reason.

You now have a filter chain: all sign-ins → last 24 hours → failures only. Each where reduces the dataset further.

The comparison operators for security work

These are the operators you will use inside where clauses. You do not need to memorize this table — you will internalize them through use. But reference it when you need an operator and cannot remember the syntax.

Combining conditions

Use and and or to build compound filters. You can chain where clauses on separate lines (implicit and) or combine conditions in a single line.

These two queries are identical:

1
2
3
4
5
6
7
8
9

// Method 1: separate where clauses (cleaner, easier to read)
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| where IPAddress !startswith "86.12"

// Method 2: combined with 'and' (same result, one line)
SigninLogs
| where TimeGenerated > ago(24h) and ResultType != "0" and not(IPAddress startswith "86.12")

Method 1 is preferred for readability — each line states one filter condition, making the query easier to understand and debug. During an active investigation, readable queries save time.

The third filter (IPAddress !startswith "86.12") excludes your office network (assuming 86.12.x.x is your known IP range). Now you are filtering out the noise — your own users who mistyped passwords — and focusing on external failed sign-in attempts.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| where UserPrincipalName has "morrison"
| where AppDisplayName !in ("Microsoft Office", "Azure Portal")

The project operator — controlling what you see

What it is

project selects which columns appear in your query output and discards the rest. It is the KQL equivalent of choosing which columns to display in a spreadsheet.

Why it matters

SigninLogs has over 50 columns. During an investigation, you need 5-8 of them. Without project, your results pane is overwhelmed with irrelevant data — authentication protocols, resource IDs, correlation IDs — none of which help you answer “who signed in from where and did it succeed?” project strips the noise and lets you focus on the columns that answer your investigation question.

How it works

Add project to the query you have been building:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType,
    ResultDescription, Location, AppDisplayName

Run this. Your results now show exactly the columns relevant to a failed sign-in investigation — when it happened, who was targeted, from what IP, why it failed, what country, and which application. The 40+ irrelevant columns are gone. The data is immediately readable.

The project variants

The extend operator — adding computed columns

What it is

extend adds a new column to every row based on a calculation or transformation. Unlike project, it does not remove any existing columns — it adds to them. The new column exists for all subsequent operations in the query.

Why it matters

Raw log data is factual but not always useful in its raw form. A timestamp of 2026-03-21T08:14:22Z is precise, but during an investigation you want to know “how long ago was this?” An IP address of 198.51.100.44 is specific, but you want to know “is this internal or external?” An email field of j.morrison@northgateeng.com contains useful structure, but you need just the domain. extend transforms raw data into investigation-ready context.

How it works

Add extend to compute the time since each failed sign-in:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| extend HoursAgo = round(datetime_diff('hour', now(), TimeGenerated), 1)
| project TimeGenerated, HoursAgo, UserPrincipalName, IPAddress, ResultDescription

extend patterns you will use in every investigation

These four patterns appear so frequently that they are worth learning now. You will use them throughout Modules 7-15.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

// 1. Extract the domain from an email address
| extend Domain = tostring(split(UserPrincipalName, "@")[1])

// 2. Classify an IP as internal or external
| extend NetworkZone = iff(IPAddress startswith "10." or IPAddress startswith "192.168.",
    "Internal", "External")

// 3. Extract location from nested JSON (common in SigninLogs)
| extend City = tostring(LocationDetails.city)
| extend Country = tostring(LocationDetails.countryOrRegion)

// 4. Classify time of day for anomaly detection
| extend Hour = hourofday(TimeGenerated)
| extend TimeCategory = iff(Hour >= 8 and Hour < 18, "Business Hours", "After Hours")

Each creates a new column without modifying existing data. Together, they transform raw IPAddress = 198.51.100.44 into NetworkZone = External, Country = US, TimeCategory = After Hours — which is immediately useful for investigation.

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(7d)
| extend City = tostring(LocationDetails.city)
| extend Hour = hourofday(TimeGenerated)
| extend TimeCategory = iff(Hour >= 8 and Hour < 18, "Business Hours", "After Hours")
| extend Outcome = iff(ResultType == "0", "Success", "Failed")
| project TimeGenerated, TimeCategory, City, AppDisplayName, Outcome
| order by TimeGenerated desc

The let statement — naming things for clarity and reuse

What it is

let assigns a name to a value, expression, or sub-query. It creates a reference you can use later in the query. let does not change the data — it makes the query more readable, more maintainable, and more powerful.

Why it matters

Without let, complex queries become unreadable walls of text. With let, you name your building blocks and the query reads like a set of instructions: “define the time window, define the target user, then search for their sign-ins.” During an active investigation where you run the same query pattern against 5 different users, let means changing one line instead of hunting through 10 lines of logic.

More importantly, let enables sub-queries — defining a dataset in one block and referencing it in the main query. This unlocks the most important investigation patterns in KQL.

How it works — simple let (naming a value)

1
2
3
4
5
6
7

let timeWindow = 24h;
let targetUser = "j.morrison@yourdomain.onmicrosoft.com";
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where UserPrincipalName == targetUser
| where ResultType != "0"
| project TimeGenerated, IPAddress, ResultType, ResultDescription

This does exactly the same thing as writing ago(24h) and the full UPN inline. But when you need to change the time window to 7 days or switch to investigating s.patel, you change one line at the top. During an investigation where you are running the same pattern for multiple users, this saves minutes per query — and during an active incident, minutes matter.

How it works — let with a sub-query (the powerful pattern)

This is where let transforms KQL from a query tool into an investigation engine.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

let suspiciousIPs =
    SigninLogs
    | where TimeGenerated > ago(7d)
    | where ResultType != "0"
    | summarize FailCount = count() by IPAddress
    | where FailCount > 10
    | project IPAddress;
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| where IPAddress in (suspiciousIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName

Read this carefully — it is one of the most important query patterns in this entire course.

The let block creates a list of IP addresses that had more than 10 failed sign-ins. It asks: “which IPs are brute-forcing accounts?” The main query then finds SUCCESSFUL sign-ins from those exact IPs. It asks: “did any of those brute-forcing IPs eventually get in?”

The combination answers the critical investigation question: “Did the brute-force attack succeed?”

The order by operator — sorting results for investigation

What it is

order by (alias: sort by) arranges rows by one or more columns. It does not filter or transform — it reorders.

Why it matters

In security investigation, sort order determines how you read the data. desc (newest first) answers “what just happened?” — the default for alert triage. asc (oldest first) answers “what happened in sequence?” — the default for timeline reconstruction. Choosing the right sort order is the difference between seeing the most recent event first (triage mode) and reading the attack story from beginning to end (investigation mode).

How it works

1
2
3
4
5
6
7
8

// Triage mode: newest first — what just happened?
| order by TimeGenerated desc

// Investigation mode: oldest first — what happened in sequence?
| order by TimeGenerated asc

// Grouped investigation: all events per user, newest first within each user
| order by UserPrincipalName asc, TimeGenerated desc

The search operator — when you don’t know where to look

What it is

search scans across all columns in one or more tables for a value. It is the broadest possible query — the KQL equivalent of Ctrl+F across your entire workspace.

Why it matters

During an investigation, you often start with a single IOC — an IP address, a domain name, a file hash — and you do not know which table contains data about it. Is this IP in SigninLogs? DeviceNetworkEvents? CommonSecurityLog? search finds it everywhere, then you switch to targeted where queries for the actual investigation.

How it works

1
2
3
4
5
6

// Search everywhere for an IP
search "198.51.100.44"

// Search specific tables (faster)
search in (SigninLogs, AADNonInteractiveUserSignInLogs, DeviceNetworkEvents)
    "198.51.100.44"

Putting it all together — your first complete investigation query

You have now learned every operator needed to build a real investigation query. This section combines them into a single query, built step by step from a realistic scenario.

Scenario: An alert fires: “Multiple failed sign-ins followed by a success from IP 198.51.100.44.” You need to determine who was targeted, whether the attack succeeded, and what the attacker did after gaining access.

Build this with your lab open. Run each step.

Step 1: Find all activity from the suspicious IP.

1
2
3

SigninLogs
| where TimeGenerated > ago(24h)
| where IPAddress == "198.51.100.44"

This gives you everything — failed and successful — from that IP. But it is too raw. Add context.

Step 2: Classify each sign-in and add time context.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(24h)
| where IPAddress == "198.51.100.44"
| extend Outcome = iff(ResultType == "0", "SUCCESS", "FAILED")
| extend HoursAgo = round(datetime_diff('hour', now(), TimeGenerated), 1)

Every row now has a clear SUCCESS/FAILED label and a recency value.

Step 3: Shape and sort for timeline reading.

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(24h)
| where IPAddress == "198.51.100.44"
| extend Outcome = iff(ResultType == "0", "SUCCESS", "FAILED")
| extend HoursAgo = round(datetime_diff('hour', now(), TimeGenerated), 1)
| project TimeGenerated, HoursAgo, Outcome, UserPrincipalName,
    AppDisplayName, ResultDescription
| order by TimeGenerated asc

Sorting asc (oldest first) gives you a timeline — read the attack story from top to bottom.

Capstone exercise

1
2
3
4
5
6
7
8
9

SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName has "morrison"
| extend City = tostring(LocationDetails.city)
| extend Hour = hourofday(TimeGenerated)
| extend TimeCategory = iff(Hour >= 8 and Hour < 18, "Business Hours", "After Hours")
| extend Outcome = iff(ResultType == "0", "Success", "Failed")
| project TimeGenerated, TimeCategory, City, AppDisplayName, Outcome
| order by TimeGenerated desc

Common mistakes and how to fix them