TH2.14 Advanced Aggregation Patterns

4-5 hours · Module 2 · Free

Operational Objective

Basic summarize with count() and dcount() handles simple aggregations. Hunting requires more — conditional counting, set operations on aggregated data, rolling windows, and multi-level aggregation that answers layered questions like "for each user, across how many unique countries, accessing how many distinct applications, with how many sessions exceeding the baseline?" This subsection teaches the aggregation patterns that campaign modules use for complex behavioral analysis.

Deliverable: Fluency with countif(), make_set(), make_bag(), set intersections, and multi-pass aggregation patterns used in M365 hunting campaigns.

⏱ Estimated completion: 25 minutes

Beyond count and dcount

Every analyst knows count() and dcount(). The aggregation patterns below are less common but appear in every campaign module.

countif() — conditional counting within a group

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
// Per-user sign-in breakdown: how many risky vs normal?
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize
    TotalSignIns = count(),
    RiskySignIns = countif(
        RiskLevelDuringSignIn in ("medium", "high")),
    NewDeviceSignIns = countif(
        DeviceDetail.isCompliant != true),
    OffHoursSignIns = countif(
        hourofday(TimeGenerated) < 6
        or hourofday(TimeGenerated) > 22)
    by UserPrincipalName
| extend RiskyRatio = round(100.0 * RiskySignIns / TotalSignIns, 1)
| where RiskyRatio > 10
// Users where >10% of sign-ins are risky = elevated investigation priority
// countif enables multi-dimensional profiling in a single summarize pass
| sort by RiskyRatio desc

countif() avoids multiple passes over the same data. Without it, you would need separate queries for each condition, then join the results. With it, one pass produces all the counts.

make_set() and make_bag() — collecting values per entity

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// Per-user profile: all IPs, all countries, all apps, all devices
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize
    IPs = make_set(IPAddress, 20),
    Countries = make_set(
        tostring(LocationDetails.countryOrRegion), 10),
    Apps = make_set(AppDisplayName, 15),
    Devices = make_set(
        tostring(DeviceDetail.displayName), 10),
    SignInCount = count()
    by UserPrincipalName
// Each user's complete authentication profile in one row
// make_set(x, N) collects up to N distinct values
// This profile is the basis for anomaly comparison:
//   "this user normally signs in from 3 IPs in 2 countries
//    accessing 5 apps from 2 devices"
// A hunt that finds them signing in from a 4th country
//   accessing a 6th app from a 3rd device = investigate

Comparing two users’ profiles — shared compromise indicators

When a hunt finds two suspect users, comparing their profiles reveals whether they share infrastructure (same attacker) or are independent compromises:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// Do two suspect users share IPs, apps, or countries?
let user1Profile = SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == "j.morrison@northgateeng.com"
| summarize
    User1_IPs = make_set(IPAddress, 30),
    User1_Countries = make_set(
        tostring(LocationDetails.countryOrRegion), 10),
    User1_Apps = make_set(AppDisplayName, 20);
let user2Profile = SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == "l.chen@northgateeng.com"
| summarize
    User2_IPs = make_set(IPAddress, 30),
    User2_Countries = make_set(
        tostring(LocationDetails.countryOrRegion), 10),
    User2_Apps = make_set(AppDisplayName, 20);
user1Profile | join user2Profile on 1==1
| extend SharedIPs = set_intersect(User1_IPs, User2_IPs)
| extend SharedCountries = set_intersect(
    User1_Countries, User2_Countries)
| extend SharedApps = set_intersect(User1_Apps, User2_Apps)
// SharedIPs with non-corporate IPs = same attacker infrastructure
// SharedCountries with unexpected countries = related compromises
// SharedApps with admin tools = coordinated privilege escalation
| project SharedIPs, SharedCountries, SharedApps,
    SharedIPCount = array_length(SharedIPs)

dcountif — conditional distinct counting

dcountif() counts distinct values that meet a condition. This is the hunting equivalent of “how many unique suspicious IPs does this user have?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
// Per-user profile with conditional counting
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize
    TotalIPs = dcount(IPAddress),
    RiskyIPs = dcountif(IPAddress,
        RiskLevelDuringSignIn in ("medium", "high")),
    NewCountries = dcountif(
        tostring(LocationDetails.countryOrRegion),
        ConditionalAccessStatus == "notApplied"),
    BlockedSignIns = countif(
        ConditionalAccessStatus == "failure")
    by UserPrincipalName
| where RiskyIPs > 0
// Users with at least one IP flagged as risky
// RiskyIPs / TotalIPs = what proportion of their IPs are risky?
// A user with 10 total IPs and 1 risky = probably VPN noise (10%)
// A user with 3 total IPs and 2 risky = 67% risky — investigate
| extend RiskyRatio = round(100.0 * RiskyIPs / TotalIPs, 0)
| sort by RiskyRatio desc

Set operations — comparing current against baseline

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// Which IPs are NEW this week compared to the 30-day baseline?
let baseline = SigninLogs
| where TimeGenerated between (ago(37d) .. ago(7d))
| where ResultType == 0
| summarize BaselineIPs = make_set(IPAddress, 50)
    by UserPrincipalName;
let current = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize CurrentIPs = make_set(IPAddress, 50)
    by UserPrincipalName;
current
| join kind=inner baseline on UserPrincipalName
| extend NewIPs = set_difference(CurrentIPs, BaselineIPs)
// set_difference returns elements in CurrentIPs NOT in BaselineIPs
// These are the genuinely new IPs — not seen in the prior 30 days
| where array_length(NewIPs) > 0
| project UserPrincipalName, NewIPs,
    NewIPCount = array_length(NewIPs)
| sort by NewIPCount desc
// set_difference is the KQL equivalent of "what changed?"
// Cleaner and faster than the anti-join pattern for set comparisons

Other set operations available: set_intersect() (common elements), set_union() (all elements from both), set_has_element() (membership test).

Multi-pass aggregation — layered questions

Some hunting questions require aggregating, then aggregating again. “How many users have more than 3 new IPs?” First aggregate per user (count new IPs), then aggregate across users (count users exceeding threshold).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
// How many users have anomalous authentication diversity?
let perUserProfile = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize
    IPCount = dcount(IPAddress),
    CountryCount = dcount(tostring(LocationDetails.countryOrRegion)),
    DeviceCount = dcount(tostring(DeviceDetail.displayName)),
    AppCount = dcount(AppDisplayName)
    by UserPrincipalName;
// Second aggregation: distribution of the profile metrics
perUserProfile
| summarize
    UsersWithMultiCountry = countif(CountryCount > 2),
    UsersWithHighIPDiversity = countif(IPCount > 10),
    UsersWithHighDeviceDiversity = countif(DeviceCount > 5),
    TotalUsers = count()
// Overview: how many users show high authentication diversity?
// If UsersWithMultiCountry is 3 out of 500, investigate those 3
// If UsersWithMultiCountry is 200 out of 500, the signal is too common
//   to be a useful hunting indicator — adjust the threshold

make_bag() for key-value profiling

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// Build a key-value profile per user: app → sign-in count
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize SignInCount = count()
    by UserPrincipalName, AppDisplayName
| summarize AppProfile = make_bag(
    bag_pack(AppDisplayName, SignInCount))
    by UserPrincipalName
// Each user has a bag: {"Outlook": 45, "Teams": 30, "SharePoint": 12}
// Useful for comparing profiles between users or between time periods
// An attacker's app profile will differ from the user's normal profile
//   — accessing admin tools the user never used, or mail-only access
//   when the user normally accesses 5+ applications

Figure TH2.14 — Four advanced aggregation patterns. Each enables a multi-dimensional hunting analysis in a single query.

Try it yourself

Exercise: Build a per-user authentication profile

Run the make_set() profile query against your environment. Pick 3 users from the results. Examine their profiles: IPs, countries, apps, devices. Does the profile match your expectation for their role?

Then run the set_difference query. How many users have new IPs this week? This is the foundation of the TH4 campaign — you are previewing it with production data.

⚠ Compliance Myth: "Complex aggregation queries are hard to audit — keep queries simple for compliance"

The myth: Compliance teams cannot audit complex KQL queries. Simpler queries are more defensible.

The reality: Compliance audits evaluate whether the hunting methodology is followed and whether findings are documented — not whether the KQL is simple or complex. A complex query that correctly identifies behavioral anomalies with documented results is more defensible than a simple query that misses them. Document the purpose of each query in the hunt record (TH1.7). The auditor reviews the record, not the syntax. Query complexity is an analyst skill concern, not a compliance concern.

Extend these patterns

KQL also supports `variance()`, `variancep()`, and `binary_all_and()` / `binary_all_or()` for specialized aggregations. `variance()` is useful in TH12 (C2 beaconing) for detecting low-variance inter-arrival times that indicate automated communication. `binary_all_and()` is useful for flag-based analysis where multiple conditions must all be true. These are niche — the four patterns in this subsection cover 90% of hunting aggregation needs.

📋 Operational Artifact — Aggregation Pattern Quick Reference

countif(condition): Count events matching condition within the same summarize — no separate query needed make_set(column, N): Collect up to N distinct values per group — build entity profiles set_difference(A, B): Elements in A not in B — “what is new?” set_intersect(A, B): Elements in both — “what is shared?” make_bag(bag_pack(key, value)): Key-value profile per entity — app usage maps, IP-to-count maps Multi-pass: summarize per entity → summarize across entities — “how many users exceed threshold X?”

References Used in This Subsection

Microsoft. “KQL Aggregation Functions.” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/aggregation-functions
Microsoft. “KQL set_difference().” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/set-difference-function

You're reading the free modules of this course

The full course continues with advanced topics, production detection rules, worked investigation scenarios, and deployable artifacts. Premium subscribers get access to all courses.

View Pricing See Full Syllabus

← TH2.13 Reusable Functions and Saved Queries TH2.15 KQL Anti-Patterns That Produce False Negatives →