Agent Foskett Academy • Lesson 14 • Connecting Tables with join in KQL
Connecting Tables with join in KQL
One table rarely tells the whole story.
EmailEvents may show the message arrived.
UrlClickEvents may show the user clicked.
Endpoint telemetry may show what happened after the click.
The investigation becomes stronger when those signals are connected.
In this Agent Foskett Academy lesson, you will learn how defenders use join in KQL to connect Microsoft Defender XDR tables, follow evidence across telemetry and build a clearer investigation timeline.
Lesson overview
Learn how to use KQL join to connect email delivery, URL click and endpoint evidence during Microsoft Defender XDR investigations.
Connect related telemetry
Use shared fields as evidence pivots
Build stronger investigation timelines
Why join matters
In Microsoft Defender XDR, different tables record different parts of the story.
EmailEvents can help you understand message delivery. UrlClickEvents can help you understand link clicks. DeviceProcessEvents and DeviceNetworkEvents can help you understand what happened on the endpoint.
The join operator allows defenders to connect rows from separate tables when they share a useful field, such as a message identifier, user account, device name, URL, IP address or timestamp window.
EmailEvents can help you understand message delivery. UrlClickEvents can help you understand link clicks. DeviceProcessEvents and DeviceNetworkEvents can help you understand what happened on the endpoint.
The join operator allows defenders to connect rows from separate tables when they share a useful field, such as a message identifier, user account, device name, URL, IP address or timestamp window.
Connect the storyBring related records together so the investigation is not trapped inside one table.
Pivot with purposeUse common fields like NetworkMessageId, AccountUpn, DeviceName or Url to move between evidence sources.
Reduce guessworkBuild clearer timelines by showing delivery, click and activity evidence side by side.
Investigation scenario
A suspicious email was delivered to several users.
One user clicked the link. Another user may have clicked later.
The analyst needs to connect the delivered email records with the URL click records so they can see who clicked, what they clicked and which email the click came from.
One user clicked the link. Another user may have clicked later.
The analyst needs to connect the delivered email records with the URL click records so they can see who clicked, what they clicked and which email the click came from.
Step 1 — Create a small email evidence set
Before joining tables, narrow the first table down to the evidence you actually need.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
let suspiciousEmail = EmailEvents | where Timestamp > ago(7d) | where Subject has "invoice" or SenderFromDomain has "example.com" | project EmailTime = Timestamp, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction; suspiciousEmail
Step 2 — Join email delivery to URL clicks
Now connect the email evidence to UrlClickEvents using the shared NetworkMessageId field.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
let suspiciousEmail = EmailEvents | where Timestamp > ago(7d) | where Subject has "invoice" or SenderFromDomain has "example.com" | project EmailTime = Timestamp, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction; UrlClickEvents | where Timestamp > ago(7d) | join kind=inner suspiciousEmail on NetworkMessageId | project ClickTime = Timestamp, EmailTime, RecipientEmailAddress, SenderFromAddress, Subject, Url, ActionType, ThreatTypes | sort by ClickTime desc
What kind=inner means
An inner join only returns records that match on both sides.
In this scenario, the result shows URL clicks that can be matched back to the suspicious email evidence set.
That makes it useful when you want to answer a direct question: Which delivered suspicious emails had matching click activity?
In this scenario, the result shows URL clicks that can be matched back to the suspicious email evidence set.
That makes it useful when you want to answer a direct question: Which delivered suspicious emails had matching click activity?
Step 3 — Use leftouter when you want to keep all emails
Sometimes you want to keep every suspicious email in the result, even if no click was found.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
let suspiciousEmail = EmailEvents | where Timestamp > ago(7d) | where Subject has "invoice" or SenderFromDomain has "example.com" | project EmailTime = Timestamp, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction; suspiciousEmail | join kind=leftouter ( UrlClickEvents | where Timestamp > ago(7d) | project ClickTime = Timestamp, NetworkMessageId, Url, ActionType, ThreatTypes ) on NetworkMessageId | project EmailTime, ClickTime, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction, Url, ActionType, ThreatTypes | sort by EmailTime desc
Inner join vs leftouter join
innerOnly shows matching records from both tables. Great when you only want confirmed relationships.
leftouterKeeps all records from the left table and adds matches from the right table where available.
Start smallFilter and project before joining so your query stays readable, focused and faster to investigate.
Step 4 — Summarize affected users after the join
Once the tables are connected, summarize the result to understand impact.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
let suspiciousEmail = EmailEvents | where Timestamp > ago(7d) | where Subject has "invoice" or SenderFromDomain has "example.com" | project EmailTime = Timestamp, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, Subject; UrlClickEvents | where Timestamp > ago(7d) | join kind=inner suspiciousEmail on NetworkMessageId | summarize Clicks = count(), UniqueUrls = dcount(Url), FirstClick = min(Timestamp), LastClick = max(Timestamp) by RecipientEmailAddress, Subject | sort by Clicks desc
Investigator notes
Joining tables is powerful, but it works best when the investigation question is clear.
Do not join everything just because you can. Start with the question, choose the fields that connect the evidence, reduce each table to the columns you need, then join the results.
Do not join everything just because you can. Start with the question, choose the fields that connect the evidence, reduce each table to the columns you need, then join the results.
Ask the question firstAre you proving a click, finding affected users or building a timeline?
Choose the right keyUse the strongest shared field available, such as NetworkMessageId for email-to-click investigations.
Project before joiningKeep only useful columns so the output is easier to read and explain.
What you learned
In this lesson, you learned how to connect Microsoft Defender XDR tables with KQL join.
Using joinJoin connects related records from different tables when they share a useful field.
Choosing join typesinner is useful for confirmed matches. leftouter is useful when you want to keep all records from the left table.
Building timelinesJoined results can help show delivery, click and follow-on evidence in one investigation view.
Continue your investigation
The next step is learning how to turn connected evidence into a timeline so defenders can explain what happened in the right order.
Agent Foskett Academy
Return to the full Academy learning path and review earlier KQL foundation lessons.
The Link Was Clicked After The Email Was Delivered
Continue into a real-world Agent Foskett investigation about email delivery, URL clicks and post-delivery evidence.
Continue learning with UrlClickEvents, EmailEvents KQL Guide, KQL Threat Hunting Guide, Microsoft Security and the GEMXIT Security Review.
Develop IT. Protect IT. GEMXIT PTY LTD | GEMXIT UK LTD