Agent Foskett Academy • Lesson 121 • Advanced KQL Series

Advanced KQL: Mastering Join Operations.

The phishing email was easy to find.

The successful sign-in was easy to find. The PowerShell process was easy to find. The Defender alert was easy to find.

But none of those tables told the whole story on their own.

Agent Foskett needed to correlate email, identity, endpoint, cloud and alert evidence into one investigation timeline. That is where join becomes one of the most important skills in KQL.

Start Lesson 121 Back to Academy

Agent Foskett Academy lesson advanced KQL join operations in Microsoft Defender XDR

Lesson overview

Learn how to use advanced KQL join operations to correlate Microsoft Defender XDR evidence across email, identity, endpoint, cloud and alert telemetry.

Choose the right join type

Correlate evidence across tables

Reduce duplicate and noisy results

Build faster investigation queries

Why join operations matter

Microsoft Defender XDR investigations rarely live in one table. A phishing case may begin in EmailEvents, continue through UrlClickEvents, pivot into IdentityLogonEvents, and end with endpoint evidence in DeviceProcessEvents.

One table rarely tells the whole storyA single table may show the alert, but the investigation usually needs evidence from multiple telemetry sources.

Joins create contextA join lets defenders connect users, devices, alerts, processes, URLs, IP addresses and cloud activity into one investigation view.

Good joins reduce noiseThe right join key, filters and projected columns can make a complex investigation easier to understand and much faster to run.

Join types defenders should understand

Different join types answer different investigation questions. Choosing the right one matters.

inner joinReturns matching rows from both sides. Useful when you only want evidence that appears in both datasets.

leftouter joinKeeps all rows from the left side and adds matching evidence when it exists. Useful for preserving a primary investigation list.

leftanti joinReturns rows from the left side that do not have a match. Useful for finding missing evidence or unmatched activity.

leftsemi joinReturns left-side rows that have a match, without bringing back all right-side columns. Useful for efficient filtering.

fullouter joinReturns matched and unmatched rows from both sides. Useful for comparison work, but can create noisy results.

Join key selectionThe join key is critical. AlertId, AccountUpn, DeviceName, NetworkMessageId, RemoteIP and SHA256 are common investigation keys.

Step 1 — Join AlertInfo with AlertEvidence

This is one of the most useful Defender XDR joins. AlertInfo gives the alert context. AlertEvidence gives the entities involved.

let TimeFrame = 7d;
AlertInfo
| where Timestamp > ago(TimeFrame)
| project AlertId, AlertTime = Timestamp, Title, Severity, Category
| join kind=inner (
    AlertEvidence
    | where Timestamp > ago(TimeFrame)
    | project AlertId, EvidenceTime = Timestamp, EntityType, EvidenceRole,
              AccountName, DeviceName, RemoteIP, FileName, SHA256
) on AlertId
| project AlertTime, Title, Severity, Category, EntityType, EvidenceRole,
          AccountName, DeviceName, RemoteIP, FileName, SHA256
| order by AlertTime desc

Step 2 — Join email delivery with URL clicks

In phishing investigations, NetworkMessageId can connect the delivered email to later click activity.

let TimeFrame = 7d;
EmailEvents
| where Timestamp > ago(TimeFrame)
| where Subject has_any ("invoice", "payment", "password", "urgent")
| project EmailTime = Timestamp, NetworkMessageId, RecipientEmailAddress,
          SenderFromAddress, Subject, DeliveryAction
| join kind=leftouter (
    UrlClickEvents
    | where Timestamp > ago(TimeFrame)
    | project ClickTime = Timestamp, NetworkMessageId, AccountUpn, Url, ActionType
) on NetworkMessageId
| project EmailTime, ClickTime, RecipientEmailAddress, AccountUpn,
          SenderFromAddress, Subject, DeliveryAction, Url, ActionType
| order by EmailTime desc

Step 3 — Join identity activity with endpoint logons

Identity and device telemetry often need to be correlated to understand where a user authenticated and which endpoints were involved.

let TimeFrame = 7d;
IdentityLogonEvents
| where Timestamp > ago(TimeFrame)
| project IdentityTime = Timestamp, AccountUpn, IPAddress, Location, ActionType
| join kind=leftouter (
    DeviceLogonEvents
    | where Timestamp > ago(TimeFrame)
    | project DeviceTime = Timestamp, AccountUpn, DeviceName, LogonType, RemoteIP
) on AccountUpn
| where abs(datetime_diff("minute", IdentityTime, DeviceTime)) <= 60
| project IdentityTime, DeviceTime, AccountUpn, DeviceName, IPAddress,
          RemoteIP, Location, ActionType, LogonType
| order by IdentityTime desc

Step 4 — Join process activity with network connections

This pattern helps defenders determine whether a suspicious process also made external network connections.

let TimeFrame = 24h;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "regsvr32.exe", "mshta.exe")
| project ProcessTime = Timestamp, DeviceName, InitiatingProcessId,
          FileName, ProcessCommandLine, AccountName
| join kind=leftouter (
    DeviceNetworkEvents
    | where Timestamp > ago(TimeFrame)
    | project NetworkTime = Timestamp, DeviceName, InitiatingProcessId,
              RemoteUrl, RemoteIP, RemotePort
) on DeviceName, InitiatingProcessId
| project ProcessTime, NetworkTime, DeviceName, AccountName, FileName,
          ProcessCommandLine, RemoteUrl, RemoteIP, RemotePort
| order by ProcessTime desc

Step 5 — Use leftanti to find missing matches

A leftanti join is useful when you want to find records that did not appear in another dataset.

let TimeFrame = 7d;
let RiskyUsers =
    IdentityLogonEvents
    | where Timestamp > ago(TimeFrame)
    | where ActionType has_any ("risk", "suspicious", "unfamiliar")
    | distinct AccountUpn;
RiskyUsers
| join kind=leftanti (
    AlertEvidence
    | where Timestamp > ago(TimeFrame)
    | where isnotempty(AccountName)
    | distinct AccountName
) on $left.AccountUpn == $right.AccountName
| project AccountUpn

Step 6 — Optimise joins before scaling them

Advanced joins become expensive when analysts join large, unfiltered datasets. Filter early and project only what you need.

let TimeFrame = 3d;
let SuspiciousProcesses =
    DeviceProcessEvents
    | where Timestamp > ago(TimeFrame)
    | where FileName in~ ("powershell.exe", "rundll32.exe", "regsvr32.exe")
    | project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine;
let AlertedDevices =
    AlertEvidence
    | where Timestamp > ago(TimeFrame)
    | where EntityType =~ "Machine"
    | where isnotempty(DeviceName)
    | distinct DeviceName;
SuspiciousProcesses
| join kind=inner AlertedDevices on DeviceName
| order by Timestamp desc

Common join mistakes

Most bad joins fail because they are too broad, use the wrong key, or return more rows than expected.

Joining before filteringAlways reduce the dataset first. Joining full tables can be slow, noisy and expensive.

Wrong join keyAccountName, AccountUpn, RecipientEmailAddress and AccountObjectId are not always interchangeable.

Duplicate rowsOne-to-many joins can multiply results. Use summarize, distinct or arg_max() when needed.

Time windows too wideLarge time ranges can create unrelated matches. Use focused time windows around the investigation.

Too many columnsProject only the fields needed for the next investigation step before joining.

Unvalidated matchesA joined result is not automatically meaningful. Always confirm the relationship makes investigative sense.

Join performance checklist

Use this checklist before running join-heavy hunting queries across large environments.

Filter firstApply Timestamp, table-specific filters and suspicious indicators before the join.

Project earlyKeep only the columns needed for correlation, triage and evidence review.

Use let statementsBuild small reusable datasets that are easier to test and understand.

Pick the right join typeUse inner for matched evidence, leftouter for enrichment, leftanti for missing matches and leftsemi for filtering.

Validate row countsCheck whether the join multiplied rows unexpectedly before trusting the result.

Summarise when usefulUse summarize, distinct, make_set() or arg_max() to reduce noisy intermediate data.

Related Agent Foskett Academy lessons

These lessons support advanced join-based investigations.

Connecting Tables with joinReview the original join lesson before moving into advanced join patterns.

Building Reusable Hunting QueriesUse let statements and modular query patterns to keep complex joins readable.

Full Microsoft Defender XDR InvestigationSee how join logic supports complete incident response workflows.

Investigating EmailEventsUse email telemetry as one side of phishing and delivery joins.

Investigating IdentityLogonEventsCorrelate identity activity with endpoint, alert and cloud evidence.

Investigating AlertInfoConnect alerts with evidence entities using AlertId joins.

Coming next

The Advanced KQL series continues with dynamic data.

Lesson 122 — Advanced KQL: Working with Dynamic DataNext, Agent Foskett explores arrays, property bags, JSON-style fields and dynamic values commonly found in Microsoft Defender XDR telemetry.

Why this mattersMany Defender XDR fields contain nested values. Learning dynamic data handling helps analysts parse richer evidence from complex security logs.

Final thought

Advanced joins are not just a KQL feature. They are how defenders connect evidence.

Agent Foskett mindsetDo not stop at the table that generated the alert. Ask which other tables can confirm, enrich or disprove the activity.

Advanced KQL SeriesLesson 121 begins a new Academy chapter focused on writing stronger, faster and more reliable investigation queries.

Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Advanced KQL Mastering Join Operations in Microsoft Defender XDR

Agent Foskett Academy Lesson 121 teaches defenders how to use advanced KQL join operations to correlate Microsoft Defender XDR telemetry across email, identity, endpoint, alert, network and cloud security tables.

Learn KQL joins for Microsoft Defender XDR hunting

This lesson explains inner joins, leftouter joins, leftanti joins, leftsemi joins, join keys, performance optimisation and practical multi-table investigation workflows.

Microsoft Defender XDR advanced hunting join tutorial

Security analysts can use KQL joins to connect AlertInfo, AlertEvidence, EmailEvents, UrlClickEvents, IdentityLogonEvents, DeviceProcessEvents, DeviceNetworkEvents and CloudAppEvents.