Agent Foskett Academy • KQL Academy • Module 11 • Lesson 138

Lesson 138 — Following an Identity Across Multiple Tables

Identity investigations become much more powerful when the analyst stops asking each table a separate question.

A sign-in may appear in Microsoft Entra. An identity profile may live in IdentityInfo. Authentication or directory logon activity may appear in IdentityLogonEvents. Cloud behaviour may appear in CloudAppEvents.

The attacker does not care which Microsoft product owns the telemetry. Neither should the investigation. In this lesson we follow one identity across multiple tables and turn separate evidence sources into one timeline.

The identity is the thread. The tables are only different places where that thread leaves evidence.

Start Lesson 138 Back to KQL Academy

Agent Foskett KQL Academy identity correlation across multiple tables

What you will investigate

One identity whose evidence is distributed across Microsoft Entra and Microsoft Defender XDR telemetry.

✓ Normalize identity pivots

✓ Query several evidence tables

✓ Union events into one timeline

✓ Correlate activity by IP and time

The investigation begins

08:51 UTC ↓ Entra sign-in appears ↓ 09:03 Identity logon activity appears elsewhere ↓ 09:11 Cloud application activity begins ↓ The analyst opens three different portals ↓ Each event looks ordinary on its own ↓ Then the same user and source IP are aligned by time ↓ QUESTION What story appears when the tables are read together?

Learning objectives

Use a single user identity as the pivot across SigninLogs, IdentityInfo, IdentityLogonEvents and CloudAppEvents, normalize different identity fields and combine events into one chronological investigation view.

The first challenge: identity fields differ

Microsoft security tables do not always use the same column names for the same person. One table may use UserPrincipalName, another AccountUpn, and another AccountId. Good correlation starts by understanding and normalizing those identifiers.

Step 1 — establish the Entra sign-in evidence

Start with SigninLogs and shape the output into generic timeline fields: time, source table, user, event type, detail and IP address.

12345678910111213

let TargetUser = "alex.wilson@contoso.com";
SigninLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName =~ TargetUser
| project EventTime=TimeGenerated,
          SourceTable="SigninLogs",
          User=UserPrincipalName,
          EventType="Entra sign-in",
          Detail=AppDisplayName,
          IPAddress,
          ResultType,
          ConditionalAccessStatus
| order by EventTime asc

Why rename the columns?

We are preparing different tables to fit the same investigation shape. Once every table exposes an EventTime, SourceTable, EventType, Detail and IPAddress, combining them becomes much easier.

Preserve the original table

The SourceTable field is important. After combining events, you still need to know which telemetry source produced each row so you can validate the evidence and pivot back to the native schema.

Step 2 — enrich the identity with IdentityInfo

IdentityInfo provides account information from Microsoft Entra and other identity sources. Use the latest record to understand who the account belongs to and how important it may be.

1234567891011121314

let TargetUser = "alex.wilson@contoso.com";
IdentityInfo
| where Timestamp > ago(7d)
| where AccountUpn =~ TargetUser
| summarize arg_max(Timestamp, *)
          by AccountUpn
| project Timestamp,
          AccountUpn,
          AccountDisplayName,
          Department,
          JobTitle,
          RiskLevel,
          CriticalAssetDesignation,
          AccountObjectId

Why identity context matters

The same suspicious sign-in means something different for a standard user, finance executive, domain administrator or identity marked as a critical asset. Context changes investigation priority.

IdentityInfo is enrichment, not chronology

Use it primarily to understand the account. It is not necessarily the same kind of event stream as sign-in or cloud activity, so do not force it into the timeline if the question is simply “what happened next?”

Step 3 — query IdentityLogonEvents

Now inspect identity-related logon and authentication activity available in Microsoft Defender XDR. Shape it into the same timeline fields.

12345678910111213

let TargetUser = "alex.wilson@contoso.com";
IdentityLogonEvents
| where Timestamp > ago(24h)
| where AccountUpn =~ TargetUser
| project EventTime=Timestamp,
          SourceTable="IdentityLogonEvents",
          User=AccountUpn,
          EventType=ActionType,
          Detail=Protocol,
          IPAddress,
          LogonType,
          AccountDomain
| order by EventTime asc

Different telemetry, same identity

IdentityLogonEvents can surface authentication activity involving on-premises Active Directory and Microsoft online services, depending on available Defender identity telemetry. That can connect cloud authentication with other identity activity.

Coverage depends on your environment

Not every tenant will have every row or every source represented. Microsoft Defender for Identity, Defender for Cloud Apps and other integrations affect what is available. Missing telemetry is a coverage fact, not proof that nothing happened.

Step 4 — add CloudAppEvents

Next shape post-authentication cloud activity into the same generic structure.

12345678910111213141516

let TargetUser = "alex.wilson@contoso.com";
CloudAppEvents
| where Timestamp > ago(24h)
| where AccountId =~ TargetUser
       or AccountDisplayName =~ TargetUser
| project EventTime=Timestamp,
          SourceTable="CloudAppEvents",
          User=coalesce(AccountId, AccountDisplayName),
          EventType=ActionType,
          Detail=Application,
          IPAddress,
          CountryCode,
          City,
          ObjectName,
          ObjectType
| order by EventTime asc

Notice the identity problem again

The same user may not be represented identically across every application. Where possible, prefer stable identifiers such as object IDs, but keep readable UPNs and display names for investigation usability.

Normalize carefully

Do not blindly join display names. Two users can share similar names, accounts can be renamed, and cloud applications may represent identities differently. Use the strongest identifier available for the telemetry you are correlating.

Step 5 — union the evidence into one timeline

Once each dataset has the same column shape, union lets us place the events into one chronological sequence.

12345678910111213141516171819202122232425262728293031323334

let TargetUser = "alex.wilson@contoso.com";
let SignIns =
    SigninLogs
    | where TimeGenerated > ago(24h)
    | where UserPrincipalName =~ TargetUser
    | project EventTime=TimeGenerated,
              SourceTable="SigninLogs",
              User=UserPrincipalName,
              EventType="Entra sign-in",
              Detail=AppDisplayName,
              IPAddress;
let IdentityLogons =
    IdentityLogonEvents
    | where Timestamp > ago(24h)
    | where AccountUpn =~ TargetUser
    | project EventTime=Timestamp,
              SourceTable="IdentityLogonEvents",
              User=AccountUpn,
              EventType=ActionType,
              Detail=Protocol,
              IPAddress;
let CloudActivity =
    CloudAppEvents
    | where Timestamp > ago(24h)
    | where AccountId =~ TargetUser
          or AccountDisplayName =~ TargetUser
    | project EventTime=Timestamp,
              SourceTable="CloudAppEvents",
              User=coalesce(AccountId, AccountDisplayName),
              EventType=ActionType,
              Detail=Application,
              IPAddress;
union isfuzzy=true SignIns, IdentityLogons, CloudActivity
| order by EventTime asc

Why union instead of join?

A join is useful when you want to enrich one row with a matching row from another dataset. A timeline usually needs something different: stack events from several sources and sort them by time. That is where union is often the better tool.

isfuzzy=true helps with optional tables

Using union isfuzzy=true can allow the query to continue when one referenced table is not available in the current workspace. It does not fix schema or permission problems, but it can make reusable investigation queries more tolerant of different environments.

Step 6 — correlate the timeline by source IP

Now summarize the combined timeline by IP address. This can reveal whether one source connects sign-in, identity logon and cloud activity.

123456789101112131415161718192021222324252627282930313233343536373839404142

let TargetUser = "alex.wilson@contoso.com";
let Timeline =
    union isfuzzy=true
        (
            SigninLogs
            | where TimeGenerated > ago(24h)
            | where UserPrincipalName =~ TargetUser
            | project EventTime=TimeGenerated,
                      SourceTable="SigninLogs",
                      EventType="Entra sign-in",
                      Detail=AppDisplayName,
                      IPAddress
        ),
        (
            IdentityLogonEvents
            | where Timestamp > ago(24h)
            | where AccountUpn =~ TargetUser
            | project EventTime=Timestamp,
                      SourceTable="IdentityLogonEvents",
                      EventType=ActionType,
                      Detail=Protocol,
                      IPAddress
        ),
        (
            CloudAppEvents
            | where Timestamp > ago(24h)
            | where AccountId =~ TargetUser
                  or AccountDisplayName =~ TargetUser
            | project EventTime=Timestamp,
                      SourceTable="CloudAppEvents",
                      EventType=ActionType,
                      Detail=Application,
                      IPAddress
        );
Timeline
| summarize EventCount=count(),
            FirstSeen=min(EventTime),
            LastSeen=max(EventTime),
            Sources=make_set(SourceTable, 10),
            EventTypes=make_set(EventType, 25)
          by IPAddress
| order by EventCount desc

One IP across tables is useful — not absolute

Matching IP addresses can provide strong correlation, but network address translation, proxies, mobile networks and service infrastructure can complicate attribution. Use IP as one connector among several.

Time is the second connector

Events from different sources occurring within minutes of one another often provide a stronger narrative than events scattered across hours or days. Build the sequence before deciding what it means.

When should you use join?

Use join when you want to attach identity context to activity — for example, enriching a sign-in with the user's department or critical-asset status from IdentityInfo. Use union when you want several different event streams to appear as one timeline.

Evidence table

Evidence source	What it can add	Important limitation
SigninLogs	Microsoft Entra authentication, IP, application and Conditional Access context.	Does not show everything the user did after authentication.
IdentityInfo	Account context such as department, title, risk and criticality.	Primarily enrichment rather than a complete event timeline.
IdentityLogonEvents	Identity logon/authentication activity represented in Defender XDR.	Coverage depends on connected identity telemetry.
CloudAppEvents	Cloud application actions, objects, network context and behavioural enrichment.	Requires Defender for Cloud Apps and connected activity sources.
Matching IP	A possible infrastructure connection between events.	NAT, proxies and shared networks can reduce attribution certainty.
Matching time and identity	A stronger chronological relationship across telemetry.	Correlation still requires interpretation and validation.

Agent Foskett's investigation

08:51 SigninLogs Successful authentication ↓ IdentityInfo The user is a privileged finance administrator ↓ 09:03 IdentityLogonEvents Additional identity activity appears ↓ 09:11 CloudAppEvents The account begins unusual cloud actions ↓ Each dataset is shaped into the same columns ↓ union stacks the evidence ↓ The timeline is sorted ↓ The same identity appears across all sources ↓ The same source IP connects several events ↓ The investigation is no longer three separate alerts ↓ It is one identity story

The breakthrough was not finding another table. It was realizing that every table was describing a different part of the same person’s activity.

Investigation questions to ask next

Which stable identity identifier is available across the datasets?
Does the same user appear under different UPN, account or display-name fields?
Which table contains authentication versus post-authentication activity?
Does IdentityInfo change the priority of the investigation?
Do the events share an IP address, device, application or time window?
Are any important telemetry sources missing?
Should the data be stacked with union or enriched with join?
Which event came first?
What changed immediately after authentication?
Can every conclusion be traced back to a specific source table?

Lesson 138 key takeaways

Identity investigations should follow the person, not the product boundary.
Different Microsoft tables use different identity column names.
Normalize fields before correlating datasets.
Keep the original source table visible after combining evidence.
IdentityInfo provides valuable account context and criticality.
IdentityLogonEvents can extend identity evidence beyond Entra sign-ins.
CloudAppEvents adds post-authentication cloud behaviour.
Use union to stack event streams into a timeline.
Use join when one dataset should enrich another.
The strongest investigation story emerges when identity, time, infrastructure and activity agree.

Continue your KQL investigation training

Lesson 138 continues Module 11: Identity Threat Hunting. Next we take the correlated evidence and deliberately construct a complete identity compromise timeline.

KQL Academy Lesson 137 — The Sign-In Was Successful but the Session Wasn't Normal Microsoft Entra Security Academy Agent Foskett Academy

Related Agent Foskett Investigations

Continue with investigations where the decisive clue only appeared after evidence from several Microsoft security surfaces was connected.

The Timeline Told the Story The Logs Already Knew The Process Tree Told the Real Story Browse Agent Foskett Investigations

🔎 KQL Academy — Module 11: Identity Threat Hunting

Use KQL to investigate identity behaviour as evidence.

⬅ Previous lesson

Lesson 137 — The Sign-In Was Successful but the Session Wasn't NormalInvestigate post-authentication cloud behaviour when the session stops looking like the user.

✅ Current lesson

Lesson 138 — Following an Identity Across Multiple TablesCorrelate one identity across Entra and Defender XDR telemetry.

Next lesson

Lesson 139 — Building an Identity Compromise TimelineTurn correlated identity evidence into a complete, defensible chronological investigation.

Follow one identity across multiple Microsoft security tables with KQL

Lesson 138 of the Agent Foskett KQL Academy teaches analysts how to correlate one identity across Microsoft Entra SigninLogs and Microsoft Defender XDR IdentityInfo, IdentityLogonEvents and CloudAppEvents.

Multi-table identity correlation in Microsoft Defender XDR and Sentinel

Identity evidence is often distributed across different schemas and products. By normalizing identity fields and event columns, analysts can union multiple telemetry streams into one chronological investigation timeline and preserve each event's original source.