MDA-Hunt-Multi-Stage-Incident.kql

//Reference documentation for "The hunt in a multi-stage incident"
//https://techcommunity.microsoft.com/t5/microsoft-365-defender-blog/microsoft-cloud-app-security-the-hunt-in-a-multi-stage-incident/ba-p/2193484
//This queries are built to M365 Defender Advanced Hunting

//Basic user info
IdentityInfo | where AccountUpn =~ '<insert user identity/upn here>' 

//Sign-in activity by the user
let timeToSearch = startofday(datetime('2022-05-01')); 
AADSignInEventsBeta
| where AccountObjectId == '<user object id>' and Timestamp >= timeToSearch  
| distinct, Application, ResourceDisplayName, Country, City, IPAddress, DeviceName, DeviceTrustType, OSPlatform, IsManaged, IsCompliant, AuthenticationRequirement, RiskState, UserAgent, ClientAppUsed

//Summarize all the performed actions from the suspicious IP/location for that account. 
let accountId = '<user object id>'; 
let locations = pack_array('RO', 'HK'); 
let timeToSearch = startofday(datetime('2022-05-01')); 
CloudAppEvents 
    | where AccountObjectId == accountId and CountryCode in (locations) and Timestamp >= timeToSearch  
| summarize by ActionType, CountryCode, AccountObjectId  
| sort by ActionType asc 

//Review the accessed emails
let accountId = '<user object id>'; 
let locations = pack_array('RO', 'HK'); 
let timeToSearch = startofday(datetime('2022-05-01')); 
CloudAppEvents 
    | where ActionType == 'MailItemsAccessed' and CountryCode in (locations) and AccountObjectId == accountId and Timestamp >= timeToSearch 
    | mv-expand todynamic(RawEventData.Folders)  
    | extend Path = todynamic(RawEventData_Folders.Path), SessionId = tostring(RawEventData.SessionId) 
    | mv-expand todynamic(RawEventData_Folders.FolderItems) 
    | project SessionId, Timestamp, AccountObjectId, DeviceType, CountryCode, City, IPAddress, UserAgent, Path, Message = tostring(RawEventData_Folders_FolderItems.InternetMessageId) 
    | join kind=leftouter ( 
        EmailEvents  
        | where RecipientObjectId == accountId  
        | project Subject, RecipientEmailAddress , SenderMailFromAddress , DeliveryLocation , ThreatTypes, AttachmentCount , UrlCount , InternetMessageId  
        ) on $left.Message == $right.InternetMessageId  
| sort by Timestamp desc


//Review the accessed folders and files
let accountId = '<user object id>';
let locations = pack_array('RO', 'BY'); 
let timeToSearch = startofday(datetime('2022-05-01')); 
CloudAppEvents 
    | where ActionType == 'FilePreviewed' and CountryCode in (locations) and AccountObjectId == accountId and Timestamp >= timeToSearch 
    | project Timestamp, CountryCode , IPAddress , ISP, UserAgent , Application, ActivityObjects, AccountObjectId 
    | mv-expand ActivityObjects 
    | where ActivityObjects['Type'] in ('File', 'Folder')  
    | evaluate bag_unpack(ActivityObjects)