In this section

KQL for Incident Responders: Filter, Summarize, Join and Read Results Honestly

Module 0

Introduction

Kusto Query Language, KQL, is how an investigator reads a Sentinel workspace and Defender XDR's advanced hunting tables. Every lesson in this module has used it, and every module after this one depends on it. An investigator who can write a correct query in a few minutes can answer most incident questions in an afternoon; one who cannot is limited to whatever the portal pages happen to show.

The good news is that investigations need a small part of the language. Five operations carry almost all of the work: filtering rows, choosing and shaping columns, summarizing, joining one table to another, and combining several tables into one result. The rest is detail that can be looked up.

This lesson teaches those five as patterns, explains the logic of each line before running it, and runs every query on the course dataset. It ends with the part most courses skip: reading a result honestly, including how to tell an empty result that means "nothing happened" from one that means "the query was wrong".

01

The Shape of a Query

A table, then a pipeline

A KQL query starts with a table name and passes its rows through a series of operators, each separated by a pipe character, |. Each operator takes the rows that come out of the previous one and produces new rows. Reading a query means reading it top to bottom, asking at each line what the rows look like now.

SigninLogs                                   // start from one table
| where TimeGenerated > ago(30d)             // keep only the window that matters
| where ResultType == "50126"                // keep the rows that answer it
| summarize Failures = count()               // reduce the rows to an answer
    by bin(TimeGenerated, 1d)                //   one row per day
| order by TimeGenerated asc                 // put the answer in a readable order

The result is one row per day of failed password attempts across the tenant over the last month, starting with five on 13 February. Each line has one job, and the order is deliberate: the time filter comes first, the specific filter second, and the reduction last. Reading any unfamiliar query in the same way, one line at a time, is the quickest way to find where it goes wrong.

Measured on the course dataset, the pipeline narrows like this:

SigninLogsthe table8,062 rows in 30 days|wherecode 50126 only172 rows|summarizeone row per day24 rows|order byoldest firstthe answer

A query is a pipeline that narrows. Each operator hands fewer, more meaningful rows to the next.

The time filter first is not only good style. Workspaces store data partitioned by time, and a query that restricts TimeGenerated early lets the service skip most of the data without reading it. On a large production workspace, the same query with the time filter at the end can run hundreds of times slower or hit a resource limit and fail.

Comments start with // and run to the end of the line. Investigation queries that will be kept as evidence, as lesson 0.4 required, should carry a short comment saying what question they answer, because the query text will be read by somebody who was not there when it was written.

Knowing which table to start from is half of writing the query. The consoles list every table in the workspace with its columns, and the schema pane is worth opening before writing anything against an unfamiliar table, because column names cannot be guessed reliably. Running TableName | take 10 and reading the rows is the quickest way to learn what a table actually holds, including which columns are empty in this tenant and which hold nested values that need unpacking.

Microsoft Defender

Run a query in advanced hunting:
Microsoft Defender › Investigation & response › Hunting › Advanced hunting
Pick the time range in the toolbar or in the query, write the query in the editor and run it. The schema pane lists every table and column; Sentinel tables appear there too when a workspace is connected.

Every query in this lesson runs unchanged there, against the course dataset's tables.

02

Filtering

Time, equality, case and strings

where keeps the rows for which a condition is true. The conditions an investigator writes most often are about time, about exact values and about text.

For time, ago() gives a moment relative to now, such as ago(7d) or ago(12h), and between gives a fixed window, such as TimeGenerated between (datetime(2026-02-27) .. datetime(2026-03-05)). Fixed windows are better for evidence, because a query using ago() returns a different result tomorrow.

For exact values, == is equality and it is case-sensitive. That matters more than it looks, because the same user principal name can be recorded in different cases in different tables. The difference is easy to demonstrate:

SigninLogs
| where UserPrincipalName =~ "C.Richardson@NE.com"
| count

With =~ the query counts 169 sign-ins. The same query with == and the name typed in that case counts zero, and nothing in the output says why. For user names, addresses, domains and anything a person typed, =~ is the safer default, and in~ is its counterpart for a list of values.

Lists are common in investigations, because the attacker's infrastructure is usually several addresses rather than one. IPAddress in ("185.234.72.18", "185.234.72.41") matches either, and a list kept in a let statement at the top of the query can be reused across several tables. Negation needs care: !=, !has and !in exclude rows, and a row whose column is empty is excluded by some of these and kept by others. When excluding known-good values, check the count before and after to make sure the exclusion removed what was intended and nothing else.

For text, three operators do most of the work. has matches a whole term anywhere in a string and is fast, because it uses the index. contains matches any substring, including part of a word, and is slower. startswith matches the beginning. Searching the Parameters column for an outside mail provider shows has at work:

OfficeActivity
| where Parameters has "protonmail"
| summarize Changes = count() by Operation, UserId

One row: a Set-Mailbox change on c.richardson's mailbox, which lesson 0.6 identified as forwarding to an outside address. The search did not need to know which parameter held the address, only that the term appeared somewhere in the record.

03

Shaping Columns

project, extend and nested fields

project chooses which columns to keep and in what order, and can rename them. extend adds a new column calculated from existing ones and keeps everything else. Together they turn a wide record into the handful of columns a question needs.

Many important fields are not simple columns but dynamic values: JSON objects or arrays stored in one column. LocationDetails in the sign-in log is an object holding city, state and country. AuthenticationDetails is an array of the authentication steps. TargetResources in the audit log is an array of the objects changed. Reaching inside them uses dot notation for objects and square brackets for array positions, wrapped in tostring() so the result can be compared and grouped:

SigninLogs
| where UserPrincipalName == "c.richardson@ne.com"
| extend City = tostring(LocationDetails.city),
         Method = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)
| project TimeGenerated, IPAddress, City, Method, ResultType
| order by TimeGenerated desc
| take 5

The five most recent sign-ins come back with the city and the first authentication method as plain columns, ready to read or group. parse_json() is needed here because AuthenticationDetails arrives as a string containing JSON, while LocationDetails is already dynamic. When a nested field returns empty on every row, the first suspect is a column that needs parsing, and the second is a misspelled field name, since KQL returns empty rather than an error for a field that does not exist inside a dynamic value.

Arrays need one more operator. The query above reads only the first authentication step, [0], which is enough when one step tells the story. A sign-in can record several steps, a password followed by a push approval, and mv-expand turns each element of an array into its own row so every step can be filtered and counted. Module 3 uses it on authentication details and on the target lists in audit records, which often name more than one object.

take returns some rows without any promise about which. It is useful for looking at a table's shape, and wrong for any question about the most recent or the largest: use top 5 by TimeGenerated desc, or order by followed by take as above, which sorts first.

04

Summarizing

From rows to an answer

summarize collapses many rows into fewer, grouping by the columns named after by and calculating aggregate values for each group. Most incident questions are answered by a summarize: how many, how many distinct, when first, when last, which ones.

The aggregations an investigation uses

count()

rows in the group

countif(cond)

rows where the condition holds

dcount(col)

distinct values; approximate at scale

min(), max()

first and last seen, on TimeGenerated

make_set(col)

the distinct values, as a list

bin(time, 1h)

after by: time in buckets

The query that found the password spray in lesson 0.3 is a summarize by address: attempts, distinct users and successes per source. The same shape, grouped by user instead, finds push bombing; grouped by application, it finds a consent being used. A large share of hunting is choosing the right column to group by.

The choice of by columns decides what the result means. Grouping by user and address answers "who signed in from where". Grouping by address alone answers "what did each address do across all users", which is the question that exposes a spray. The same data can hide or reveal a pattern depending on that one choice.

Two refinements come up often. arg_max(TimeGenerated, *) returns the latest whole row for each group rather than only the latest time, which answers questions such as "what was each user's most recent sign-in, and from where". And a summarize can be followed by a where on its own results, such as keeping only addresses that tried more than ten distinct users, which turns a description of the data into a short list worth reading.

05

Joins and Baselines

What is new compared with what was normal

join combines two tables on matching column values. Lesson 0.3 showed the most important rule, which is that the columns must actually hold the same kind of value, and lesson 0.1 showed the second, which is that duplicates on either side multiply rows. Two join kinds matter most to investigators. inner keeps only rows that match on both sides. leftanti keeps rows from the left side that have no match on the right, and it is the tool for finding what is new.

A third kind is the one a join gets when no kind is named, and it surprises almost everyone the first time. KQL's default join is innerunique, which removes duplicate keys from the left side before matching. A query written without kind= can therefore return fewer rows than an investigator expects, with no warning, when the left side has several rows for the same key. Naming the kind every time, kind=inner or kind=leftanti, makes the query mean what it says.

That makes it the basis of a baseline: define what was normal in one period, then find what appears in another period that was not. let names an intermediate result so the query reads in steps:

BaselinePeriodbefore 27 FebruaryRowssuccessful sign-insKeptuser and country pairsRecentPeriodfrom 27 FebruaryRowsall sign-insShaped touser and countryleftantiKeepsrecent rows with no matchMeaninga place new for that userReadeach row by hand

A baseline as three steps. The anti-join keeps only what the earlier period never showed.

In KQL the three steps are a let for the baseline and one join:

let usual = SigninLogs
    | where TimeGenerated < datetime(2026-02-27) and ResultType == "0"
    | extend Country = tostring(LocationDetails.countryOrRegion)
    | distinct UserPrincipalName, Country;
SigninLogs
| where TimeGenerated >= datetime(2026-02-27) and ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=leftanti (usual) on UserPrincipalName, Country
| summarize SignIns = count(), First = min(TimeGenerated)
    by UserPrincipalName, Country

Eight rows come back, and they read like a summary of the course dataset's incidents. c.richardson from Singapore on 27 February. m.patel from Germany on 5 March, the spray. The administrator account a-pgreaves from Singapore on 15 March. A directory synchronization account from Germany on 7 March, and a network service account from the Netherlands on 12 March, both of which deserve a close look because service accounts should not travel.

The result also shows a baseline's weakness. d.foster appears with a sign-in from the United Kingdom, the user's own country, because that account has no sign-ins before 27 February at all, so nothing counts as normal for it. A new user, a returning user and an account that rarely signs in will always appear in a first-seen query. That is not a reason to discard the technique, but it is a reason to read each row rather than treat the result as a list of compromises.

06

Unions Across Sources

One timeline from several tables

union stacks the rows of several tables into one result, which is how a timeline across directory, mailbox and sign-in records is built. Lesson 0.7's AiTM timeline was a union. withsource adds a column naming the table each row came from, which keeps the result readable.

A union is also the honest answer to a common request: "show me everything this user did". No single table holds everything, and a union across sign-ins, directory changes, mailbox activity and file activity, each shaped to the same columns and limited to the same window, is the closest a tenant gets to a complete activity record for one person.

The difficulty with unions is that tables name the same idea differently. The user is UserPrincipalName in the sign-in logs, UserId in OfficeActivity and something inside InitiatedBy in the audit log. The time column is TimeGenerated in Sentinel tables and Timestamp in Defender XDR's own tables. A union of tables with different column names produces a result where each column is empty for the rows that came from other tables, which is easy to misread as missing data.

The answer is to shape each table to the same columns before the union:

let user = "c.richardson@ne.com";
union
    (SigninLogs
     | where UserPrincipalName == user and IPAddress startswith "185.234.72."
     | project TimeGenerated, Source = "Sign-in", Detail = AppDisplayName),
    (AuditLogs | where tostring(InitiatedBy.user.userPrincipalName) == user
     | project TimeGenerated, Source = "Directory", Detail = OperationName),
    (OfficeActivity | where UserId == user and ClientIP startswith "185.234.72."
     | where Operation != "MailItemsAccessed"
     | project TimeGenerated, Source = "Mailbox", Detail = Operation)
| where TimeGenerated between (datetime(2026-02-27T18:00:00Z)
                            .. datetime(2026-03-04T00:00:00Z))
| order by TimeGenerated asc

Every row now has the same three columns, whichever table it came from, and the result reads as a single timeline: the Singapore sign-in at 18:47, the inbox rule at 18:49, the directory changes from 18:55 to 19:03, then the later sign-ins and the forwarding change. That is the shape Module 3 uses for every investigation timeline.

Unions are also where queries slow down, because each branch reads its own table. Filtering inside each branch, as above, rather than once after the union, lets each table skip what it does not need before the rows are stacked. On a small dataset the difference is invisible; on a production workspace with months of mailbox records it is the difference between a timeline in seconds and a query that times out.

07

Reading a Result Honestly

Empty is not the same as nothing

A query result is a claim, and like any claim it can be wrong in ways that do not announce themselves. An investigator's most dangerous result is the empty one, because "no rows" looks exactly like "it did not happen", and the course has already shown several ways an empty result can be false.

1. Do the join columns hold the same kind of value?an object ID never matches a user principal name2. Is any == comparing typed text?use =~ for anything copied from an email or a ticket3. Is the time column right for this table?Defender tables use Timestamp, not TimeGenerated4. Does every nested path exist as written?a misspelled field returns empty, not an error5. Is the data there at all?routed, and inside retention for the whole window6. Run it against a known positiveonly a query that finds something can be trusted to find nothing

Six checks before an empty result goes into a finding. The first five are the common causes of a false empty; the last proves the query works.

The discipline that protects against all of these is to prove the query can find something before trusting its silence. Run it against a case where the answer is known to be yes: a time when the activity certainly happened, a user who certainly did it, or a value you plant. If it finds that, its empty result elsewhere means something. If it does not, the query is broken.

For forwarding, the known positive is the incident already confirmed. Before reporting that no other mailbox was set to forward, run the search across the tenant and check it returns the one case already known:

OfficeActivity
| where Operation == "Set-Mailbox"
| where Parameters has "ForwardingSmtpAddress"
| summarize Changes = count(), Mailboxes = dcount(UserId),
            Addresses = make_set(ClientIP)

One forwarding change, on one mailbox, from the attacker's address. The search finds the case already known, so its silence about every other mailbox can be trusted, for this cmdlet and this window.

Large results have their own traps. Consoles and APIs limit how many rows they return, and a result that stops at a round number, such as exactly 10,000 or exactly 100, is very likely truncated rather than complete. When a count matters, ask for the count with summarize or count rather than counting the rows a console happens to show.

Finally, a result describes the data, not the world. A query showing no forwarding rules created from an attacker's address shows that the records hold none, which is a statement about what was recorded and retained. Lesson 0.9 turns that distinction into the wording of findings.

A good investigation query therefore travels with three things: the question it answers, the known positive it was tested against, and the coverage it relied on, meaning the tables, the window and anything known to be missing from them. Written down beside the query, those three turn a result into something another investigator can trust, repeat and build on. They also make the next incident faster, because a tested query with its notes can be reused rather than rewritten under pressure.

08

What Module 8 Builds On It

From queries to hunts

Module 8, Threat Hunting, uses these five operations to look for compromise nobody has alerted on. It covers hunting without an alert, writing a hypothesis that can be wrong, how far back a hunt can really look, the identity tables, hunting what a response left behind, attacks that add nothing new, the difference between a result and a finding, turning a hunt into a detection rule, tuning and retiring rules, and what to do when a hunt finds nothing.

The baseline in section five is the seed of most of it. A hunt is usually a baseline question asked deliberately: what is new compared with what was normal, for an attribute where newness is suspicious. A daily count of password failures, read against its own history, is the simplest example:

SigninLogs
| where TimeGenerated > ago(30d) and ResultType == "50126"
| summarize Failures = count(), Users = dcount(UserPrincipalName)
    by Day = bin(TimeGenerated, 1d)
| order by Day asc

Most days in the course dataset show seven or fewer failures across seven or fewer users, which is the normal background of mistyped passwords. 5 March stands out with 23 failures across 22 users. It stands out because the count of distinct users jumps alongside the failures: a single user locked out of their own account can produce a run of failures, but only a spray produces failures across twenty accounts in one afternoon. Choosing what to measure is most of the craft, and Module 8 builds it carefully.

Do

Practice

Is this query telling the truth?
prepared evidence

The prepared evidence below is a query a colleague ran during an incident and the conclusion they drew from its result. Decide whether the conclusion holds, find each problem in the query, and say how you would prove a corrected version can find what it is looking for.

The second path is to run and adapt the patterns yourself, in the Advanced hunting console on the course dataset or in your own workspace.

Do this Run each pattern, then break it on purpose
  1. Run the baseline query in section five and explain each of the eight rows in one sentence.
  2. Change the case-insensitive match in section two to == and confirm the count drops to zero.
  3. Run the timeline union in section six, then remove the project lines and see what the columns look like.
  4. Write one query of your own that answers a question about the course dataset, and prove it can find a known positive before trusting a negative.
What you should end up with: five working patterns you can adapt, and the habit of checking that a query can find something before believing it found nothing.

Five operations carry most investigation queries: where to filter, with time first and =~ for anything typed; project and extend to shape, reaching into nested fields with tostring(); summarize to answer, where the by columns decide the meaning; join, with leftanti for baselines; and union, with every table shaped to the same columns. An empty or round-numbered result is checked before it is believed.

The next lesson turns query results into findings, and findings into what the organization must say, to whom and by when.

Resources

Microsoft revises these pages; where a page and this lesson disagree, the page is newer.