ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 91 • Advanced KQL

Advanced join Techniques in KQL.

One table showed the process.

Another showed the connection.

Separately, neither proved enough.

Together, they revealed the attack.

Start Lesson 91 Back to Academy
Agent Foskett Academy lesson advanced join techniques in KQL for Microsoft Defender XDR
Lesson overview

Learn how defenders use advanced join techniques in KQL to correlate Microsoft Defender XDR telemetry across process, network, file, logon and email tables.

Choose the right join type
Reduce data before joining
Correlate process, network and file evidence
Build stronger threat hunting queries

Why advanced joins matter

In Microsoft Defender XDR, important evidence is often split across different tables. A process may appear in DeviceProcessEvents, the connection in DeviceNetworkEvents and the downloaded file in DeviceFileEvents. Advanced joins help defenders put that evidence back together.
One table rarely tells the full storyDevice, identity, email and network evidence often need to be correlated before the investigation makes sense.
The join key mattersChoosing DeviceId, AccountUpn, NetworkMessageId, ReportId or a timestamp window changes what evidence is connected.
Performance mattersLarge Defender tables can be expensive to join. Filtering, projecting and summarising first keeps hunts faster and cleaner.

The advanced join workflow

Agent Foskett does not join tables randomly. He starts with a question, chooses the evidence sources, reduces the data and then joins on the field that best proves the relationship.
1. Start with the investigation questionAsk what relationship you are trying to prove before choosing a join type.
2. Filter both sides firstReduce each table to the relevant timeframe, devices, accounts, processes or indicators before joining.
3. Choose the join keyUse stable fields where possible, such as DeviceId, SHA256, NetworkMessageId, AccountUpn or a clearly bounded timestamp.
4. Select the join typeUse inner, leftouter, anti joins or other join types depending on what you need to prove or exclude.
5. Project clean evidenceKeep only the columns that explain the investigation. Too many columns make the result harder to read.
6. Validate the relationshipCheck timestamps, devices and accounts to make sure the joined events truly belong together.

Step 1 — Join process activity to network activity

Use an inner join to connect suspicious process execution with outbound network connections from the same device and account.
step-1-process-network-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
let SuspiciousProcesses =
    DeviceProcessEvents
    | where Timestamp > ago(24h)
    | where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
    | project ProcessTime=Timestamp, DeviceId, DeviceName, AccountUpn, FileName, ProcessCommandLine;
SuspiciousProcesses
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(24h)
    | project NetworkTime=Timestamp, DeviceId, DeviceName, AccountUpn, RemoteUrl, RemoteIP, InitiatingProcessFileName
) on DeviceId, AccountUpn
| where NetworkTime between (ProcessTime .. ProcessTime + 10m)
| project ProcessTime, NetworkTime, DeviceName, AccountUpn, FileName, ProcessCommandLine, RemoteUrl, RemoteIP
| order by ProcessTime asc

Step 2 — Use leftouter to keep unmatched evidence

A leftouter join keeps the suspicious process even when no matching network event is found. This is useful when you want to review both matched and unmatched activity.
step-2-leftouter-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName =~ "powershell.exe"
| project ProcessTime=Timestamp, DeviceId, DeviceName, AccountUpn, ProcessCommandLine
| join kind=leftouter (
    DeviceNetworkEvents
    | where Timestamp > ago(24h)
    | project NetworkTime=Timestamp, DeviceId, RemoteUrl, RemoteIP
) on DeviceId
| where isnull(NetworkTime) or NetworkTime between (ProcessTime .. ProcessTime + 5m)
| project ProcessTime, NetworkTime, DeviceName, AccountUpn, ProcessCommandLine, RemoteUrl, RemoteIP
| order by ProcessTime asc

Step 3 — Use anti join to find missing evidence

An anti join helps identify records that do not have a match. In investigations, this can reveal devices, accounts or indicators that did not behave as expected.
step-3-anti-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
let DevicesWithPowerShell =
    DeviceProcessEvents
    | where Timestamp > ago(7d)
    | where FileName =~ "powershell.exe"
    | distinct DeviceId, DeviceName;
DevicesWithPowerShell
| join kind=leftanti (
    DeviceNetworkEvents
    | where Timestamp > ago(7d)
    | distinct DeviceId
) on DeviceId
| project DeviceName, DeviceId

Step 4 — Join file hashes across tables

Use SHA256 to correlate files written to disk with later process execution.
step-4-file-process-hash-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
DeviceFileEvents
| where Timestamp > ago(7d)
| where ActionType in ("FileCreated", "FileModified")
| where isnotempty(SHA256)
| project FileTime=Timestamp, DeviceId, DeviceName, FileName, FolderPath, SHA256
| join kind=inner (
    DeviceProcessEvents
    | where Timestamp > ago(7d)
    | where isnotempty(SHA256)
    | project ProcessTime=Timestamp, DeviceId, DeviceName, ProcessName=FileName, SHA256, ProcessCommandLine
) on DeviceId, SHA256
| where ProcessTime >= FileTime
| project FileTime, ProcessTime, DeviceName, FileName, FolderPath, ProcessName, SHA256, ProcessCommandLine
| order by ProcessTime asc

Step 5 — Join email delivery to URL clicks

NetworkMessageId is a strong join key when connecting delivered email evidence with user click activity.
step-5-email-url-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
EmailEvents
| where Timestamp > ago(7d)
| project EmailTime=Timestamp, NetworkMessageId, SenderFromAddress, RecipientEmailAddress, Subject, DeliveryAction
| join kind=inner (
    UrlClickEvents
    | where Timestamp > ago(7d)
    | project ClickTime=Timestamp, NetworkMessageId, AccountUpn, Url, ActionType, IsClickedThrough
) on NetworkMessageId
| project EmailTime, ClickTime, SenderFromAddress, RecipientEmailAddress, Subject, Url, ActionType, IsClickedThrough
| order by ClickTime asc

Step 6 — Join identity activity to device activity

Join identity logons to endpoint activity when you need to understand what happened after a user authenticated.
step-6-identity-device-join.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
IdentityLogonEvents
| where Timestamp > ago(24h)
| where ActionType == "LogonSuccess"
| project LogonTime=Timestamp, AccountUpn, IPAddress, Application, DeviceName
| join kind=inner (
    DeviceProcessEvents
    | where Timestamp > ago(24h)
    | project ProcessTime=Timestamp, AccountUpn, DeviceName, FileName, ProcessCommandLine
) on AccountUpn
| where ProcessTime between (LogonTime .. LogonTime + 30m)
| project LogonTime, ProcessTime, AccountUpn, DeviceName, IPAddress, Application, FileName, ProcessCommandLine
| order by LogonTime asc

How to choose the right join type

The join type should match the investigation question.
innerUse when you only want records that appear on both sides of the join.
leftouterUse when you want to keep the left-side evidence even if a match is missing.
leftantiUse when you need to find records on the left that do not appear on the right.
inneruniqueUse carefully when you want unique left-side matches. Validate results so important duplicate evidence is not hidden.

Real-world investigation

The first clueDeviceProcessEvents shows PowerShell executing an encoded command on a workstation.
The second clueDeviceNetworkEvents shows the same device contacting an unfamiliar domain moments later.
The joined evidenceA join connects the encoded PowerShell command to the outbound connection within the same five-minute window.
The conclusionThe query proves that the suspicious process and network activity were part of the same investigation story.

Investigation checklist

Question definedKnow what relationship the join is supposed to prove.
Data reduced firstFilter by time, account, device, process or indicator before joining large tables.
Join key selectedChoose fields that reliably connect the evidence.
Join type chosenUse inner, leftouter or anti joins based on whether you need matches, missing matches or both.
Timestamps validatedConfirm the joined events occurred close enough together to support the investigation theory.
Output simplifiedProject only the columns that help explain what happened.

Related Agent Foskett Academy lessons

Connecting Tables with joinReview the fundamentals of joining tables in KQL.
Investigating DeviceProcessEventsUnderstand process execution evidence before correlating it with other activity.
Investigating DeviceNetworkEventsUse network telemetry to identify outbound connections and remote infrastructure.
Building an IOC HuntSearch indicators across multiple Microsoft Defender XDR tables.
Building an Incident TimelinePlace joined evidence into a complete investigation timeline.
Investigating PowerShell AttacksUse process and network joins to investigate suspicious PowerShell activity.

Advanced KQL milestone

Lesson 91 begins a new advanced KQL block inside the Agent Foskett Academy.
Back to advanced KQLAfter 90 lessons, the Academy now returns to deeper KQL techniques that make real investigations stronger.
Investigation-driven KQLThe goal is not just syntax. The goal is to combine evidence in a way that proves what happened.
Agent Foskett continuesThe next lessons build reusable hunting queries, cleaner logic and stronger Defender XDR investigations.

Coming next

Lesson 92 — Using let Statements to Build Reusable Hunting QueriesNext, Agent Foskett Academy explains how defenders use let statements to make complex KQL easier to read, reuse and maintain.
Why this mattersAdvanced joins connect evidence. let statements make that evidence easier to structure, explain and reuse.

Final thought

A join is not just a KQL operator. It is how separate clues become a case.
Agent Foskett mindsetDo not join tables because you can. Join them because the investigation needs the relationship proven.
The evidence connectedWhen process, network, file, email and identity data are joined correctly, the investigation becomes easier to defend.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Advanced join Techniques in KQL

Agent Foskett Academy Lesson 91 teaches defenders how to use advanced join techniques in KQL for Microsoft Defender XDR investigations.

KQL joins for Microsoft Defender XDR hunting

This lesson explains how inner joins, leftouter joins, anti joins, join keys, timestamp windows, DeviceProcessEvents, DeviceNetworkEvents, DeviceFileEvents, EmailEvents, UrlClickEvents and IdentityLogonEvents help defenders correlate evidence during threat hunting and incident response.

ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026