TH2.10 Entity Pivoting Across Tables

4-5 hours · Module 2 · Free

Operational Objective

TH1.9 introduced multi-table correlation patterns. This subsection teaches the KQL mechanics for efficient entity pivoting — starting from a finding in one table and tracing the entity's activity across all relevant tables. This is step 4 (pivot) in the iterative query funnel — the step that expands from an indicator to a complete activity timeline.

Deliverable: Production-ready entity pivot query templates for users, IPs, devices, and applications — adaptable to any campaign module's enrichment needs.

⏱ Estimated completion: 25 minutes

Pivot from indicator to timeline

You have a suspect user from the hunt. You need their complete activity across every M365 data source. The pivot query builds that timeline.

User entity pivot — the universal enrichment query

 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
// Complete activity timeline for a suspect user
let suspectUser = "j.morrison@northgateeng.com";
let pivotWindow = 7d;
union
    // Authentication activity
    (SigninLogs
    | where TimeGenerated > ago(pivotWindow)
    | where UserPrincipalName == suspectUser
    | project TimeGenerated, Source = "SignIn",
        Detail = strcat(IPAddress, " → ", AppDisplayName,
            " [", RiskLevelDuringSignIn, "]")),
    // Token refresh activity
    (AADNonInteractiveUserSignInLogs
    | where TimeGenerated > ago(pivotWindow)
    | where UserPrincipalName == suspectUser
    | project TimeGenerated, Source = "TokenRefresh",
        Detail = strcat(IPAddress, " → ", AppDisplayName)),
    // Directory modifications
    (AuditLogs
    | where TimeGenerated > ago(pivotWindow)
    | where InitiatedBy.user.userPrincipalName == suspectUser
    | project TimeGenerated, Source = "AuditLog",
        Detail = OperationName),
    // Cloud app activity
    (CloudAppEvents
    | where TimeGenerated > ago(pivotWindow)
    | where AccountId == suspectUser
    | project TimeGenerated, Source = "CloudApp",
        Detail = strcat(ActionType, " → ", Application)),
    // Email activity
    (EmailEvents
    | where TimeGenerated > ago(pivotWindow)
    | where SenderFromAddress == suspectUser
    | project TimeGenerated, Source = "EmailSent",
        Detail = strcat("To: ", RecipientEmailAddress,
            " | ", Subject))
| sort by TimeGenerated asc
// Complete chronological timeline across all M365 data sources
// Read the timeline from top to bottom:
//   sign-in → what they did → what they accessed → what they sent
// This is the analysis query for TH1.4 behavioral enrichment

IP entity pivot — shared infrastructure detection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// Which users authenticated from the same suspicious IP?
let suspectIP = "203.0.113.47";
let pivotWindow = 30d;
SigninLogs
| where TimeGenerated > ago(pivotWindow)
| where IPAddress == suspectIP
| summarize
    Users = make_set(UserPrincipalName, 20),
    UserCount = dcount(UserPrincipalName),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    Apps = make_set(AppDisplayName, 10)
// UserCount > 1 from the same attacker IP = multiple compromised accounts
// UserCount == 1 = targeted attack on a single account
// This distinction determines whether the hunt finding is an isolated
//   compromise or a wider campaign requiring broader investigation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// What is this application doing after being consented?
let suspectAppId = "aaaabbbb-cccc-dddd-eeee-ffffgggghhhh";
let pivotWindow = 90d;
union
    // Application consent event
    (AuditLogs
    | where TimeGenerated > ago(pivotWindow)
    | where TargetResources[0].id == suspectAppId
    | project TimeGenerated, Source = "Consent",
        Detail = OperationName),
    // Application sign-ins
    (AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(pivotWindow)
    | where AppId == suspectAppId
    | project TimeGenerated, Source = "AppSignIn",
        Detail = strcat(IPAddress, " → ",
            ResourceDisplayName))
| sort by TimeGenerated asc
// Timeline: when was it consented → when did it start signing in
//   → which resources did it access → from which IPs?

Figure TH2.10 — Entity pivot pattern. Start with the suspect entity. Query every relevant table. Build the chronological timeline.

Try it yourself

Exercise: Build a user timeline

Pick a user from your environment (yourself or a test account). Run the user entity pivot query with a 7-day window. Read the timeline chronologically. Does the activity pattern match your expectation for that user's role?

This exercise builds environmental knowledge (TH0.11) — understanding what a normal user timeline looks like makes anomalous timelines visible during campaigns.

⚠ Compliance Myth: "Investigating a specific user without a formal alert violates privacy policies"

The myth: Running entity pivot queries on specific users during hunting — when no alert has fired — may violate data privacy policies or employee monitoring regulations.

The reality: Threat hunting is a legitimate security function. The pivot is performed on security log data that the organization has lawful basis to process for security purposes (GDPR Article 6(1)(f) — legitimate interests, or the organization’s acceptable use policy). However, the concern is not frivolous. Document why the entity was selected (the hunt hypothesis and the indicator that flagged them) in the hunt record. This documentation demonstrates that the investigation was proportionate and hypothesis-driven, not arbitrary surveillance. TH1.7 (hunt documentation standard) provides this traceability by design.

Extend this approach

The `union` pattern produces a flat timeline. For richer visualization, use Sentinel notebooks (Jupyter + MSTICPy) which can render entity timelines as interactive graphical displays with color-coded events by source. TH16 covers notebook-based hunting. For query-based hunting in Advanced Hunting, the `union | sort by TimeGenerated` pattern is the standard approach used throughout the campaigns.

📋 Operational Artifact — Entity Pivot Templates

User pivot: union (SigninLogs, AADNonInteractive, AuditLogs, CloudAppEvents, EmailEvents) filtered by UPN → sort by TimeGenerated
IP pivot: SigninLogs | where IPAddress == suspectIP | summarize Users, UserCount, FirstSeen, LastSeen, Apps
App pivot: union (AuditLogs consent, AADServicePrincipalSignInLogs) filtered by AppId → sort by TimeGenerated
Device pivot: union (DeviceProcessEvents, DeviceFileEvents, DeviceRegistryEvents, DeviceNetworkEvents, DeviceLogonEvents) filtered by DeviceName → sort by TimeGenerated

References Used in This Subsection

Microsoft. “KQL union Operator.” Microsoft Learn. https://learn.microsoft.com/en-us/kusto/query/union-operator
Course cross-references: TH1.3 (step 4 — pivot), TH1.4 (behavioral enrichment), TH1.9 (multi-table correlation)

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.9 materialize() for Query Performance TH2.11 Graph Semantics for Process Tree Analysis →