Agent Foskett Academy • Lesson 7 • Counting and Grouping with summarize
Counting and Grouping with summarize
Security telemetry becomes far more powerful when defenders stop looking at single events and start looking for patterns.
One failed sign-in might not matter. One email might not matter. One PowerShell process might not matter.
But when the same activity appears hundreds or thousands of times, the investigation changes.
The summarize operator helps defenders count events, group activity and identify suspicious behaviour across Microsoft Defender XDR and Microsoft Sentinel telemetry.
In this lesson, you will learn how to use summarize, count() and by to transform raw telemetry into investigation patterns.
The goal is simple:
Find the behaviour hiding inside the noise.
Lesson overview
Learn how to count events, group activity and reveal patterns across Microsoft Defender XDR and Sentinel investigations.
Plain-English summarize introduction
summarize, count() and by
Finding repeated behaviour in telemetry
Why summarize matters
Microsoft security telemetry can contain thousands of rows. Reading every row one by one is not always practical. The summarize operator helps defenders count, group and reduce large datasets into useful investigation patterns.
Count repeated behaviour
Summarize helps defenders see how often something happened instead of only seeing individual rows.
Group activity
Group events by sender, user, device, IP address, application, file name or any other useful field.
Reveal investigation patterns
Patterns can show unusual volume, repeated failures, suspicious senders, noisy accounts or unexpected endpoint activity.
The summarize operator
The summarize operator calculates values across rows. A simple way to start is by using count() to count how many rows match your query.
- 1
- 2
- 3
- 4
EmailEvents | where Timestamp > ago(24h) | summarize count()
Plain-English translation:
Show me how many email events appeared in the last 24 hours.
Show me how many email events appeared in the last 24 hours.
Understanding count()
The count() function counts rows. In security investigations, this is useful when you need to understand volume before deciding whether something deserves deeper investigation.
Count rowscount() tells you how many events matched your query.
Measure volumeVolume can help defenders understand whether activity is isolated or widespread.
Start simpleBefore grouping, a basic count can show whether your filter is returning too much or too little data.
Grouping with by
The real power of summarize appears when you group results with by. This lets defenders count activity per sender, user, device, IP address or other useful fields.
- 1
- 2
- 3
- 4
EmailEvents | where Timestamp > ago(24h) | summarize count() by SenderFromAddress
Plain-English translation:
Count how many emails each sender generated in the last 24 hours.
Count how many emails each sender generated in the last 24 hours.
Finding top senders
Lesson 6 introduced sorting. Lesson 7 now combines sorting with summarize so defenders can quickly identify the highest-volume senders, users, devices or processes.
- 1
- 2
- 3
- 4
- 5
EmailEvents | where Timestamp > ago(24h) | summarize EmailCount=count() by SenderFromAddress | order by EmailCount desc
EmailCountGiving the count a clear name makes the result easier to read.
Highest firstSorting by EmailCount desc shows the most active senders first.
Pattern huntingHigh volume may indicate campaigns, noisy systems, abuse or unusual sender behaviour.
Summarizing sign-in activity
Sign-in investigations often need grouping. A user with repeated failures, repeated success from unusual locations or high authentication volume may deserve closer attention.
- 1
- 2
- 3
- 4
- 5
SigninLogs | where TimeGenerated > ago(24h) | summarize SignInCount=count() by UserPrincipalName | order by SignInCount desc
Repeated activityGrouped sign-ins show which users generated the most authentication events.
Noisy accountsHigh counts may point to automation, misconfiguration, password spray attempts or active user activity.
Next investigation stepAfter identifying a user, drill back into their detailed sign-in events.
Summarizing endpoint activity
Endpoint investigations can also benefit from grouping. Counting process names can help defenders understand which binaries were active and whether unusual tools appear in the environment.
- 1
- 2
- 3
- 4
- 5
DeviceProcessEvents | where Timestamp > ago(24h) | summarize ProcessCount=count() by FileName | order by ProcessCount desc
Plain-English translation:
Count endpoint process events by file name, then show the most common processes first.
Count endpoint process events by file name, then show the most common processes first.
Combining where, summarize and order by
By this stage, your KQL query can follow a very practical investigation pattern: choose a table, filter the data, group the results and sort the highest counts first.
- 1
- 2
- 3
- 4
- 5
- 6
EmailEvents | where Timestamp > ago(24h) | where Subject contains "invoice" | summarize EmailCount=count() by SenderFromAddress | order by EmailCount desc
Plain-English translation:
Find invoice-related emails, group them by sender and show who sent the most.
Find invoice-related emails, group them by sender and show who sent the most.
Beginner mistakes with summarize
summarize is powerful, but it can also hide important details if defenders do not think carefully about what they are grouping and why.
Counting without filtering firstIf the query is too broad, the count may be too noisy to help the investigation.
Forgetting the by fieldsummarize count() gives one total. summarize count() by FieldName groups the results.
Assuming high volume is maliciousHigh activity may be suspicious, but it can also be normal system, application or user behaviour.
Investigator mindset
Attackers often repeat behaviour. Defender telemetry can reveal that repetition if you group the right fields and ask better questions.
Who did this the most?Group by user, sender, device, IP address or application to find the highest-volume activity.
What keeps repeating?Repeated behaviour can reveal campaigns, automation, misconfiguration, persistence or suspicious tooling.
What deserves a deeper look?Summarize helps defenders decide where to drill down next.
What you learned
In this lesson, you learned how summarize helps defenders count, group and identify patterns inside Microsoft security telemetry.
summarize groups dataThe summarize operator reduces many rows into useful grouped results.
count() totals rowscount() helps defenders measure activity volume across security telemetry.
by creates grouped investigationsUsing by lets defenders group activity by sender, user, device, file name or other fields.
Next lesson coming soon
The next Agent Foskett Academy lesson will build on summarize and introduce bin(), helping defenders group activity into time windows and find time-based investigation patterns.
Lesson 8 — Finding Time Patterns with bin()
Learn how bin() helps defenders group events into time windows and spot spikes, bursts and repeated behaviour.
Keep building the investigation
After counting and grouping results, the next step is learning how to understand activity over time.
Continue learning with Agent Foskett Academy, Microsoft Defender KQL Threat Hunting Guide, EmailEvents KQL Guide, KQL Email Spoofing, Microsoft Security and the GEMXIT Security Review.
Develop IT. Protect IT. GEMXIT PTY LTD | GEMXIT UK LTD