ACSC Logo
© GEMXIT
GEMXIT Logo
Agent Foskett Investigation • KQL • Microsoft Defender XDR • Threat Hunting

The KQL join Revealed The Attacker

The email looked suspicious.

The URL click appeared in another table.

The endpoint connection looked ordinary.

The process had a familiar Windows name.

Every signal appeared to describe a different event.

Then one KQL operator connected the entire attack.

Discuss threat hunting Back to investigations
Agent Foskett using KQL joins to connect email, URL click, endpoint network and process evidence in Microsoft Defender XDR
Cross-table investigation

The clue was not hidden in one table. It was hidden in the relationship between them.

Connect email delivery to URL clicks
Correlate identity, device and time
Reconstruct the complete attack path

Four tables. Four partial stories.

Each Microsoft Defender XDR table contained useful evidence, but none of them explained the incident alone.
EmailEventsShowed the sender, recipient, subject, delivery action and NetworkMessageId for the suspicious message.
UrlClickEventsRecorded which user clicked the URL, when the click occurred and whether Microsoft allowed or blocked it.
DeviceNetworkEventsRevealed the device, remote host, initiating process and network connection that followed.
DeviceProcessEventsExposed the process name, command line, parent process and account behind the network activity.

The first join connected the email to the click

NetworkMessageId exists in both email and URL-click telemetry, making it the strongest correlation key for this stage of the investigation.
email-to-click.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
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
let Clicks =
    UrlClickEvents
    | where Timestamp > ago(7d)
    | project
        ClickTime = Timestamp,
        AccountUpn,
        NetworkMessageId,
        Url,
        ActionType;
EmailEvents
| where Timestamp > ago(7d)
| project
    EmailTime = Timestamp,
    NetworkMessageId,
    SenderFromAddress,
    RecipientEmailAddress,
    Subject,
    DeliveryAction
| join kind=inner Clicks on NetworkMessageId
| project EmailTime, ClickTime, RecipientEmailAddress,
          SenderFromAddress, Subject, Url, ActionType,
          DeliveryAction, AccountUpn
| sort by ClickTime asc

What the first result proved

The suspicious message was no longer only an email-security event. A specific identity had interacted with it.
The message was deliveredEmailEvents confirmed that the suspicious email reached the organisation and identified its recipient.
The URL was clickedUrlClickEvents proved the user interacted with a URL associated with the same NetworkMessageId.
The timeline had a pivotThe click time and AccountUpn created the next investigation pivots into endpoint and identity telemetry.

The second join followed the user onto the endpoint

The next query correlates the clicked host with endpoint network activity for the same account during a controlled thirty-minute window.
click-to-network.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
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
let Clicks =
    UrlClickEvents
    | where Timestamp > ago(7d)
    | extend ClickHost = tostring(parse_url(Url).Host)
    | project ClickTime = Timestamp, AccountUpn,
              NetworkMessageId, Url, ClickHost;
DeviceNetworkEvents
| where Timestamp > ago(7d)
| extend AccountUpn = tostring(InitiatingProcessAccountUpn)
| extend RemoteHost = tostring(parse_url(RemoteUrl).Host)
| join kind=inner Clicks on AccountUpn
| where Timestamp between (ClickTime .. ClickTime + 30m)
| where RemoteHost == ClickHost
| project
    ClickTime,
    NetworkTime = Timestamp,
    AccountUpn,
    DeviceName,
    DeviceId,
    RemoteUrl,
    RemoteIP,
    InitiatingProcessFileName,
    InitiatingProcessId,
    InitiatingProcessCommandLine
| sort by NetworkTime asc

The final join identified the process

DeviceId and process identifiers connect the outbound network event with the process telemetry that explains how the connection was created.
network-to-process.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
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
let NetworkActivity =
    DeviceNetworkEvents
    | where Timestamp > ago(7d)
    | where RemoteUrl has "suspicious-example.com"
    | project
        NetworkTime = Timestamp,
        DeviceId,
        DeviceName,
        RemoteUrl,
        RemoteIP,
        InitiatingProcessId;
NetworkActivity
| join kind=inner (
    DeviceProcessEvents
    | where Timestamp > ago(7d)
    | project
        ProcessTime = Timestamp,
        DeviceId,
        ProcessId,
        FileName,
        ProcessCommandLine,
        InitiatingProcessFileName,
        InitiatingProcessCommandLine,
        AccountUpn
) on DeviceId, $left.InitiatingProcessId == $right.ProcessId
| where ProcessTime between (NetworkTime - 5m .. NetworkTime + 5m)
| project NetworkTime, ProcessTime, DeviceName, AccountUpn,
          FileName, ProcessCommandLine, InitiatingProcessFileName,
          InitiatingProcessCommandLine, RemoteUrl, RemoteIP

The process tree changed the verdict

The remote connection looked like browser traffic until the joined process evidence revealed the execution chain behind it.
joined-investigation-result.txt
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
08:12  Suspicious email delivered
08:17  User clicked embedded URL
08:18  Browser connected to suspicious host
08:19  Browser spawned powershell.exe
08:19  PowerShell downloaded payload.ps1
08:20  rundll32.exe executed downloaded content
08:22  Outbound connection opened to new IP
08:24  Scheduled task created for persistence

Choosing the right join kind

The join type determines which rows survive the correlation. Choosing the wrong type can hide evidence or multiply results.
innerReturns rows with matching keys on both sides. Use it when the investigation requires confirmed correlation.
leftouterKeeps every row from the left side, even when no matching evidence exists on the right. Useful for finding missing telemetry.
inneruniqueDeduplicates the left-side key before matching. It can reduce duplicate correlations when only one left-side event is needed.

Why joins go wrong

Most failed joins are caused by weak keys, mismatched data types or unconstrained time ranges—not by the operator itself.
Joining only on a usernameA busy user may generate thousands of events. Add a time window, device, message ID, URL host or another strong key.
Ignoring field typesConvert dynamic or numeric fields with tostring(), toint() or another appropriate function before joining.
Joining entire tables firstFilter and project before the join. Smaller datasets are easier to understand and generally perform better.
Forgetting duplicate matchesOne left-side row can match several right-side rows. Confirm whether the multiplication represents evidence or noise.
Using a huge time rangeA seven-day or thirty-day join may connect unrelated events. Restrict activity around the known click, sign-in or alert time.
Trusting names instead of IDsUse stable identifiers such as NetworkMessageId, DeviceId, ReportId or process IDs whenever the relevant tables provide them.

A safer investigation pattern

Build and validate each dataset independently before joining them.
1. Define the time rangeStart near the known event. Expand only when the evidence justifies a wider window.
2. Filter each tableReduce each side to relevant messages, users, devices, URLs, processes or alerts.
3. Project only needed fieldsRename timestamps and overlapping columns before joining so the final output remains readable.
4. Join on strong keysPrefer identifiers that directly represent the same message, device, process, account or session.
5. Validate every matchInspect timestamps, identities and surrounding telemetry before treating correlation as causation.
6. Build the timelineSort chronologically and explain how the separate events form one defensible investigation narrative.

Agent Foskett's investigation mindset

The operator does not solve the case. It exposes relationships the investigator must still validate.
Do not ask only: What happened in this table?Single-table queries often describe one stage of a much larger attack.
Ask: Which identifiers travel with the evidence?Message IDs, account names, device IDs, URLs, process IDs and timestamps create reliable pivots.
Ask: Does the joined timeline make sense?A technical match becomes meaningful evidence only when the identity, device, sequence and behaviour agree.

Investigation findings

The incident was visible all along, but its evidence was distributed across Microsoft Defender XDR.
The email started the chainEmail telemetry identified the message, recipient and delivery context.
The click created the pivotURL-click telemetry connected the message to a user and a precise point in time.
The endpoint revealed executionNetwork and process telemetry exposed the command chain, download and persistence activity.

Related Agent Foskett investigations

Continue with KQL, process-tree, URL-click and Microsoft Defender XDR investigations.
Microsoft Defender KQL Threat Hunting GuideStart with practical KQL queries for email, identity, endpoint and cloud investigations.
The Process Tree Told The Real StoryFollow parent-child process relationships when the alert names only one executable.
The Link Was Clicked After The Email Was DeliveredUse UrlClickEvents and email telemetry to investigate post-delivery user interaction.
Nothing Flagged The PowerShell DownloadInvestigate trusted tooling through command lines, network connections and file activity.
The Command Line Told A Different StoryLearn why process names are only the beginning of an endpoint investigation.
Agent Foskett AcademyBuild Microsoft Defender XDR and KQL skills through structured, practical lessons.
Investigations do not happen inside one table.
Learn to connect the email, identity, endpoint and cloud evidence.
Visit the Agent Foskett Academy

Final thought

Every table held a clue. The join revealed that they belonged to the same attacker.
The email was the beginningDelivery telemetry identified the first observable event in the attack chain.
The join connected the evidenceShared identifiers and constrained time windows transformed separate events into one investigation.
The investigator proved the storyKQL revealed the relationship. Human validation established what the relationship meant.
Develop IT. Protect IT.
GEMXIT PTY LTD | GEMXIT UK LTD
Talk to GEMXIT
ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT

The KQL Join Revealed The Attacker

This Agent Foskett investigation explains how the KQL join operator can connect evidence across EmailEvents, UrlClickEvents, DeviceNetworkEvents and DeviceProcessEvents in Microsoft Defender XDR. The investigation follows a suspicious email through URL interaction, endpoint network activity, process execution and persistence.

KQL Join Examples For Microsoft Defender XDR

The page provides practical KQL join examples using NetworkMessageId, AccountUpn, DeviceId and process identifiers. It explains inner, leftouter and innerunique joins, time-window correlation, field projection, data-type conversion and methods for reducing duplicate or unrelated matches.

Cross-Table Threat Hunting With KQL

GEMXIT helps organisations investigate Microsoft Defender XDR telemetry across email, identity, endpoint and cloud activity. Agent Foskett threat hunting lessons teach defenders to connect tables, validate timelines and follow behaviour beyond individual alerts.