TH2.5 top-nested for Frequency Analysis

4-5 hours · Module 2 · Free

Operational Objective

Hunting often requires multi-level frequency analysis: "What are the most common IP addresses per user per application?" A single summarize + top cannot answer multi-level questions because it flattens the hierarchy. top-nested preserves the nesting — giving you the top N of each category within the context of its parent category. This is how you find the rare combinations that indicate compromise.

Deliverable: The ability to use top-nested for multi-level frequency profiling in M365 hunting data — identifying rare user-IP-application combinations that warrant investigation.

⏱ Estimated completion: 25 minutes

Finding the rare in the frequent

Most sign-ins are from known IPs accessing known applications. The interesting signal is in the exceptions: the one IP an individual user has never used before, the one application accessed from an anomalous location, the one device that appeared for a single session.

top-nested performs hierarchical frequency analysis. It finds the top N values at each level of a hierarchy, preserving the parent-child relationship. This is more powerful than summarize | top because it shows you the rare items within each group — not just the globally rare items.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// For each user, what are their top 3 IPs and top 3 apps per IP?
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| top-nested 10 of UserPrincipalName by SignIns = count(),
  top-nested 3 of IPAddress by IPCount = count(),
  top-nested 3 of AppDisplayName by AppCount = count()
// Result: hierarchical table
//   User1 (500 sign-ins)
//     IP1 (400) → App1 (300), App2 (80), App3 (20)
//     IP2 (80)  → App1 (60), App4 (15), App2 (5)
//     IP3 (20)  → App5 (12), App1 (5), App6 (3)
//   User2 (300 sign-ins)
//     ...
// IP3 for User1 at only 20 sign-ins = potential anomaly
// App5 accessed only from IP3 = potential indicator

Hunting application: rare application access patterns

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// Which applications are accessed from unusual IPs?
// Applications normally accessed from corporate IPs but
// also accessed from unexpected locations = potential token theft
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| top-nested 20 of AppDisplayName by AppSignIns = count(),
  top-nested 5 of tostring(LocationDetails.countryOrRegion)
    by CountryCount = count()
// For each of the top 20 apps, the top 5 countries
// An app normally accessed from GB and US that suddenly
//   shows sign-ins from Romania = elevated investigation priority
// This pattern is impossible with a flat summarize

Finding rare combinations with top-nested + where

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
// Users who accessed high-privilege applications from unusual countries
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where AppDisplayName in (
    "Microsoft Azure Portal",
    "Microsoft Graph",
    "Azure Active Directory PowerShell",
    "Microsoft Exchange Online PowerShell")
| top-nested 50 of UserPrincipalName by AccessCount = count(),
  top-nested 5 of tostring(LocationDetails.countryOrRegion)
    by CountryAccess = count()
// For users accessing admin tools: from which countries?
// A Global Admin accessing Azure Portal from a new country
//   is a high-priority hunting lead

Figure TH2.5 — Flat versus nested frequency analysis. Flat top-N misses per-entity anomalies. top-nested reveals them.

Try it yourself

Exercise: Profile application access by country

Run the second query (top 20 apps, top 5 countries per app) against your environment. Examine the results for each application: are the countries expected for your organization's user base?

If an application normally accessed from 2–3 countries suddenly shows access from a 4th country, note it as a potential hunting lead. Cross-reference with the authentication anomaly patterns from TH2.1 — is the user from that country a new IP in their baseline?

⚠ Compliance Myth: "summarize | top is sufficient for frequency analysis in hunting"

The myth: Standard summarize and top operators provide the frequency analysis hunting needs.

The reality: summarize | top produces flat rankings — the most common values globally. It does not answer “what is unusual for this specific entity?” which is the core hunting question. top-nested provides the hierarchical view: for each entity, what are its most (and least) common behaviors? The rare behaviors within each entity’s profile are the hunting signals. Flat analysis finds globally unusual values. Nested analysis finds per-entity unusual values. Hunting needs both, but per-entity analysis is the higher-value pattern.

Extend this operator

top-nested supports `with others` to include a catch-all group for values outside the top N. This is useful for identifying what percentage of an entity's activity falls outside the top patterns: `top-nested 3 of IPAddress with others = "Other" by Count = count()`. If "Other" accounts for a significant percentage, the entity has diverse behavior that the top 3 do not adequately represent — and the "Other" category may contain the anomaly.

References Used in This Subsection

Microsoft. “KQL top-nested Operator.” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/top-nested-operator
Course cross-references: TH4 (per-user IP profiling), TH6 (per-app access profiling), TH7 (per-role activity profiling)

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.4 series_fir() and Time Series Smoothing TH2.6 autocluster() for Behavioral Grouping →