TH1.9 Advanced Collection: Multi-Table Correlation Patterns

// Pattern: anomalous sign-in → directory modification in same window
// Use case: AiTM → MFA registration, role assignment, CA policy change
let anomalyWindow = 24h;
let suspectSignIns = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| project SignInTime = TimeGenerated,
    UserPrincipalName, IPAddress,
    tostring(LocationDetails.countryOrRegion);
// Join: what directory changes did these users make?
AuditLogs
| where TimeGenerated > ago(7d)
| where Result == "success"
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| join kind=inner suspectSignIns
    on $left.Actor == $right.UserPrincipalName
| where TimeGenerated between (
    SignInTime .. (SignInTime + anomalyWindow))
// Directory changes within 24h of a risky sign-in
// Relevant operations: "User registered security info",
//   "Add member to role", "Update conditional access policy",
//   "Consent to application"
| project TimeGenerated, Actor, OperationName,
    SignInTime, IPAddress

// Pattern: sign-in from new IP → cloud app activity from that session
// Use case: post-compromise activity mapping
let suspectUsers = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
// ... (your indicator filter from the hunt hypothesis)
| distinct UserPrincipalName, IPAddress;
CloudAppEvents
| where TimeGenerated > ago(7d)
| join kind=inner suspectUsers
    on $left.AccountId == $right.UserPrincipalName
| where IPAddress == suspectUsers.IPAddress
// Activity from the same IP as the anomalous sign-in
// Look for: New-InboxRule, Set-Mailbox, file downloads,
//   sharing operations, consent events
| summarize Actions = make_set(ActionType, 20),
    ActionCount = count()
    by AccountId, IPAddress
| where ActionCount > 5
// Users with 5+ actions from the anomalous IP warrant investigation

// Pattern: phishing email → compromised sign-in within N hours
// Use case: confirming AiTM attack chain
let maxPhishToSignIn = 48h;
let riskySignIns = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| project SignInTime = TimeGenerated,
    UserPrincipalName, IPAddress;
EmailEvents
| where TimeGenerated > ago(7d)
| where DeliveryAction == "Delivered"
| where ThreatTypes has_any ("Phish", "Malware")
| join kind=inner riskySignIns
    on $left.RecipientEmailAddress == $right.UserPrincipalName
| where SignInTime between (
    TimeGenerated .. (TimeGenerated + maxPhishToSignIn))
// Phishing email delivered → risky sign-in within 48 hours
// This correlation is the strongest single indicator of AiTM
| project EmailTime = TimeGenerated, Subject,
    SenderFromAddress, SignInTime, IPAddress,
    UserPrincipalName

// Pattern: cloud compromise → endpoint execution
// Use case: tracking attack pivot from cloud to endpoint
let compromisedUsers = datatable(UPN:string)
["j.morrison@northgateeng.com"];  // From hunt finding
// Cloud activity
let cloudActions = CloudAppEvents
| where TimeGenerated > ago(7d)
| where AccountId in (compromisedUsers)
| project CloudTime = TimeGenerated, ActionType,
    Application, AccountId;
// Endpoint activity for the same user
DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where AccountUpn in (compromisedUsers)
| project EndpointTime = TimeGenerated,
    FileName, ProcessCommandLine,
    DeviceName, AccountUpn
// Manual correlation: examine cloud and endpoint timelines
//   side by side for the compromised user
// Look for: file downloads in cloud followed by execution on endpoint
// Look for: browser-based credential theft followed by tool deployment

// Pattern: interactive baseline → non-interactive anomaly
// Use case: AiTM token replay (TH4 primary detection)
let baselineWindow = 30d;
let detectionWindow = 7d;
let interactiveBaseline = SigninLogs
| where TimeGenerated between (
    ago(baselineWindow + detectionWindow) .. ago(detectionWindow))
| where ResultType == 0 and IsInteractive == true
| summarize KnownIPs = make_set(IPAddress, 30)
    by UserPrincipalName;
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(detectionWindow)
| where ResultType == 0
| join kind=inner interactiveBaseline on UserPrincipalName
| where not(IPAddress in (KnownIPs))
// Token refresh from IP not seen in 30-day interactive baseline
// This is the signature of token replay
| summarize RefreshCount = count(),
    Apps = make_set(AppDisplayName, 5)
    by UserPrincipalName, IPAddress
| where RefreshCount > 3
// Multiple refreshes = sustained session from anomalous IP

// Pattern: consent event → app sign-in → data access
// Use case: privilege escalation investigation (TH6)
let recentConsents = AuditLogs
| where TimeGenerated > ago(90d)
| where OperationName == "Consent to application"
| where Result == "success"
| extend AppId = tostring(TargetResources[0].id)
| extend ConsentedBy = tostring(
    InitiatedBy.user.userPrincipalName)
| project ConsentTime = TimeGenerated,
    AppId, ConsentedBy;
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(90d)
| join kind=inner recentConsents on AppId
| where TimeGenerated > ConsentTime
// App sign-ins AFTER consent was granted
| summarize
    SignInCount = count(),
    UniqueIPs = dcount(IPAddress),
    IPs = make_set(IPAddress, 5)
    by AppId, ConsentedBy
// High sign-in count from many IPs post-consent = suspicious
// Low sign-in count from expected IP = likely legitimate app