6.3 Build Multi-Table Statements Using KQL

Build Multi-Table Statements Using KQL

Introduction

Until now, every query you have written targets a single table. But real investigations do not live in one table. A phishing attack spans email data (EmailEvents), sign-in data (SigninLogs), endpoint data (DeviceProcessEvents), and cloud app data (CloudAppEvents). To trace the full attack chain — from phishing email to credential theft to mailbox access to lateral movement — you must query across tables and connect the results.

This subsection teaches the two operators that make cross-table analysis possible: union and join. These are the operators that transform KQL from a log viewer into an investigation engine. The queries in Modules 11-15 (the real-world investigation modules) depend heavily on what you learn here.

Here is what this subsection covers:

• union — combining rows from multiple tables into a single result set, useful when the same type of data exists in multiple tables
• join — matching rows between two tables on a shared key, used to correlate different types of events that share a common entity (user, IP, device)
• Join types — how inner, leftouter, leftanti, and other join kinds control which rows appear in the result, and which join type to use for each investigation scenario
• Investigation pattern: email-to-signin correlation — the cross-table query that connects a phishing email to the resulting credential compromise, used in Module 11

Each operator is taught with an investigation scenario that demonstrates why you need it.

The union operator — combining tables vertically

What it is

union takes two or more tables and stacks their rows together into a single result set. Think of it as appending one spreadsheet below another. The columns are merged — columns that exist in both tables appear once, columns unique to one table appear with null values for the other.

Why it matters

Microsoft stores sign-in data in two separate tables: SigninLogs (interactive — user signs in with a browser) and AADNonInteractiveUserSignInLogs (non-interactive — an app refreshes a token). To see ALL sign-in activity for a user, you must query both tables. Without union, you only see half the picture.

How it works

1
2
3
4

union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName has "morrison"
| summarize EventCount = count() by Type

Wildcard union

When you need to search across many tables with a common naming pattern:

1
2
3

union Device*
| where TimeGenerated > ago(1h)
| summarize EventCount = count() by Type

This unions all tables starting with “Device” — DeviceProcessEvents, DeviceNetworkEvents, DeviceFileEvents, DeviceLogonEvents, DeviceInfo, DeviceEvents. Useful for broad device investigation when you do not yet know which table contains the evidence.

The join operator — correlating across tables

What it is

join matches rows between two tables based on a shared column value. Unlike union (which stacks rows vertically), join connects rows horizontally — combining columns from both tables into a single row where the key matches.

Why it matters

This is the most powerful investigation tool in KQL. It answers questions that no single table can: “Which users who received the phishing email also had suspicious sign-ins afterward?” “Which devices that triggered a malware alert also made network connections to the C2 IP?” These cross-table correlations are what separate an analyst who can use one tool at a time from one who can trace an entire attack chain.

How it works — the basic join

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromDomain == "northgate-voicemail.com"
| project EmailTime = TimeGenerated, RecipientEmailAddress, Subject
| join kind=inner (
    SigninLogs
    | where TimeGenerated > ago(7d)
    | where ResultType == "0"
    | project SigninTime = TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName
) on $left.RecipientEmailAddress == $right.UserPrincipalName
| where SigninTime between (EmailTime .. (EmailTime + 1h))
| project EmailTime, SigninTime, RecipientEmailAddress, Subject, IPAddress, AppDisplayName

Read this query carefully — it is the foundation of the Module 11 AiTM investigation.

Line by line:

1. Start with EmailEvents, filter to emails from the phishing domain
2. Keep only the relevant email columns, rename TimeGenerated to avoid ambiguity
3. Join with SigninLogs, filtered to successful sign-ins
4. Match where the email recipient equals the sign-in user
5. Filter to sign-ins that occurred within 1 hour AFTER the email was received
6. Display the correlated timeline

Join types — choosing the right one

The kind= parameter controls which rows appear in the result. This is critical for investigation — different join types answer different questions.

The leftanti join — the exclusion pattern

leftanti is particularly powerful for security. It returns rows from the left table that have NO match in the right table. This answers “what is missing?” — which is often more important than “what is present.”

Investigation pattern: token replay detection.

In the Module 11 AiTM investigation, the attacker replayed a stolen token. The stolen token had MFA satisfied (it was captured during the legitimate user’s MFA completion on the proxy). The attacker’s sign-in has AuthenticationRequirement = singleFactorAuthentication (token replay, no MFA challenge) while the legitimate user’s sign-in has AuthenticationRequirement = multiFactorAuthentication.

But the cleaner detection is: find sign-ins that succeeded WITHOUT a corresponding MFA event.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

let successfulSignins =
    SigninLogs
    | where TimeGenerated > ago(24h)
    | where ResultType == "0"
    | where UserPrincipalName has "morrison"
    | project SigninTime = TimeGenerated, CorrelationId, UserPrincipalName, IPAddress;
let mfaEvents =
    AADNonInteractiveUserSignInLogs
    | where TimeGenerated > ago(24h)
    | where UserPrincipalName has "morrison"
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | project CorrelationId;
successfulSignins
| join kind=leftanti mfaEvents on CorrelationId
| project SigninTime, UserPrincipalName, IPAddress

1
2
3
4
5
6
7

union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName has "morrison"
| summarize
    EventCount = count(),
    UniqueIPs = dcount(IPAddress)
    by Type

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromDomain != "yourdomain.onmicrosoft.com"
| project EmailTime = TimeGenerated, RecipientEmailAddress, SenderFromDomain, Subject
| join kind=inner (
    SigninLogs
    | where TimeGenerated > ago(7d)
    | where ResultType == "0"
    | project SigninTime = TimeGenerated, UserPrincipalName, IPAddress
) on $left.RecipientEmailAddress == $right.UserPrincipalName
| where SigninTime between (EmailTime .. (EmailTime + 30m))
| project EmailTime, SigninTime, RecipientEmailAddress, SenderFromDomain, IPAddress
| order by EmailTime asc

Common join mistakes