In this module

EI1.10 Cross-Table Joins and Correlation

60-80 minutes · Module 1 · Free

Operational Objective

A risky sign-in succeeded for a user at 14:32. You need to know: what did they do next? Did the audit log record any suspicious administrative actions? Did the non-interactive logs show token activity from a different IP? Was an inbox rule created in the Exchange audit data? These questions require joining data across multiple tables — correlating sign-in events with audit events, non-interactive activity, and M365 workload logs to build a complete picture of the session.

Deliverable: The ability to join SigninLogs with AADNonInteractiveUserSignInLogs, AuditLogs, and OfficeActivity to correlate identity events across tables and build the multi-source queries that underpin the detection rules in EI13.

⏱ Estimated completion: 18 minutes

Why cross-table correlation matters

Single-table queries answer single-dimension questions: who signed in, what authentication method was used, what conditional access decided. But real identity attacks span multiple data sources. The initial access appears in SigninLogs. The persistence mechanisms appear in AuditLogs (consent grants, role assignments, credential additions). The lateral movement appears in AADNonInteractiveUserSignInLogs (token refresh from a new IP). The impact appears in OfficeActivity (file downloads, email forwarding rules).

Without cross-table correlation, each of these events looks unremarkable on its own. A successful sign-in is normal. A consent grant is routine. A file download is expected. Only when you correlate them — this user had a risky sign-in, then consented to an unknown application, then downloaded 500 files in 30 minutes — does the attack pattern emerge.

The join operator

KQL's join operator combines rows from two tables based on a matching condition. The basic syntax is:

TableA
| join kind=inner TableB on CommonField

For identity security, the most common join patterns are:

// EI1.10 — 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  // Audit event after the risky sign-in
| project 
    SignInTime = TimeGenerated1,
    AuditTime = TimeGenerated,
    User = Actor,
    Operation = OperationName,
    Target = tostring(TargetResources[0].displayName),
    MinutesAfterSignIn = datetime_diff('minute', TimeGenerated, TimeGenerated1)
| where MinutesAfterSignIn < 120  // Within 2 hours of the risky sign-in
| order by User, AuditTime asc
// Results: administrative actions taken by users who had risky sign-ins
// Look for: "Add member to role", "Add service principal credentials",
// "Consent to application", "Update conditional access policy"
// These are Stage 3-4 kill chain activities performed after suspicious access

// EI1.10 — Detect concurrent sessions from different IPs
// Strongest indicator of token theft in progress
let timeWindow = 1h;
let interactiveSessions = SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == 0
| summarize InteractiveIP = 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,
    OtherIPCount = array_length(
        set_difference(NonInteractiveIPs, dynamic([InteractiveIP])))
// Results: users whose non-interactive sessions come from IPs
// different than their interactive session
// Common benign causes: VPN split tunnel, mobile network, Microsoft CDN IPs
// Suspicious: non-interactive IP in different country than interactive IP

// EI1.10 — Complete sign-in view: interactive + non-interactive
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName == "casey.finance@yourdomain.onmicrosoft.com"
| where ResultType == 0
| project 
    TimeGenerated,
    SignInType = iff(Type == "SigninLogs", "Interactive", "Non-Interactive"),
    AppDisplayName,
    IPAddress,
    Country = tostring(LocationDetails.countryOrRegion),
    ConditionalAccessStatus
| order by TimeGenerated asc
// Complete chronological view of a user's authentication activity
// Useful for incident timelines and baseline comparison

Expand for Deeper Context

Join sign-in logs with audit logs by user — correlate who signed in with what administrative actions they performed:

This query is the operational foundation of identity incident detection. It answers the most important question after a risky sign-in: "what did this user do with the access they obtained?" The let statement defines the risky users as a subquery, then the main query joins that result with the AuditLogs to find what those users did afterward.

Join interactive with non-interactive to detect concurrent sessions:

Union for querying across log types:

When you want the same analysis across both interactive and non-interactive sign-ins, union stacks the tables:

Correlating with audit logs

The AuditLogs table records every administrative and configuration change in Entra ID. The most security-critical operations to correlate with sign-in activity are:

Consent grants: OperationName == "Consent to application" — a user or admin granted permissions to an application. Correlate with the sign-in that preceded it: was the user's sign-in risky?

// EI1.10 — High-risk audit events correlated with sign-in context
let highRiskOperations = dynamic([
    "Consent to application",
    "Add member to role", 
    "Add service principal credentials",
    "Update conditional access policy",
    "Add application",
    "Update application – Certificates and secrets management"
]);
AuditLogs
| where TimeGenerated > ago(24h)
| where OperationName in (highRiskOperations)
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| extend Target = tostring(TargetResources[0].displayName)
| extend TargetType = tostring(TargetResources[0].type)
| lookup kind=leftouter (
    SigninLogs 
    | where TimeGenerated > ago(24h) 
    | where ResultType == 0
    | summarize 
        LastSignInIP = arg_max(TimeGenerated, IPAddress),
        LastSignInRisk = arg_max(TimeGenerated, RiskLevelDuringSignIn)
        by UserPrincipalName
    | project UserPrincipalName, LastSignInIP = IPAddress, 
        LastSignInRisk = RiskLevelDuringSignIn
    ) on $left.Actor == $right.UserPrincipalName
| project 
    TimeGenerated,
    Actor,
    OperationName,
    Target,
    LastSignInIP,
    LastSignInRisk
| order by TimeGenerated desc
// Results: high-risk admin actions with the actor's most recent sign-in context
// An admin action from a user with a high-risk sign-in = investigate immediately

Expand for Deeper Context

Role assignments: OperationName contains "Add member to role" — a user was added to a directory role. Correlate with who performed the action and whether their sign-in was risky.

Credential modifications: OperationName in ("Add service principal credentials", "Update application – Certificates and secrets management") — credentials were added to a service principal or application. Correlate with the actor's recent sign-in activity.

Conditional access changes: OperationName contains "conditional access policy" — a policy was created, modified, or deleted. Correlate with the actor and timing — was this a planned change or a suspicious modification?

The lookup operator is a specialized join optimized for enriching data — it adds columns from a reference table without expanding the row count. Functionally similar to a left outer join but more efficient when the right table is small.

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. For identity security, the most critical correlations connect risky sign-ins with subsequent data access patterns.

Detect mass file download after a risky sign-in:

// EI1.10 — File download volume after risky sign-in
let riskyUsers = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| distinct UserPrincipalName, TimeGenerated;
OfficeActivity
| where TimeGenerated > ago(24h)
| where Operation in ("FileDownloaded", "FileSyncDownloadedFull")
| join kind=inner riskyUsers on $left.UserId == $right.UserPrincipalName
| where TimeGenerated > TimeGenerated1  // Download after the risky sign-in
| summarize 
    DownloadCount = count(),
    DistinctFiles = dcount(OfficeObjectId),
    Sites = make_set(Site_Url, 5)
    by UserId
| where DownloadCount > 20  // Threshold: more than 20 downloads
// Results: users who downloaded many files after a risky sign-in
// This is the Stage 6 (data exfiltration) pattern from the identity kill chain

// EI1.10 — Inbox rule creation correlated with risky sign-in
let riskyUsers = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| distinct UserPrincipalName, TimeGenerated;
OfficeActivity
| where TimeGenerated > ago(24h)
| where Operation == "New-InboxRule"
| join kind=inner riskyUsers on $left.UserId == $right.UserPrincipalName
| where TimeGenerated > TimeGenerated1
| project 
    RiskySignInTime = TimeGenerated1,
    InboxRuleTime = TimeGenerated,
    User = UserId,
    RuleParameters = Parameters,
    MinutesAfterSignIn = datetime_diff('minute', TimeGenerated, TimeGenerated1)
// Results: inbox rules created after risky sign-ins
// This is the AiTM→BEC pattern from EI0.8 Case Study 1
// Every result requires immediate investigation

Expand for Deeper Context

Detect inbox rule creation after a risky sign-in:

These OfficeActivity correlations are the queries that would have detected the case study breaches in EI0.8 — the AiTM attack that led to the inbox rule and BEC, and the consent phishing attack that led to mass file download. When you build detection rules in EI13, these patterns become automated Sentinel analytics rules that fire alerts in real time.

Figure EI1.10 - Cross-Table Joins and Correlation

Try it yourself

Try It — Correlate Sign-Ins with Audit Activity

Environment: Your M365 developer tenant with Sentinel workspace.

Exercise: First, generate some audit log activity by performing an administrative action in the Entra admin center — for example, create a new security group, or modify an existing user's display name. Wait 5-10 minutes for the event to appear in the AuditLogs table.

Then write a KQL query that joins your recent interactive sign-in (from SigninLogs) with the audit event you just generated (from AuditLogs). Join on your admin UPN and filter the audit event to occur after the sign-in. The result should show: your sign-in time, your audit action time, the operation you performed, and the minutes between the sign-in and the action.

This is the same correlation pattern used to detect post-compromise administrative activity — you are just using your own legitimate activity to practice the query.

⚠ Compliance Myth: "Our SIEM correlates events automatically — we do not need to write cross-table queries"

The myth: Microsoft Sentinel correlates events across data sources automatically through its built-in detection rules and incident correlation. We do not need to write custom cross-table queries.

The reality: Sentinel's built-in analytics rules and fusion detection provide valuable automated correlation, but they are generic — designed to detect common patterns across all tenants. They do not know your organization's specific applications, user behavior patterns, named locations, or critical accounts. Custom cross-table queries tailored to your environment detect patterns that the built-in rules miss — like a specific service principal signing in from a new IP followed by a credential addition to a different service principal. EI13 builds these custom detection rules using the cross-table correlation patterns taught in this subsection. The built-in rules are a starting point; custom rules are the operational advantage.

📋 Investigation Artifact — Cross-Table Correlation Templates

Reusable join patterns for identity security:

Cross-Table Correlation Patterns

1. Sign-in → Audit (post-auth admin actions):

let users = SigninLogs | where [condition] | distinct UserPrincipalName, TimeGenerated;

AuditLogs | extend Actor = tostring(InitiatedBy.user.userPrincipalName) | join kind=inner users on $left.Actor == $right.UserPrincipalName | where TimeGenerated > TimeGenerated1

2. Interactive → Non-Interactive (concurrent sessions):

let interactive = SigninLogs | summarize arg_max(TimeGenerated, IPAddress) by UserPrincipalName;

AADNonInteractiveUserSignInLogs | join kind=inner interactive on UserPrincipalName | where IPAddress != IPAddress1

3. Sign-in → OfficeActivity (post-auth data access):

let signins = SigninLogs | where [condition] | distinct UserPrincipalName, TimeGenerated;

OfficeActivity | join kind=inner signins on $left.UserId == $right.UserPrincipalName | where TimeGenerated > TimeGenerated1

4. Union (complete view across log types):

union SigninLogs, AADNonInteractiveUserSignInLogs | where UserPrincipalName == "[user]" | project TimeGenerated, Type, AppDisplayName, IPAddress

5. Lookup (enrichment without expansion):

AuditLogs | lookup (SigninLogs | summarize by UserPrincipalName, IPAddress) on $left.Actor == $right.UserPrincipalName

Compliance mapping: Cross-table correlation supports SOC 2 CC7.2 (Monitoring Activities), ISO 27001 A.12.4 (Logging and Monitoring), and NIST CSF DE.AE-3 (Event Correlation).

Decision point

You are reviewing NE's Entra ID security posture. You find 4 accounts with Global Administrator role, but NE's policy says maximum 2. The extra 2 were added during the AiTM incident for emergency response and never removed. Do you remove them?

Remove them — but through the proper process, not unilaterally. Notify the account owners that their emergency GA assignment is being revoked, confirm they have their standard role assignments restored, and document the removal with the rationale ('emergency assignment during INC-NE-2026-0227-001, no longer required'). Then add a PIR action item: 'Implement PIM time-limited role assignments for future incident response — emergency GA assignments auto-expire after 8 hours rather than persisting indefinitely.' The stale emergency assignment is a governance failure, not a technical failure — the fix is procedural.

NE's Entra ID security audit reveals: 4 Global Administrators (policy says 2), 23 users with Global Reader from a completed project, a break-glass account with no monitoring rule, and 3 guest accounts with no expiry date. Which finding is the highest priority?

The 4 Global Administrators — 2 extra GAs doubles the attack surface.

The break-glass account with no monitoring rule. The 4 GAs and stale Global Readers are governance issues that should be remediated — but they are existing conditions, not active threats. The unmonitored break-glass account is a critical detection gap: if the break-glass account is compromised or misused, the SOC has no alert. A break-glass account is excluded from CA policies by design — it is the most powerful and least restricted account in the tenant. Without monitoring, its compromise or misuse is invisible. Deploy the monitoring rule (any sign-in to the break-glass account = Severity 1 alert) before addressing the other findings.

The 23 stale Global Readers — this is the largest number of affected accounts.

The 3 guest accounts — external accounts without expiry are the highest risk.

You've mapped the identity threat landscape and learned to read sign-in logs.

EI0 established that every cloud attack starts with identity. EI1 took you through the signal that matters most — interactive, non-interactive, service principal, and managed identity sign-ins. Now you engineer the defences.

17 engineering modules — authentication methods, conditional access architecture, Identity Protection, PIM, token protection, application governance, and detection rules
The Defense Design Method — the six-step framework applied to every identity control you'll build
EI18 Capstone — Identity Security Architecture Design — design complete identity architectures for three realistic organisations (SMB, mid-market, regulated enterprise)
Identity Security Toolkit lab pack — deployable conditional access policies, PIM configurations, and Identity Protection risk rules
Cross-domain detection (EI16) — email-to-identity correlation and the full phishing-to-inbox-rule attack chain

Unlock the full course with Premium See Full Syllabus

← Previous Next →