Cross-Table Joins and Correlation

Why single-table queries aren't enough

The identity kill chain from Section 0.5 spans multiple stages, and each stage produces telemetry in a different table. The initial credential compromise or token theft appears in SigninLogs. Persistence — consent grants, credential additions to service principals, role assignments — appears in AuditLogs. Token replay from the attacker's infrastructure appears in AADNonInteractiveUserSignInLogs. The impact — file exfiltration, inbox rule creation, mailbox manipulation — appears in OfficeActivity.

An analyst who monitors only SigninLogs sees the front door open but not what was stolen. An analyst who monitors only AuditLogs sees the persistence mechanism created but not how the attacker got in. Cross-table correlation is how you reconstruct the complete attack chain from entry to impact. It's also how you build the detection rules that catch the pattern in real time — a Sentinel analytics rule that fires when a risky sign-in is followed by a consent grant within 60 minutes is a cross-table correlation query running on a schedule.

The join operator for identity security

KQL's join operator combines rows from two tables based on a matching condition. The three join kinds you'll use most: inner returns only rows that match in both tables — use when you want to find users who appear in both SigninLogs and AuditLogs. leftouter returns all rows from the left table and matching rows from the right — use when you want to enrich sign-in data with audit context, keeping sign-ins that have no matching audit events. lookup is a specialized join optimized for enrichment — it adds columns from a small reference table without expanding the row count, and is faster than leftouter when the right table fits in memory.

The join key is critical. For identity correlation, the primary join key is UserPrincipalName — the field that connects a sign-in event to the audit actions performed by the same user. But the field name differs across tables: in SigninLogs it's UserPrincipalName, in AuditLogs the actor is nested inside InitiatedBy.user.userPrincipalName, and in OfficeActivity it's UserId. You must extract and align these fields before joining. The $left and $right notation in on $left.Actor == $right.UserPrincipalName handles the name mismatch — it explicitly maps which field on each side of the join corresponds to the other.

A time filter after the join is equally important. Without it, you'd correlate a risky sign-in from Monday with an audit action from Friday — a meaningless connection. The filter where TimeGenerated > TimeGenerated1 ensures the audit event occurred after the risky sign-in. The additional window filter where MinutesAfter < 120 scopes to activity within two hours, which is the window where post-compromise actions are most concentrated based on observed attack timelines.

The most important identity security join pattern correlates risky sign-ins with subsequent administrative actions:

// Correlate risky sign-ins with subsequent admin actions
let riskyUsers = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| distinct UserPrincipalName, TimeGenerated;
AuditLogs
| where TimeGenerated > ago(24h)
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| join kind=inner riskyUsers on $left.Actor == $right.UserPrincipalName
| where TimeGenerated > TimeGenerated1
| project
    SignInTime = TimeGenerated1,
    AuditTime = TimeGenerated,
    User = Actor,
    Operation = OperationName,
    Target = tostring(TargetResources[0].displayName),
    MinutesAfter = datetime_diff('minute', TimeGenerated, TimeGenerated1)
| where MinutesAfter < 120
| order by User, AuditTime asc
// Look for: "Consent to application", "Add member to role",
// "Add service principal credentials", "Update conditional access policy"
// These are kill chain Stage 3-4 activities after suspicious initial access

This query answers the most important question after a risky sign-in: what did the user do with the access they obtained? The let statement defines the risky users as a subquery. The main query joins that result with AuditLogs to find what those users did afterward. The time filter where MinutesAfter < 120 scopes to activity within two hours of the risky sign-in — the window where post-compromise actions are most concentrated.

The AuditLogs operations that matter most for post-compromise correlation: "Consent to application" (OAuth persistence — Section 0.4), "Add member to role" (privilege escalation), "Add service principal credentials" (backdoor creation), "Update conditional access policy" (defense evasion), and "Update application – Certificates and secrets management" (application credential injection). Any of these operations performed by a user who had a risky sign-in within the preceding two hours is a high-confidence attack indicator.

Detecting concurrent sessions across tables

The concurrent session detection pattern joins interactive sign-ins with non-interactive token activity to find users whose sessions are active from multiple IP addresses simultaneously. This is the strongest KQL-based indicator of token theft in progress.

// Detect concurrent sessions from different IPs — token theft indicator
let timeWindow = 1h;
let interactiveSessions = SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == 0
| summarize arg_max(TimeGenerated, IPAddress)
    by UserPrincipalName
| project UserPrincipalName, InteractiveIP = IPAddress;
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == 0
| summarize NonInteractiveIPs = make_set(IPAddress, 20)
    by UserPrincipalName
| join kind=inner interactiveSessions on UserPrincipalName
| extend HasDifferentIP = array_length(
    set_difference(NonInteractiveIPs, dynamic([InteractiveIP]))) > 0
| where HasDifferentIP
| project
    UserPrincipalName, InteractiveIP, NonInteractiveIPs

The logic: find each user's most recent interactive sign-in IP using arg_max from Section 1.9, then compare it against all non-interactive IPs in the same time window. If the non-interactive activity comes from an IP that differs from the interactive session, someone else may be using a stolen token. Common benign causes include VPN split tunnel (the interactive sign-in goes through the VPN but background token refreshes go through the local network), mobile network handoffs, and Microsoft CDN IPs. The suspicious pattern is a non-interactive IP in a different country from the interactive IP — which indicates the token is being replayed from attacker infrastructure.

Union for complete authentication views

When you need the same analysis across both interactive and non-interactive sign-ins, union stacks the tables into a single result set. This is how you build a complete chronological view of a user's authentication activity — essential for incident timelines.

The query union SigninLogs, AADNonInteractiveUserSignInLogs | where UserPrincipalName =~ "user@domain.com" | project TimeGenerated, Type, AppDisplayName, IPAddress produces every sign-in event for a user, labelled by table, in chronological order. The Type column distinguishes the source — "SigninLogs" for interactive, "AADNonInteractiveUserSignInLogs" for non-interactive. During an investigation, this is the first query you run to see the complete picture of a user's session.

Correlating with OfficeActivity

The OfficeActivity table records user activity across M365 workloads — file access in SharePoint and OneDrive, mailbox operations in Exchange Online, and Teams activity. Two OfficeActivity correlations detect the impact stage of identity attacks.

Mass file download after a risky sign-in detects data exfiltration. The pattern uses the same let subquery approach — define the risky users from SigninLogs, then join with OfficeActivity where Operation in ("FileDownloaded", "FileSyncDownloadedFull") and the download timestamp is after the risky sign-in. A threshold of 20+ downloads by a user who had a risky sign-in is a strong Stage 6 (exfiltration) indicator. The query also captures dcount(OfficeObjectId) to count distinct files and make_set(Site_Url) to show which SharePoint sites were accessed — the scope of the exfiltration tells you the severity. A user downloading 50 files from a single project site is different from a user downloading 50 files across 10 different sites. This is the pattern that detects the compromise-to-exfiltration chain that the Microsoft Digital Defense Report identifies as the most damaging identity attack outcome.

Inbox rule creation after a risky sign-in detects BEC persistence. The pattern joins risky users with OfficeActivity New-InboxRule operations. An inbox forwarding rule created within 60 minutes of a risky sign-in is the AiTM-to-BEC pattern from Section 0.7 — the attacker captures the session token via AiTM phishing, signs in with the stolen token, and creates an inbox rule that forwards incoming email to an external address. The forwarding rule provides persistent access to the victim's email even after the session token expires. The Parameters field on the OfficeActivity record contains the rule configuration — look for ForwardTo, ForwardAsAttachmentTo, or RedirectTo parameters with external email addresses. Internal forwarding rules are common and typically benign. External forwarding rules created after a risky sign-in are the highest-confidence BEC indicator in the sign-in log ecosystem.

These OfficeActivity correlations are the queries that would have detected the case study breaches in Section 0.7. When you build detection rules in EI13, these patterns become automated Sentinel analytics rules that fire alerts in real time rather than waiting for manual triage.