In this section

KQL Foundations: project, extend, case, Normalizing Text, top and distinct

Module 0

Introduction

A filter decides which rows a query keeps. Everything after it decides what those rows say: which columns they carry, what new values are worked out from the old ones, how messy values are cleaned, what order the rows come in and how many the reader sees. That is shaping, and it is where a correct set of rows can still turn into a wrong answer.

This lesson shapes a month of mail, 15,468 messages in the lab's EmailEvents table, the mail family's record of every message the organization sent and received. It chooses columns, derives new ones, classifies messages by outcome, finds a folder recorded under two spellings, works with the hour a message arrived, orders and limits the result, and counts distinct senders.

Chooseproject:which columnsthenDeriveextend, case:new valuesthenCleantolower, replace:one spellingthenOrdersort, top:what comes first

Shaping changes what each row carries and the order rows come in, never which events happened.

This is the ground Module 3 builds on. Every query on the page runs in your browser against the same month.

01

Choosing Columns

project keeps the columns the question needs

A row of EmailEvents carries 27 columns, from identifiers nobody reads to the subject line everybody does. A result that shows all of them hides the answer among them. project keeps only the columns named, in the order named:

EmailEvents
| where EmailDirection == 'Inbound' and ThreatTypes == ''
| project Timestamp, SenderFromDomain, Subject, Links = UrlCount,
    Files = AttachmentCount
| take 3

The where keeps inbound mail with no threat recorded, the project keeps five columns, and take keeps three rows:

Timestamp            SenderFromDomain     Subject              Links  Files
-------------------  -------------------  -------------------  -----  -----
2026-03-15 11:46:56  accounts.google.com  Out of office reply  3      0
2026-03-15 11:16:55  slack.com            Weekly digest        3      0
2026-03-15 11:10:28  docusign.net         Project update       2      0

Three ordinary messages, each reduced to when it arrived, which domain sent it, its subject and how many links and attachments it carried. The project did not change a single message, though it did give the two counts shorter names; it changed what the reader sees of each one.

The column order is a decision too: the time first, because a reader scanning mail thinks in time, then the sender, then what the message was about.

project also renames as it selects. A column can be given a new name with an equals sign, such as project Received = Timestamp, and a computed value can be added in the same list. That makes project the last line of most queries meant for a person: it decides the final columns, their names and their order in one place.

There are relatives for other jobs. project-away drops named columns and keeps the rest, which is useful when a table has many columns and only a few are noise. project-rename renames without selecting. project-reorder moves columns to the front and keeps the others. Module 3's lesson on choosing and naming columns works through all of them.

A narrow result is also faster. Every column a query carries has to be read and passed along, and on a wide table with long text columns, such as the subject and body details of mail, carrying fewer columns makes a measurable difference. Projecting early in a long query, keeping only what later steps need, is one of the habits Module 10 measures.

Choosing columns is also a check on the question. If a result needs a column to answer it, the project has to keep that column, and writing the project line forces the question: what does the reader need to see to believe this answer? Columns that do not serve that question are usually better left out, because every extra column is something a reader has to decide to ignore.

02

Deriving New Values

extend adds a column worked out from others

extend adds a column to every row, computed from the columns already there, and keeps everything else. It is how a question that the table does not answer directly becomes one it does. Does inbound mail carry links, files, both or neither?

EmailEvents
| where EmailDirection == 'Inbound'
| extend HasLink = UrlCount > 0, HasFile = AttachmentCount > 0
| summarize Messages = count() by HasLink, HasFile
| order by Messages desc

Two new true-or-false columns, then a count of each combination:

HasLink  HasFile  Messages
-------  -------  --------
true     false    8142
false    false    2821
true     true     1488
false    true     516

Of 12,967 inbound messages, 8,142 carried links and no attachment, and 2,821 carried neither. The table records UrlCount and AttachmentCount as numbers; neither column says whether a message had a link. The extend turned two numbers into two answers, and the summarize grouped by them. The counts add up to every inbound message, which is the check that the classification missed nothing.

Derived columns can be any expression: arithmetic, string functions, date functions, comparisons, the result of a lookup. They are evaluated once per row, so an extend on a million rows does a million calculations. That is cheap for arithmetic and expensive for heavy string work, which is one reason to filter before extending, so the work is done only on the rows that matter.

An extend can also replace a column by giving the new value an existing name. That is occasionally useful for cleaning a value in place, and occasionally a source of confusion when the next person reads the query and expects the original. A new name for a derived value is usually the clearer choice, and it keeps the raw value available for checking.

The derived columns here are booleans, and booleans group well: four combinations, each easy to name. A derived number, such as the total of links and attachments, would group into as many rows as there are totals, which is rarely what a reader wants.

Deriving a value is also choosing how coarse it is, and the coarseness should match the question. Whether a message carried a link is a question with two answers, so the derived value has two.

Module 3 opens with deriving new values because so much of analysis is this move: the data records what happened in its own terms, and the question needs it in yours.

03

Classifying Rows

case() and the column it tests

A classifier puts each row in one of a few categories, and case() is the usual way to write one. It takes pairs of a condition and a value, tests the conditions in order, returns the value of the first that is true, and returns the last argument if none is. Here is a classifier of every message by outcome, written the way many analysts would write it first:

EmailEvents
| extend Outcome = case(DeliveryAction == 'Blocked', 'Stopped',
    DeliveryLocation has 'junk', 'Junk',
    'Inbox')
| summarize Messages = count() by Outcome

The first condition catches blocked mail, the second junked mail by its folder, and everything else is called Inbox:

Outcome  Messages
-------  --------
Inbox    14993
Stopped  471
Junk     4

Four junked messages in a month. The number looks plausible, and it is wrong. The second condition tests DeliveryLocation with has 'junk', and most junked messages in the lab record their folder as JunkFolder, written as one word.

KQL reads JunkFolder as a single term, so has 'junk' does not match it, and those messages fall through to the default and are counted as Inbox. The classifier ran cleanly and moved hundreds of messages into the wrong group.

The fix is to classify on the column that carries the meaning. DeliveryAction records what happened to a message as a code, Junked for every junked message, however the folder was written:

EmailEvents
| extend Outcome = case(DeliveryAction == 'Blocked', 'Stopped',
    DeliveryAction == 'Junked', 'Junk',
    'Inbox')
| summarize Messages = count() by Outcome

The same three categories, now decided by the delivery action:

Outcome  Messages
-------  --------
Inbox    14709
Stopped  471
Junk     288

288 junked, 471 stopped, 14,709 delivered to the inbox, and the three add up to all 15,468 messages. The lesson generalizes. A classifier should test the most reliable column for the meaning it wants, usually a code or a status, before a descriptive string. Its default should be checked too, because a default quietly collects every row the other conditions missed, and here it collected the error.

Order inside case() matters as well. The conditions are tested from the first to the last and the first true one wins, so a broad condition placed early swallows rows a narrower condition later was meant to catch. Put the most specific conditions first, and when two categories could overlap, decide which one a row belongs to before writing the classifier, not after reading its counts.

04

Cleaning Text

One folder, two spellings

Why did the folder have two spellings? Real security data comes from many sources and many versions of the same source, and the same value is often written more than one way: different case, extra spaces, a prefix one product adds and another does not. Look at the junked mail by the folder it records:

EmailEvents
| where DeliveryAction == 'Junked'
| summarize Messages = count() by DeliveryLocation

Grouped by the raw value, the one folder appears as two:

DeliveryLocation  Messages
----------------  --------
JunkFolder        284
Junk folder       4

284 messages say JunkFolder and 4 say Junk folder. Any summarize by DeliveryLocation reports two folders where there is one, and any filter on one spelling misses the other.

Nothing in either spelling is wrong on its own, which is why the problem survives so long in real queries: each value looks reasonable when it is read alone. The cure is to normalize the value into one form before grouping or filtering on it, by lowering its case and removing the spaces:

EmailEvents
| extend Folder = replace_string(tolower(DeliveryLocation), ' ', '')
| summarize Messages = count() by Folder

After normalizing, each folder has one name and its full count:

Folder      Messages
----------  --------
inbox       14709
quarantine  471
junkfolder  288

The 288 junked messages now group as one folder. tolower() and replace_string() are the simplest tools for this; trim() removes spaces at the ends, and Module 3 adds the rest, including extracting the part of a value that matters and mapping known variants to one canonical name.

Three shaping mistakes that change the answer
has 'junk' finds every junked message.
JunkFolder is one term; it finds 4 of 288.classify on the code
take 10 shows the top ten.
take returns any ten rows, with no order promised.top 10 by
A spelling is a spelling.
JunkFolder and Junk folder split one folder into two groups.normalize first
Shape on the column that carries the meaning, then check the groups add up.

Two spellings of one folder is a small example of a large problem. Account names arrive with and without a domain, host names with and without a DNS suffix, file paths with forward and backward slashes, addresses with and without a port.

Every one of them splits a group or defeats an exact filter in exactly the way JunkFolder did, and every one is found the same way: summarize by the raw column and read the distinct values before trusting any grouping on it.

Normalizing has a cost: the cleaned value is no longer what the source recorded. Keep the original column alongside the cleaned one while you work, so that when a count looks odd you can see what the raw values were.

05

Time Values

The hour a message arrived

Every row has a timestamp, and many questions are about the time rather than the date: what hour, which weekday, how long between two events. KQL has functions that take a datetime apart, such as hourofday(), dayofweek() and startofday(), and others that format it for a reader, such as format_datetime(). Group inbound mail by the part of the day it arrived:

EmailEvents
| where EmailDirection == 'Inbound'
| extend Hour = hourofday(Timestamp)
| summarize Messages = count() by Period = case(Hour < 8, 'Before 08:00',
    Hour < 18, '08:00 to 17:59', 'From 18:00')
| order by Messages desc

The hour is extracted with extend, then a case() turns it into three periods:

Period          Messages
--------------  --------
08:00 to 17:59  12841
Before 08:00    76
From 18:00      50

12,841 messages arrived between 08:00 and 17:59, 76 before and 50 after. The lab's mail is a working day's mail, which is itself a baseline: a burst of inbound messages at three in the morning would stand out against it at once.

Two cautions come with time values. The first is the time zone. Timestamps in Sentinel and Defender are recorded in UTC, so hourofday() returns the UTC hour; a team in another zone has to shift the value before reading it as local working hours.

The second is that a formatted time is a string. format_datetime() makes a timestamp readable, and the result can no longer be compared or subtracted as a time, so it belongs at the end of a query, in the project a person reads, not in the middle where later steps need the real value.

Grouping into three periods was a choice as well. Twenty-four hourly rows show the shape of a day in detail; three periods answer the question asked, whether mail arrives outside working hours, in a table a reader takes in at a glance. The same data supports both, and the question decides which one the result should be.

Module 3's lesson on time values and formats covers both, along with the functions that round a time down to the start of its day, week or month, which are how most reports group events.

06

Order and Limits

sort, top and take

Rows in a KQL result have no order unless the query gives them one. sort by orders the rows by one or more columns; top N by orders them and keeps the first N in a single step. Here are the three domains that sent the most inbound mail:

EmailEvents
| where EmailDirection == 'Inbound'
| summarize Messages = count() by SenderFromDomain
| top 3 by Messages

top sorts by the count in descending order and stops after three:

SenderFromDomain  Messages
----------------  --------
salesforce.com    1247
xero.com          1247
slack.com         1234

The first two domains tie at 1,247 messages each. A tie at the boundary of a top is worth noticing: if the third and fourth domains had tied, top 3 would have kept one of them and dropped the other with nothing in the result to say so. When a ranking matters, check the value just below the cut.

take is different. It returns the number of rows asked for, with no promise about which ones, and Microsoft's documentation says so plainly: unless the data is sorted, take can return different rows each time it runs.

The take 3 in the first section was fine because it only had to show what a shaped row looks like. It would not have been fine as an answer to which messages came first, and a query that says take 10 is never an answer to which ten matter.

Sorting has a direction too. sort by and top default to descending order, largest first, which suits counts and the latest events. For the earliest events, or the smallest values, the query says asc. Mixing the two up returns the opposite end of the data, a full set of rows, and no error.

The habit is to say what the order means before choosing the operator. If the question is any few rows to look at, take is cheap and honest. If the question is the most, the latest or the largest, it is top or sort, and the column it sorts by is part of the question.

07

Distinct Values and the Reader

dcount, distinct and the final shape

How many different domains sent inbound mail? Counting distinct values is so common that KQL has a function for it, dcount(), and an operator that lists them, distinct:

EmailEvents
| where EmailDirection == 'Inbound'
| summarize Domains = dcount(SenderFromDomain), Senders = dcount(SenderFromAddress),
    Subjects = dcount(Subject)

It returns one row, with a distinct count for each of the three columns:

Domains  Senders  Subjects
-------  -------  --------
33       35       1008

33 domains, 35 sender addresses and 1,008 distinct subjects. distinct SenderFromDomain followed by count gives the same 33, as a list rather than a number. One caution belongs here: on large data dcount() is an estimate, accurate to a small error, which Module 4 explains. On the lab's month it is exact.

The last step of most queries is shaping the result for the person who will read it. Which domains sent the most blocked mail, and what kind of threat was it?

EmailEvents
| where EmailDirection == 'Inbound' and DeliveryAction == 'Blocked'
| summarize Blocked = count() by Sender = SenderFromDomain, Threat = ThreatTypes
| top 3 by Blocked
| project Threat, Sender, Blocked

The summarize names its columns as it groups, top keeps three, and the project puts the threat first:

Threat  Sender                      Blocked
------  --------------------------  -------
Phish   sharepoint-docs-access.com  85
Phish   docusign-secure-review.com  82
Phish   m365-mailbox-verify.com     80

The groups are named as they are made: Sender = SenderFromDomain inside the summarize gives the column the name the reader will see, so no rename is needed later.

The threat comes first because the reader of a blocked-mail report thinks in threats, then the sender, then the count. Nothing in the data required that order; the reader did. That is the last rung of a short climb this module repeats in every query meant for a person:

Shaping a result for its reader
1Which rows?
the filter already decided thiswhere
2Which columns, in what order?
what the reader reads first goes leftproject
3What are they called?
names the reader understandsproject, project-rename
4In what order are rows?
the most important firstsort, top
5How many rows?
enough to answer, few enough to readtop, take
Every rung is a decision about the reader, not about the data.

Most of the shaping in this lesson sits on those rungs. The project in the first section chose and ordered columns, the summarize here named them, and top decided both the order and the count. A query that has climbed all five is one a colleague can read without asking what any column means.

What Module 3 builds

Module 3

3.1 to 3.3

deriving new values, classifying rows, cleaning and normalizing text

3.4 to 3.5

time values and formats, choosing and naming columns

3.6 to 3.8

ordering and limiting results, removing duplicates, shaping a result for its reader

Module 3 builds on these steps one lesson at a time, ending on a lesson about shaping a result for its reader. The habits to carry into it: choose columns for the question, derive values the table does not record, classify on codes before descriptions and check the default, normalize before grouping, keep times as times until the last line, and never mistake take for top.

Do

Practice

Find the groups that should be one
prepared evidence

The prepared evidence below is the month's mail counted by direction, delivery action and delivery location. Before shaping it into a report, find the rows that are one folder written two ways, the column a junk classifier should test, and the right count of junked mail. Try each question before opening its answer, and say which column decided it.

A grouped count like this is the first thing to read before writing any classifier, because it shows every value a column holds and how often. Two values that should be one are visible here at a glance, and invisible in any query that already assumes one spelling.

Read the table row by row before answering. Each row is one combination of three values, so a row tells you that a combination exists and how common it is. A combination that appears with a handful of messages next to a near-identical one with hundreds is the pattern a second spelling makes, and it is worth checking every time you see it, in mail data or anywhere else.

Then run the queries on this page and change them. Add DeliveryLocation to the good classifier's summarize and see both spellings appear under Junk, then change the time periods in the hour query to split the working day at noon.

Shaping changes what rows say and the order they come in. Choose columns for the question, derive what the table does not record, classify on the column that carries the meaning and check the default, normalize before grouping, keep times as times, and use top for order and take only for a look.

The next lesson goes from shaping rows to summarizing them: counting, counting distinct, counting by day, and keeping the evidence inside each group.