Advanced Cross-Product Threat Hunting

// ============================================================
// TELEMETRY HEALTH CHECK – verify data is flowing from each product
// Run each line individually in Advanced Hunting at security.microsoft.com
// Purpose: Before cross-product hunting, confirm every data source
//   has recent events. Stale or missing data = blind spots.
// Output columns:
//   Events      – number of events in last 24h (0 = problem)
//   LatestEvent – timestamp of most recent event (stale = config issue)
// ============================================================

// ENDPOINT (MDE): Device-level security events
// Covers: process execution, file changes, logons, registry mods
// If Events=0 → no devices onboarded to MDE or agent not reporting
DeviceEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)
// ENDPOINT (MDE): Process creation telemetry specifically
// This is the richest table for detecting malicious execution
DeviceProcessEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)

// EMAIL (MDO): Mail flow events for all processed emails
// Covers: inbound/outbound delivery, phishing verdicts, spam filtering
// If Events=0 → MDO not licensed or no mailboxes connected
EmailEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)
// EMAIL (MDO): Attachment metadata for files attached to emails
// Includes: file name, type, SHA256 hash, detonation results
EmailAttachmentInfo | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)

// IDENTITY (MDI): Authentication events from AD domain controllers
// Covers: Kerberos, NTLM, interactive/remote logons, failures
// If Events=0 → MDI sensors not installed on domain controllers
IdentityLogonEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)
// IDENTITY (MDI): LDAP/AD query events (reconnaissance detection)
// Covers: user/group enumeration, service account queries
IdentityQueryEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)

// CLOUD APPS (MDA): SaaS application activity
// Covers: OAuth grants, file downloads, admin changes, app access
// If Events=0 → MDA not configured or no app connectors enabled
CloudAppEvents | where Timestamp > ago(24h) | summarize Events=count(), LatestEvent=max(Timestamp)

// ALERTS (cross-product): All alerts from all Defender products
// Using 7d window since alerts are generated less frequently than raw events
// Output: total alert count + list of detection sources sending alerts
// Sources should include: MicrosoftDefenderForEndpoint, MicrosoftDefenderForOffice365, etc.
AlertInfo | where Timestamp > ago(7d) | summarize Alerts=count(), Sources=make_set(DetectionSource)

// DETECT ENCODED POWERSHELL EXECUTION
// MITRE ATT&CK: T1059.001 (Command and Scripting Interpreter: PowerShell)
// Why: Attackers encode PowerShell commands in Base64 to evade static signature
//   detection and hide malicious payloads. Legitimate encoded commands exist
//   (e.g., some Azure scripts) but are typically short; malicious ones are long.
//
// Detection logic:
//   1. Filters to powershell.exe or pwsh.exe (PowerShell 7+) processes
//   2. Checks ProcessCommandLine for encoding indicators:
//      -EncodedCommand / -enc / -e / -ec  = direct Base64 flag
//      FromBase64String / Convert]::FromBase64 = inline decoding in script
//      -WindowStyle Hidden / -w hidden   = hidden execution (no visible window)
//      -nop -sta = no profile + single-threaded (common attack combo)
//   3. Filters by CommandLength > 200 chars (encoded payloads are long strings)
//
// Output columns:
//   InitiatingProcessFileName – what launched PowerShell (e.g., explorer.exe,
//     cmd.exe, or an Office app like WINWORD.EXE = macro-based attack)
//   CommandLength – > 500 chars is highly suspicious
//   SHA256 – hash of the PowerShell binary (verify it’s not renamed)
// False positives: SCCM, Azure Automation, DSC scripts may trigger.
//   Exclude known parent processes via InitiatingProcessFileName if needed.
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any (
    "-EncodedCommand", "-enc ", "-e ", "-ec ",
    "FromBase64String", "Convert]::FromBase64",
    "-WindowStyle Hidden", "-w hidden", "-nop -sta"
)
| extend CommandLength = strlen(ProcessCommandLine)
| where CommandLength > 200  // Encoded commands are typically long
| project Timestamp, DeviceName, AccountUpn,
          FileName, ProcessCommandLine, CommandLength,
          InitiatingProcessFileName, InitiatingProcessCommandLine,
          FolderPath, SHA256
| order by Timestamp desc
| take 100

// DETECT LOLBins ABUSE (Living-off-the-Land Binaries)
// MITRE ATT&CK: T1218 (System Binary Proxy Execution)
// Why: Attackers use legitimate Windows binaries to bypass application
//   whitelisting and evade detection. These tools are signed by Microsoft
//   and present on every Windows machine, making them ideal for:
//   - Downloading malware from remote URLs (certutil, bitsadmin)
//   - Executing scripts without PowerShell (mshta, wscript, cscript)
//   - Loading malicious DLLs (rundll32, regsvr32)
//   - Compiling/running code inline (msbuild, installutil)
//
// LOLBin-specific indicators in command line:
//   http/https/ftp     – remote payload download
//   /i: + scrobj.dll   – regsvr32 loading a COM scriptlet (Squiblydoo)
//   javascript:        – mshta executing inline JavaScript
//   -decode/-urlcache  – certutil downloading & decoding Base64 files
//   process call create – wmic spawning a new process remotely
//   /transfer          – bitsadmin downloading files in background
//
// Output: Each row = a LOLBin execution with suspicious arguments.
//   Check InitiatingProcessFileName to see what spawned the LOLBin
//   (e.g., cmd.exe from a macro → Word launched the attack)
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ (
    "mshta.exe", "certutil.exe", "regsvr32.exe",
    "rundll32.exe", "msbuild.exe", "installutil.exe",
    "cmstp.exe", "wmic.exe", "bitsadmin.exe"
)
| where ProcessCommandLine has_any (
    "http://", "https://", "ftp://",       // Remote downloads
    "/i:", "scrobj.dll", "javascript:",     // Script execution
    "-decode", "-urlcache", "-split",       // certutil abuse
    "process call create",                  // wmic process creation
    "/transfer"                             // bitsadmin transfers
)
| project Timestamp, DeviceName, AccountUpn,
          FileName, ProcessCommandLine,
          InitiatingProcessFileName,
          InitiatingProcessCommandLine
| order by Timestamp desc
| take 50

// DETECT PROCESS INJECTION via Windows API abuse
// MITRE ATT&CK: T1055 (Process Injection)
// Why: Attackers inject malicious code into legitimate processes to:
//   - Run code inside trusted processes (evade EDR detections)
//   - Steal credentials from LSASS memory
//   - Establish persistence without writing files to disk
//
// Windows API calls that indicate injection:
//   CreateRemoteThreadApiCall           – creates a thread in another process (classic injection)
//   QueueUserApcRemoteApiCall           – APC injection (queues code in target’s thread)
//   NtAllocateVirtualMemoryRemoteApiCall – allocates memory in another process
//   WriteProcessMemoryApiCall           – writes shellcode into target process memory
//   SetThreadContextRemoteApiCall       – hijacks an existing thread’s execution context
//
// Aggregation logic:
//   Groups by InitiatingProcessFileName (what’s doing the injecting) + ActionType
//   per 1-hour time bin. The InjectionCount > 3 threshold filters single-event noise.
//   make_set() shows WHICH processes were injected into (targets like svchost.exe,
//   explorer.exe are suspicious; security software targets may be legitimate).
//
// False positives: AV/EDR agents, accessibility tools, legitimate hooking.
//   Check InitiatingProcessFileName against your software inventory.
DeviceEvents
| where Timestamp > ago(7d)
| where ActionType in (
    "CreateRemoteThreadApiCall",
    "QueueUserApcRemoteApiCall",
    "NtAllocateVirtualMemoryRemoteApiCall",
    "WriteProcessMemoryApiCall",
    "SetThreadContextRemoteApiCall"
)
| project Timestamp, DeviceName, AccountUpn,
          ActionType, FileName,
          InitiatingProcessFileName,
          InitiatingProcessCommandLine,
          AdditionalFields
| summarize InjectionCount = count(),
            TargetProcesses = make_set(FileName),
            Devices = make_set(DeviceName)
    by InitiatingProcessFileName, ActionType, bin(Timestamp, 1h)
| where InjectionCount > 3
| order by InjectionCount desc

// FIND DELIVERED PHISHING EMAILS that bypassed or partially bypassed filtering
// MITRE ATT&CK: T1566.001 (Phishing: Spearphishing Attachment)
// Why: Emails that reach user mailboxes are the starting point of most
//   enterprise attacks. This query finds threats that evaded protection.
//
// Filter logic:
//   ThreatTypes has "Phish" or "Malware" – MDO’s threat classification
//   DetectionMethods – HOW MDO detected the threat:
//     "URL detonation"   = Safe Links sandbox analysis found malicious URL
//     "File detonation"  = Safe Attachments sandbox detonated a malicious file
//     "Impersonation domain" = sender domain spoofing a trusted brand
//     "Mailbox intelligence" = AI detected impersonation based on mail patterns
//   DeliveryAction:
//     "Delivered" = reached inbox (highest risk – user can interact)
//     "Junked"    = went to Junk folder (lower risk but user can still open)
//
// Key output columns:
//   NetworkMessageId      – unique email ID for joining with attachment/URL tables
//   SenderFromAddress      – display "From" (can be spoofed)
//   SenderMailFromAddress  – envelope sender (harder to spoof, check for mismatches)
//   AuthenticationDetails  – SPF/DKIM/DMARC results (failures = likely spoofed)
//   RecipientEmailAddress  – the targeted user (use this to join with identity events)
EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has_any ("Phish", "Malware")
    or DetectionMethods has_any ("URL detonation", "File detonation",
        "Impersonation domain", "Mailbox intelligence impersonation")
| where DeliveryAction == "Delivered"
    or DeliveryAction == "Junked"
| project Timestamp, NetworkMessageId, SenderFromAddress,
          SenderMailFromAddress, RecipientEmailAddress,
          Subject, ThreatTypes, DetectionMethods,
          DeliveryAction, DeliveryLocation,
          AuthenticationDetails
| order by Timestamp desc
| take 50

// IDENTIFY WEAPONIZED EMAIL ATTACHMENTS
// MITRE ATT&CK: T1566.001 (Phishing: Spearphishing Attachment)
// Why: Malicious attachments are the #1 payload delivery method.
//   This query correlates attachment metadata with email delivery status.
//
// High-risk file types explained:
//   exe/dll/scr  – direct executables (rarely legitimate in email)
//   js/vbs/hta   – script files that run via Windows Script Host
//   ps1          – PowerShell scripts
//   bat/cmd      – batch/command scripts
//   iso/img/vhd  – disk images that auto-mount and bypass Mark-of-the-Web
//   lnk          – shortcuts that can launch arbitrary commands
//   .html.htm    – double-extension trick to disguise HTML credential harvesters
//
// Join logic: inner join on NetworkMessageId links each attachment
//   to its parent email, filtering to only delivered/junked emails.
// Output: SHA256 hash can be searched in VirusTotal for known malware.
//   FileType mismatches (e.g., FileType="exe" but FileName="invoice.pdf.exe")
//   are strong indicators of social engineering.
EmailAttachmentInfo
| where Timestamp > ago(7d)
| where FileType in~ (
    "exe", "dll", "scr", "js", "vbs", "hta", "ps1",
    "bat", "cmd", "iso", "img", "vhd", "lnk"
)
    or FileName endswith ".html.htm"    // Double extensions
    or FileName matches regex @"\.\w+\.\w+$"  // Any double extension
| join kind=inner (
    EmailEvents
    | where Timestamp > ago(7d)
    | where DeliveryAction in ("Delivered", "Junked")
) on NetworkMessageId
| project Timestamp, SenderFromAddress, RecipientEmailAddress,
          Subject, FileName, FileType, SHA256,
          ThreatTypes, DeliveryAction
| order by Timestamp desc

// TRACE MALICIOUS URLS IN PHISHING CAMPAIGNS
// MITRE ATT&CK: T1566.002 (Phishing: Spearphishing Link)
// Why: URL-based phishing is increasingly common as organizations
//   improve attachment filtering. This query identifies phishing
//   domains and reveals campaign scope (how many users were targeted).
//
// How it works:
//   1. Starts from EmailUrlInfo (all URLs extracted from emails)
//   2. Excludes known Microsoft domains to reduce noise
//   3. Inner joins with EmailEvents filtered to delivered phishing emails
//      using NetworkMessageId (unique email identifier)
//   4. Aggregates by UrlDomain to reveal campaign infrastructure
//
// Output columns:
//   UrlDomain       – the phishing domain (check in VirusTotal/URLhaus)
//   PhishEmailCount – how many phishing emails used this domain
//   TargetedUsers   – list of users who received emails with this URL
//   UrlList         – full URLs (look for credential harvesting paths
//                     like /login, /signin, /verify commonly used in BEC)
// High PhishEmailCount with many TargetedUsers = organized phishing campaign
EmailUrlInfo
| where Timestamp > ago(7d)
| where UrlDomain !in~ ("microsoft.com", "office.com", "windows.net",
    "microsoftonline.com", "sharepoint.com")
| join kind=inner (
    EmailEvents
    | where Timestamp > ago(7d)
    | where ThreatTypes has "Phish"
    | where DeliveryAction == "Delivered"
) on NetworkMessageId
| project Timestamp, RecipientEmailAddress, SenderFromAddress,
          Subject, Url, UrlDomain, UrlLocation
| summarize PhishEmailCount = count(),
            TargetedUsers = make_set(RecipientEmailAddress),
            UrlList = make_set(Url)
    by UrlDomain
| order by PhishEmailCount desc

// DETECT BRUTE-FORCE PASSWORD ATTACKS with successful compromise
// MITRE ATT&CK: T1110.001 (Brute Force: Password Guessing)
// Why: Brute-force attacks try many passwords against one account.
//   The dangerous pattern is many failures FOLLOWED by a success,
//   meaning the attacker guessed the correct password.
//
// Tunable thresholds:
//   failureThreshold = 10 – minimum failed attempts to trigger detection
//     (lower = more sensitive but more false positives from typos)
//   timeWindow = 1h – aggregation window for counting failures
//     (password spray attacks may spread failures over longer periods)
//
// Detection logic:
//   Groups all logon events per user per 1-hour window, then checks:
//   - FailedAttempts >= 10 (brute-force pattern)
//   - SuccessfulAttempts >= 1 (attacker got in – HIGH PRIORITY)
//
// Output: each row = a likely compromised account
//   FailureReasons – "BadPassword" is typical; "AccountLocked" means
//     lockout policy kicked in (but attacker may have succeeded before lock)
//   SourceIPs – multiple IPs suggest distributed/botnet attack
//   TargetDevices – which systems the attacker targeted
let failureThreshold = 10;
let timeWindow = 1h;
IdentityLogonEvents
| where Timestamp > ago(7d)
| summarize
    FailedAttempts = countif(ActionType == "LogonFailed"),
    SuccessfulAttempts = countif(ActionType == "LogonSuccess"),
    FailureReasons = make_set(FailureReason),
    SourceIPs = make_set(IPAddress),
    TargetDevices = make_set(DeviceName)
    by AccountUpn, bin(Timestamp, timeWindow)
| where FailedAttempts >= failureThreshold
    and SuccessfulAttempts >= 1  // Compromise indicator
| extend RiskIndicator = "BruteForceSuccess"
| project Timestamp, AccountUpn, FailedAttempts,
          SuccessfulAttempts, FailureReasons,
          SourceIPs, TargetDevices, RiskIndicator
| order by FailedAttempts desc

// DETECT PASS-THE-HASH AND NTLM LATERAL MOVEMENT
// MITRE ATT&CK: T1550.002 (Use Alternate Authentication Material: Pass the Hash)
// Why: After stealing NTLM hashes (e.g., from LSASS memory), attackers
//   use them to authenticate to other machines without knowing the password.
//   NTLM Network logons to MULTIPLE unique devices in a short window
//   is the strongest indicator of lateral movement.
//
// Detection logic:
//   1. Filters to NTLM protocol (not Kerberos) – PtH uses NTLM
//   2. Only successful Network logons (remote authentication)
//   3. Groups by user per 1-hour window
//   4. Checks: NTLMLogonCount > 5 AND UniqueTargets > 3
//      Normal users rarely authenticate to 3+ different servers in 1 hour
//      via NTLM. Admins may, so tune threshold for admin accounts.
//
// Output:
//   UniqueTargets  – number of distinct devices accessed (high = lateral movement)
//   TargetDevices  – list of device names (look for DCs, file servers, tier-0 assets)
//   SourceIPs      – originating IP addresses
// False positives: Domain admins, SCCM, backup agents. Exclude known service accounts.
IdentityLogonEvents
| where Timestamp > ago(7d)
| where Protocol == "NTLM"
| where ActionType == "LogonSuccess"
| where LogonType == "Network"
| summarize
    NTLMLogonCount = count(),
    UniqueTargets = dcount(DeviceName),
    TargetDevices = make_set(DeviceName),
    SourceIPs = make_set(IPAddress)
    by AccountUpn, bin(Timestamp, 1h)
| where NTLMLogonCount > 5
    and UniqueTargets > 3   // Multiple targets = lateral movement
| extend RiskIndicator = "PotentialPtH_LateralMovement"
| order by UniqueTargets desc

// PROACTIVE CORRELATION: Phishing recipients who later logged in
// MITRE ATT&CK: T1566 → T1078 (Phishing → Valid Accounts)
// Why: This is a lightweight pre-join that finds users who:
//   1. Received a delivered phishing email (from Step 3)
//   2. Subsequently had a successful logon event
//   This does NOT mean they’re compromised, but they ARE high-priority
//   for investigation. Any unusual IPAddress, Location, or Application
//   in the logon event suggests credential harvesting was successful.
//
// How it works:
//   - 'let phishedUsers' creates a list of email recipients from EmailEvents
//   - The main query filters IdentityLogonEvents to only those users
//   - Sorted by time (asc) to see logons AFTER the phishing delivery
//
// Key columns to examine:
//   IPAddress   – is this from a known/expected location?
//   Location    – does this match the user’s normal geography?
//   Application – did the attacker access a sensitive app (e.g., Exchange, SharePoint)?
//   LogonType   – Interactive from a new location is high-risk
let phishedUsers =
    EmailEvents
    | where Timestamp > ago(7d)
    | where ThreatTypes has "Phish"
    | where DeliveryAction == "Delivered"
    | distinct RecipientEmailAddress;
IdentityLogonEvents
| where Timestamp > ago(7d)
| where AccountUpn in (phishedUsers)
| where ActionType == "LogonSuccess"
| project Timestamp, AccountUpn, IPAddress, DeviceName,
          Location, Application, LogonType, Protocol
| order by Timestamp asc

// CROSS-PRODUCT JOIN: Phishing email → Credential compromise
// MITRE ATT&CK: T1566 → T1078 (Phishing → Valid Accounts)
// Purpose: Directly correlate a delivered phishing email with a
//   subsequent suspicious logon by the SAME user within 4 hours.
//   This is evidence of a successful credential harvesting attack.
//
// How the join works:
//   1. 'let phishingEmails' captures all delivered phishing emails
//   2. 'let suspiciousLogons' captures all successful logon events
//   3. Inner join on RecipientEmailAddress == AccountUpn links them
//   4. 'where LogonTime between (PhishTime .. PhishTime + 4h)' ensures
//      the logon happened AFTER the phishing email and within the window
//
// Output columns:
//   PhishTime     – when the phishing email was delivered
//   LogonTime     – when the user’s credentials were used
//   TimeDelta     – time between phish and logon (shorter = more suspicious)
//   IPAddress     – logon source IP (different from user’s normal IP = compromise)
//   Application   – what the attacker accessed first (OWA, SharePoint, Teams, etc.)
//   LogonType     – Interactive = direct sign-in with stolen credentials
//
// The 4-hour window is typical for credential harvesting. Adjust:
//   - Narrow to 1h for automated attack campaigns
//   - Widen to 24h for targeted spearphishing with delayed exploitation
let phishingEmails =
    EmailEvents
    | where Timestamp > ago(7d)
    | where ThreatTypes has "Phish"
    | where DeliveryAction == "Delivered"
    | project PhishTime = Timestamp, RecipientEmailAddress,
              SenderFromAddress, Subject, NetworkMessageId;
let suspiciousLogons =
    IdentityLogonEvents
    | where Timestamp > ago(7d)
    | where ActionType == "LogonSuccess"
    | project LogonTime = Timestamp, AccountUpn,
              IPAddress, DeviceName, Application, LogonType;
phishingEmails
| join kind=inner (suspiciousLogons)
    on $left.RecipientEmailAddress == $right.AccountUpn
| where LogonTime between (PhishTime .. (PhishTime + 4h))
| project PhishTime, LogonTime,
          TimeDelta = LogonTime. PhishTime,
          AccountUpn, SenderFromAddress, Subject,
          IPAddress, DeviceName, Application, LogonType
| order by PhishTime asc

// ============================================================
// FULL KILL CHAIN CORRELATION: Email → Identity → Endpoint
// MITRE ATT&CK: T1566 → T1078 → T1059 (Phishing → Valid Accounts → Execution)
// Purpose: Traces a complete 3-stage attack across all three products:
//   Stage 1 (MDO): Phishing email delivered to user
//   Stage 2 (MDI): Stolen credentials used to sign in
//   Stage 3 (MDE): Attacker runs malicious commands on endpoint
// This is the most powerful cross-product query in this lab.
//
// Join strategy:
//   - Stage 1 → Stage 2: joined on TargetUser == AccountUpn
//     with 4h time window (cred harvesting window)
//   - Stage 2 → Stage 3: joined on AccountUpn
//     with 8h time window (post-compromise execution window)
//
// Stage 3 detection criteria:
//   Suspicious processes: powershell, cmd, wscript, cscript, mshta, certutil
//   Suspicious arguments: encoded commands, download strings, bypass flags
//   These indicate hands-on-keyboard attacker activity post-compromise
//
// Output: Each row = a confirmed 3-stage attack chain
//   AttackDuration – total time from email to execution (assess urgency)
//   LogonIP – attacker’s IP address (geo-locate and block)
//   ProcessCommandLine – what the attacker actually ran (decode if encoded)
// ============================================================
let lookback = 7d;
// Stage 1: Phishing emails delivered
let stage1_phish =
    EmailEvents
    | where Timestamp > ago(lookback)
    | where ThreatTypes has "Phish" and DeliveryAction == "Delivered"
    | project PhishTime = Timestamp, TargetUser = RecipientEmailAddress,
              SenderFromAddress, Subject, NetworkMessageId;
// Stage 2: Successful logons by phished users
let stage2_logon =
    IdentityLogonEvents
    | where Timestamp > ago(lookback)
    | where ActionType == "LogonSuccess"
    | project LogonTime = Timestamp, AccountUpn,
              LogonIP = IPAddress, LogonDevice = DeviceName,
              Application, Protocol;
// Stage 3: Suspicious process execution by compromised users
let stage3_execution =
    DeviceProcessEvents
    | where Timestamp > ago(lookback)
    | where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe",
                           "cscript.exe", "mshta.exe", "certutil.exe")
    | where ProcessCommandLine has_any (
        "-enc", "FromBase64", "bypass", "http://", "https://",
        "Invoke-", "IEX", "downloadstring", "-nop"
    )
    | project ExecTime = Timestamp, AccountUpn,
              ExecDevice = DeviceName, FileName,
              ProcessCommandLine, SHA256;
// Correlate all three stages using time-windowed joins
stage1_phish
| join kind=inner (stage2_logon)
    on $left.TargetUser == $right.AccountUpn
| where LogonTime between (PhishTime .. (PhishTime + 4h))
| join kind=inner (stage3_execution)
    on AccountUpn
| where ExecTime between (LogonTime .. (LogonTime + 8h))
| project PhishTime, LogonTime, ExecTime,
          AccountUpn, SenderFromAddress, Subject,
          LogonIP, LogonDevice, Application,
          ExecDevice, FileName, ProcessCommandLine
| extend AttackDuration = ExecTime. PhishTime
| order by PhishTime asc

// ============================================================
// COMPREHENSIVE CROSS-PRODUCT ATTACK TIMELINE
// Purpose: Merge ALL telemetry sources for a single user into one
//   chronological view. This reconstructs the entire attack narrative.
//
// How it works:
//   - 'union' combines rows from 7 different tables into one result
//   - Each sub-query targets the same user and labels its Source
//   - Results sorted by Timestamp (ascending) = attack progression
//
// Data sources included:
//   1. Email (MDO)      – phishing delivery to the user’s mailbox
//   2. Attachment (MDO)  – details of attached files (type, hash)
//   3. Identity (MDI)    – authentication events (logon success/failure)
//   4. AD Query (MDI)    – LDAP/directory queries (reconnaissance)
//   5. Process (MDE)     – process execution on endpoints
//   6. Network (MDE)     – outbound connections (C2, exfiltration)
//   7. Cloud App (MDA)   – SaaS app activity (data access)
//
// Reading the timeline: Each row shows WHEN, WHERE (source), and WHAT.
//   Look for the progression pattern typical of multi-stage attacks:
//   MDO → MDI → MDE Process → MDE Network → MDA
// ============================================================
// Replace the targetUser value with the actual compromised account UPN
let targetUser = "jsmith@contoso.com";
let timeRange = 24h;
union
    // Email events: phishing email delivery to target user
    (EmailEvents
     | where Timestamp > ago(timeRange)
     | where RecipientEmailAddress =~ targetUser
     | extend Source = "Email (MDO)",
              Detail = strcat("From: ", SenderFromAddress,
                              " | Subject: ", Subject,
                              " | Action: ", DeliveryAction,
                              " | Threat: ", ThreatTypes)
     | project Timestamp, Source, Detail),
    // Email attachment info: files attached to emails received by target
    (EmailAttachmentInfo
     | where Timestamp > ago(timeRange)
     | join kind=inner (
         EmailEvents | where RecipientEmailAddress =~ targetUser
     ) on NetworkMessageId
     | extend Source = "Attachment (MDO)",
              Detail = strcat("File: ", FileName,
                              " | Type: ", FileType,
                              " | SHA256: ", SHA256)
     | project Timestamp, Source, Detail),
    // Identity logon events: authentication attempts by target user
    // ActionType shows LogonSuccess vs LogonFailed; IP/Location reveal attacker origin
    (IdentityLogonEvents
     | where Timestamp > ago(timeRange)
     | where AccountUpn =~ targetUser
     | extend Source = "Identity (MDI)",
              Detail = strcat(ActionType, " | Device: ", DeviceName,
                              " | IP: ", IPAddress,
                              " | App: ", Application,
                              " | Protocol: ", Protocol,
                              " | Type: ", LogonType)
     | project Timestamp, Source, Detail),
    // AD directory queries: reconnaissance activity (LDAP enumeration)
    // Attackers query AD for Domain Admins, service accounts, group membership
    (IdentityQueryEvents
     | where Timestamp > ago(timeRange)
     | where AccountUpn =~ targetUser
     | extend Source = "AD Query (MDI)",
              Detail = strcat(ActionType, " | Query: ", QueryType,
                              " | Target: ", QueryTarget)
     | project Timestamp, Source, Detail),
    // Device process events: commands executed on endpoints by the user
    // Look for: PowerShell, cmd, suspicious LOLBins, encoded commands
    (DeviceProcessEvents
     | where Timestamp > ago(timeRange)
     | where AccountUpn =~ targetUser
         or InitiatingProcessAccountUpn =~ targetUser
     | extend Source = "Process (MDE)",
              Detail = strcat(FileName, " | Cmd: ",
                              substring(ProcessCommandLine, 0, 200),
                              " | Device: ", DeviceName)
     | project Timestamp, Source, Detail),
    // Device network events: outbound connections (C2 beaconing, data exfiltration)
    // RemoteUrl/RemoteIP show where data is going; RemotePort 443/80 = HTTPS/HTTP
    (DeviceNetworkEvents
     | where Timestamp > ago(timeRange)
     | where InitiatingProcessAccountUpn =~ targetUser
     | extend Source = "Network (MDE)",
              Detail = strcat(ActionType, " | Remote: ",
                              RemoteUrl, " (", RemoteIP, ":",
                              tostring(RemotePort), ")",
                              " | Process: ", InitiatingProcessFileName)
     | project Timestamp, Source, Detail),
    // Cloud app events: SaaS application usage (file access, downloads, sharing)
    // Large file downloads or sharing to external users indicates data exfiltration
    (CloudAppEvents
     | where Timestamp > ago(timeRange)
     | where AccountObjectId == targetUser
         or AccountDisplayName =~ targetUser
     | extend Source = "Cloud App (MDA)",
              Detail = strcat(ActionType, " | App: ", Application,
                              " | Object: ", ObjectName)
     | project Timestamp, Source, Detail)
| order by Timestamp asc

// ============================================================
// PRODUCTION-READY CUSTOM DETECTION RULE
// Purpose: Automated detection of phishing-to-endpoint compromise
//   This query is designed to run as a scheduled detection rule
//   (every hour) and automatically create alerts with entity mapping.
//
// MITRE ATT&CK: T1566 → T1078 → T1059
//   (Phishing → Valid Accounts → Command and Scripting Interpreter)
//
// Time windows explained:
//   lookback = 4h          – matches the rule frequency + buffer to avoid
//                            missing events between runs
//   phishToLogonWindow = 4h – max time between phishing delivery and logon
//   logonToExecWindow = 8h  – max time between logon and suspicious execution
//
// Entity mapping (configure in the portal when creating the rule):
//   AccountUpn  → User entity (enables automatic user investigation)
//   ExecDevice  → Device entity (enables one-click device isolation)
//   LogonIP     → IP entity (enables IP blocking and geo-lookup)
//
// Response actions to configure:
//   - Isolate device (ExecDevice) – network-isolates the compromised endpoint
//   - Force password reset (AccountUpn) – invalidates stolen credentials
//   - Soft delete email (NetworkMessageId) – removes phishing email from mailboxes
//
// IMPORTANT: Start with alert-only mode for 2 weeks to validate accuracy.
//   Only enable automated response after confirming low false positive rate.
// ============================================================
let lookback = 4h;  // Match the rule frequency + buffer
let phishToLogonWindow = 4h;
let logonToExecWindow = 8h;
let stage1 =
    EmailEvents
    | where Timestamp > ago(lookback)
    | where ThreatTypes has "Phish"
    | where DeliveryAction == "Delivered"
    | project PhishTime = Timestamp,
              TargetUser = RecipientEmailAddress,
              SenderFromAddress, Subject, NetworkMessageId;
let stage2 =
    IdentityLogonEvents
    | where Timestamp > ago(lookback)
    | where ActionType == "LogonSuccess"
    | project LogonTime = Timestamp, AccountUpn,
              LogonIP = IPAddress, LogonDevice = DeviceName,
              Application;
let stage3 =
    DeviceProcessEvents
    | where Timestamp > ago(lookback)
    | where FileName in~ ("powershell.exe", "cmd.exe",
        "wscript.exe", "cscript.exe", "mshta.exe")
    | where ProcessCommandLine has_any (
        "-enc", "FromBase64", "bypass", "Invoke-",
        "downloadstring", "http://", "https://")
    | project ExecTime = Timestamp, AccountUpn,
              ExecDevice = DeviceName, FileName,
              ProcessCommandLine;
stage1
| join kind=inner (stage2) on $left.TargetUser == $right.AccountUpn
| where LogonTime between (PhishTime .. (PhishTime + phishToLogonWindow))
| join kind=inner (stage3) on AccountUpn
| where ExecTime between (LogonTime .. (LogonTime + logonToExecWindow))
| project PhishTime, LogonTime, ExecTime, AccountUpn,
          SenderFromAddress, Subject, LogonIP, LogonDevice,
          ExecDevice, FileName, ProcessCommandLine,
          NetworkMessageId