Correlating events in KQL: join, let, and time windows for multi-step detections

Why correlation matters

Single events are often harmless: a new inbox rule, a sign-in from a new country, a new OAuth consent. A sequence is more interesting: a risky sign-in followed within an hour by a new inbox rule that forwards mail externally. Correlation turns weak signals into a stronger detection.

join kinds and let

join kind=inner keeps rows that match on both sides. leftouter keeps all left rows. leftanti keeps left rows with no match, which is useful for “never seen before” detections. Use let to define each side, filter and project them before joining, and then compare timestamps after the join.

Snippet

let window = 1h;
let risky = SigninLogs
  | where TimeGenerated > ago(1d) and RiskLevelDuringSignIn in ("medium", "high")
  | project SigninTime = TimeGenerated, UserPrincipalName, IPAddress;
let rules = OfficeActivity
  | where TimeGenerated > ago(1d) and Operation in ("New-InboxRule", "Set-InboxRule")
  | project RuleTime = TimeGenerated, UserPrincipalName = UserId, Parameters;
risky
| join kind=inner rules on UserPrincipalName
| where RuleTime between (SigninTime .. SigninTime + window)
| project UserPrincipalName, IPAddress, SigninTime, RuleTime, Parameters

Lab: a first-seen detection

Build a leftanti join that finds processes run today on any device that were never seen in the previous 14 days. This is a common hunting query for rare binaries. Run it in a lab or demo workspace, then review how many results appear and which are legitimate software updates. Add an allow list with let to suppress known updaters.

Snippet

let baseline = DeviceProcessEvents
  | where Timestamp between (ago(15d) .. ago(1d))
  | distinct SHA256;
DeviceProcessEvents
| where Timestamp > ago(1d)
| join kind=leftanti baseline on SHA256
| summarize Devices = dcount(DeviceName), Example = any(FolderPath) by FileName, SHA256
| order by Devices asc

Performance tips

Put the smaller table on the left side of a join, filter both sides by time before joining, and project only the join key and needed columns. For very large time ranges, summarize first and join the summaries instead of raw rows.

Diagram

Join kinds for detections

Choose by the question you are asking.

inner
A and B both happened for the same entity.
leftouter
Show every A, with B if it exists.
leftanti
A happened and B never did: first seen, missing control.
time window filter
After joining, keep pairs where B followed A within N minutes.

Check yourself

1. Which join kind finds rows in the left table that have no match in the right table?

2. Which statement names a subquery or constant for reuse later in the query?

3. How do you keep only pairs where the second event followed the first within an hour?