Reading width
Wide uses the full column for everything, text, diagrams, code, and exercises. Narrow keeps the standard reading width.
Text size
Scales the body text. Headings and code blocks keep their size.
In this section
KQL Foundations: summarize, count, dcount, Percentiles, make_set and Rates
Introduction
Most security questions are about how many: how many failures, how many devices, how many files, how many per day. summarize is the operator that answers them. It splits rows into groups by the values of the columns you name, works out one or more aggregates for each group, and returns one row per group.
Getting the grouping and the aggregate right is most of the work, and both can be wrong while the result looks fine.
This lesson summarizes a month of Microsoft 365 activity from the lab's OfficeActivity table, the cloud-app family's record of what users did in Exchange Online and SharePoint. It counts rows and distinct users, describes a skewed distribution, collects the evidence inside each group, counts by week and by kind of day, and computes a rate.
summarize turns rows into groups and each group into one row of aggregates.
This is the ground Module 4 builds on. Every query on the page runs in your browser against the same month.
From Rows to Groups
One row per group, one value per aggregateThe simplest summarize has one aggregate and one grouping column. This one has two aggregates, a count of rows and a count of distinct users, for each workload the table records:
OfficeActivity
| summarize Events = count(), Users = dcount(UserId) by OfficeWorkload
| order by Events desc
Each workload becomes one row, with its two aggregates beside it:
OfficeWorkload Events Users
-------------- ------ -----
Exchange 11082 86
SharePoint 5194 94
16,276 events split into Exchange, with 11,082 events from 86 users, and SharePoint, with 5,194 from 94. The input had one row per action a user took; the output has one row per workload, and nothing about any single action survives.
That is the most important thing to keep in mind about summarize: after it, a row means a group, and any question about individual events has to be asked before it.
The columns after by define the groups. With one column there is a group per value; with two, a group per combination of values that actually occurs. With none at all, the whole table is one group and the result is one row, which is how the earlier lessons counted a month in a single line.
The names on the left of each aggregate become the column names of the result, and naming them is worth the few characters it costs: a column called count_ in a result is a column somebody has to work out.
Every aggregate is computed from the rows in its group and nothing else. A count of users in the SharePoint row knows nothing about the Exchange row, so a user active in both workloads is counted once in each and twice in total. Adding distinct counts across groups is one of the commonest errors in summarizing, and the next section shows why the totals of different counts tell different stories.
From here on the lesson stays with SharePoint, the file side of the workload, because its five operations are easy to read. The filter on OfficeWorkload at the top of each query keeps it there, and every count below is a count of SharePoint events unless it says otherwise. Changing that one line moves the whole lesson to Exchange, where the same questions have different answers.
Counting Correctly
count, countif and dcount answer different questionsHow much file downloading happened? The question has at least three honest answers, and summarize can give all of them at once:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| summarize Events = count(),
Downloads = countif(Operation == 'FileDownloaded'),
Downloaders = dcountif(UserId, Operation == 'FileDownloaded')
It returns one row, with three counts of three different things:
Events Downloads Downloaders
------ --------- -----------
5194 1455 92
5,194 SharePoint events in all, of which 1,455 were downloads, done by 92 different users. countif() counts the rows that satisfy a condition without filtering the others out, which is what lets one summarize count the whole and a part side by side. dcountif() does the same for distinct values.
Each number answers a different question, how much activity, how much downloading and how many people, and a report that says downloads must say which. A security question usually needs the third kind: a hundred downloads by one person and a hundred by a hundred people are the same count of rows and very different situations.
The same table broken down by operation shows the rest of the picture:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| summarize Events = count(), Users = dcount(UserId) by Operation
| order by Events desc
Five operations, each with its events and its distinct users:
Operation Events Users
---------------------- ------ -----
FileAccessed 1881 93
FileDownloaded 1455 92
FileUploaded 927 91
FileModified 635 92
FileSyncDownloadedFull 296 78
The events column adds up to 5,194, as it must, because every event has exactly one operation. The users column does not add up to anything meaningful. A user who accessed, downloaded and uploaded files appears in three rows, so the sum counts them three times.
Counts of rows add across groups; counts of distinct things do not, and the check that catches the mistake is the same one the first lesson used: reconcile the parts against the whole, and expect the reconciliation to fail for distinct counts.
dcount() has one more property worth knowing. Microsoft documents it as an estimate rather than an exact count, with an error of about 0.8 percent at the default accuracy, because exact distinct counts over billions of rows are expensive.
On the lab's month it is exact. On a real workspace it is close, which is fine for a baseline and not fine for a figure that has to match another system to the unit.
Describing a Distribution
The mean, the median and the tailHow many files does a user download in a month? summarize can answer it in two steps: count downloads per user, then summarize those counts.
OfficeActivity
| where Operation == 'FileDownloaded'
| summarize Downloads = count() by UserId
| summarize Users = count(), Mean = round(avg(Downloads), 1),
Median = percentile(Downloads, 50), P90 = percentile(Downloads, 90)
The second summarize takes one row per user and describes the spread of their counts:
Users Mean Median P90
----- ---- ------ ---
92 15.8 8 34
92 users downloaded files. The mean is 15.8; the median is 8; the ninetieth percentile is 34. The mean is nearly double the median, which says the distribution is skewed: most users download a modest number of files and a few download a great many, enough to pull the average up. A report that said the typical user downloads 16 files would describe almost nobody.
The skew is visible without a chart if you read the three figures together. When the mean sits well above the median, a minority of large values is pulling it; when the ninetieth percentile is several times the median, the tail is long. Neither needs the individual users to be listed, which is useful in a public summary and in a first look at any new table.
The median, percentile(Downloads, 50), is the value half the users fall below, and it is what a report should call typical. The ninetieth percentile says where the heavy users start. Together they describe the distribution well enough to judge a single user against it: someone downloading ten times the ninetieth percentile in a week is worth a look, whatever the mean says.
Security data is almost always skewed this way. Sign-ins per user, processes per device, mail per sender and bytes per connection all have a long tail of a few large values, and every one of them is better described by percentiles than by an average.
Module 4's lesson on describing a distribution adds the rest of the toolkit: the minimum and maximum, the standard deviation and when it misleads, and percentiles computed in one pass for several cut points.
The two-step pattern used here, summarize per entity and then summarize the results, is one of the most useful shapes in KQL. The first summarize answers a question about each user; the second answers a question about users in general. Most baselines are built exactly this way.
What Was in the Group
The evidence behind a countA count says how many; it does not say what. make_set() collects the distinct values of a column inside each group into an array, so a group can carry its evidence with it. One user's SharePoint activity, collected that way, looks like this: FileDownloaded, FileModified, FileAccessed, FileUploaded, FileSyncDownloadedFull. Collected for every user and counted by how many kinds of operation each performed:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| summarize Kinds = dcount(Operation) by UserId
| summarize Users = count() by Kinds
| order by Kinds desc
The first summarize counts the operations each user performed; the second counts users at each level:
Kinds Users
----- -----
5 74
4 18
3 1
1 1
Of 94 SharePoint users, 74 performed all five operations and 18 performed four. 1 performed three and 1 only one.
In a baseline like this, the rare rows at the bottom are the ones to read: a user who only ever does one thing in SharePoint is either a very particular role, a service account or something else, and the way to find out is to collect the set and look at it.
This is why make_set() and its relative make_list(), which keeps duplicates and order, are among the most used aggregates in investigation. A count is the summary a manager reads; the set is the evidence an analyst checks. Both can sit in the same summarize, so a result row can say that a user did five things and list them, and nothing has to be run twice.
Sets have limits. make_set() returns at most a maximum number of values, 1,048,576 by default and fewer when the query passes a smaller limit, such as make_set(Operation, 4). A set used as evidence should be checked against a dcount of the same column, so that a limit never decides what the evidence shows.
A set also loses order and counts: it says which operations happened, not how often or in what sequence. Module 4's lesson on collecting evidence inside a group covers the variants that keep those, and arg_max(), which keeps the whole of the latest row in each group.
Activity Over Time
Groups by week, and partial periods at the edgesGrouping by time turns a table into a trend. startofweek() rounds each timestamp down to the start of its week, so grouping by it gives one row per week:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| summarize Events = count() by Week = startofweek(TimeGenerated)
| order by Week asc
Six weeks, each with its count of SharePoint events:
Week Events
------------------- ------
2026-02-08 00:00:00 230
2026-02-15 00:00:00 1267
2026-02-22 00:00:00 1174
2026-03-01 00:00:00 1166
2026-03-08 00:00:00 1354
2026-03-15 00:00:00 3
The four middle weeks hold between 1,166 and 1,354 events each. The first holds 230 and the last 3.
Nothing happened in those weeks to cut activity; the month simply starts on a Friday and ends at noon on a Sunday, so the first week contains two days of data and the last contains half a day. A chart of these six rows would show activity collapsing at both ends, and it would be wrong.
Partial periods at the edges of a window are the most common trap in counting over time. They appear whenever a window does not start and end on the boundaries of the periods being counted, which is almost always, because ago(30d) starts at the current minute, not at midnight on a Monday.
The fixes are to align the window to whole periods, to drop the partial periods before reading the trend, or to compare rates per day rather than totals.
bin() is the general form of the same idea. bin(TimeGenerated, 1d) rounds to the day, bin(TimeGenerated, 1h) to the hour, and any width can be used.
Groups by bin() have the same edge problem and one more: a period with no events produces no row at all, so a gap in activity disappears from the result instead of showing as a zero. Module 8 is about keeping those zeros, and Module 4's lesson on activity over time introduces both problems.
The four whole weeks are also the honest baseline for SharePoint in this month: an average of 1,240 events a week. It is a figure worth writing down beside the query that produced it, with the dates it covers, because a baseline without its window cannot be compared with anything later.
The same habit applies to every number in this lesson: a count is only meaningful with the period, the filter and the grouping that produced it. That figure, not the six-row average, is what a later week should be compared with.
Weekdays and Weekends
Compare per day, not in totalTotals compare badly when the groups cover different amounts of time. A month has many more weekdays than weekend days, so a total for each says more about the calendar than about behavior. Counting the days in each group and dividing gives a rate per day that can be compared:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| extend Weekend = dayofweek(TimeGenerated) in (0d, 6d)
| summarize Events = count(), Days = dcount(startofday(TimeGenerated)) by Weekend
| extend PerDay = round(1.0 * Events / Days, 1)
Each kind of day gets its events, the number of distinct days it covers, and the events per day:
Weekend Events Days PerDay
------- ------ ---- ------
true 48 10 4.8
false 5146 21 245
Weekdays average 245 SharePoint events a day across 21 days; weekend days average 4.8 across 10. The ratio is the finding, and it is a baseline in its own right: file activity in this organization is a working-day activity, so a weekend day with weekday volume would be unusual whoever caused it.
The days were counted with dcount(startofday(TimeGenerated)), the number of distinct calendar days that appear in each group, rather than assumed from the calendar. That matters when data is missing.
A day on which a connector stopped would not appear, and a per-day rate computed from calendar days would silently include a zero for it, while one computed from observed days would not. Which is right depends on the question, and the query should make the choice deliberately.
The division used 1.0 * Events / Days, and the next section explains why the 1.0 is there. The same pattern, an amount divided by the time it covers, is how Module 4 and Module 8 compare periods of different lengths, from this week against last to a holiday against an ordinary week.
There is one more choice hidden in the query: what counts as a weekend. dayofweek() returns the number of days since Sunday, so 0 and 6 are Sunday and Saturday, in UTC. An organization with a different working week, or staff far from UTC, would define the weekend differently, and the definition belongs in the query where a reader can see it.
Rates Done Right
Integer division, and the denominatorA rate divides one count by another. Here is the share of SharePoint events that were downloads, computed two ways in one query:
OfficeActivity
| where OfficeWorkload == 'SharePoint'
| summarize Events = count(), Downloads = countif(Operation == 'FileDownloaded')
| extend Wrong = Downloads / Events,
Percent = round(100.0 * Downloads / Events, 1)
The first column divides the two counts as they are; the second multiplies by 100.0 first:
Events Downloads Wrong Percent
------ --------- ----- -------
5194 1455 0 28
The first way returns 0. Both counts are whole numbers, and dividing one whole number by another in KQL gives a whole number, with the fraction thrown away. 1,455 divided by 5,194 is less than one, so it becomes zero.
Multiplying by 100.0, a real number, before dividing makes the arithmetic real and gives 28 percent. This mistake produces rates of exactly zero and percentages that are always 0 or 100, and it is easy to miss because zero is a believable answer.
The denominator is the other half of a rate. 28 percent of SharePoint events were downloads. Divided by every OfficeActivity event instead, Exchange included, the figure would be much smaller and would answer a different question.
A rate should say what it is a rate of, and the denominator should be the set of things that could have been the numerator. Choosing the aggregate comes down to naming that thing first:
The ladder is short because most questions land on one of its first two rungs, and the mistakes on this page come from landing on the wrong one: counting rows when the question was about people, or averaging when the question was about a typical person. Three of those mistakes, side by side:
All three mistakes return numbers that look reasonable, which is why each one survives in real reports. The checks that catch them are quick: read the median beside the mean, look at the first and last periods before reading a trend, and ask what a rate of zero would mean before believing one.
What Module 4 builds
Module 4Module 4 builds on this lesson one aggregate at a time, ending on showing an aggregate as a chart. The habits to carry into it: decide what a row means after the summarize, count the thing the question names, describe skewed data with percentiles, keep the evidence beside the count, watch the edges of a time window, compare per day, and make every division real.
Practice
Read the aggregates before you report them prepared evidenceThe prepared evidence below is two of this lesson's results: SharePoint events by week, and the distribution of downloads per user. Find the weeks that cannot be compared with the others, the measure a report should call typical, and the download rate. Try each question before opening its answer, and say which part of the lesson decided it.
The two tabs are small on purpose. Six weekly counts and five measures are about as much as a report puts in front of a reader, and that is exactly where the mistakes on this page get made: not in the query, but in the sentence somebody writes about its result.
All three questions are about reading an aggregate rather than computing one. The numbers are correct; what goes wrong in practice is the sentence written about them, and each question has an answer that is arithmetically right and wrong as a description.
Then run the queries on this page and change them. Group the weekly query by day with bin(TimeGenerated, 1d) and find the weekend days, then add the ninety-ninth percentile to the distribution query and see how far the tail reaches.
summarize turns rows into groups and each group into a row. Count what the question names, describe skewed values with the median and percentiles, keep the evidence in a set beside the count, compare whole periods and rates per day, and multiply by 100.0 before dividing.
The next lesson puts two tables side by side: union, join, the join that drops rows by default, and the rows that have no match.