KQL Search

//Lookup Identity info table to find the most recent device a user has logged onto and any macro usage from that device and return identity info

//Data connector required for this query - M365 Defender - Device* tables
//Data connector required for this query - Microsoft Sentinel UEBA

let id=
    IdentityInfo
    | where TimeGenerated > ago (21d)
    | summarize arg_max(TimeGenerated, *) by AccountName
    | extend LoggedOnUser = AccountName
    | project LoggedOnUser, AccountUPN, JobTitle, EmployeeId, Country, City
    | join kind=inner (
        DeviceInfo
        | where TimeGenerated > ago (21d)
        | summarize arg_max(TimeGenerated, *) by DeviceName
        | extend LoggedOnUser = tostring(LoggedOnUsers[0].UserName)
        )
        on LoggedOnUser
    | project LoggedOnUser, AccountUPN, JobTitle, Country, DeviceName, EmployeeId;
DeviceProcessEvents
| join kind=inner id on DeviceName
| where TimeGenerated > ago (21d)
| where InitiatingProcessFileName == "EXCEL.EXE"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
| extend Process = InitiatingProcessFileName
| extend Command = InitiatingProcessCommandLine
| project
    TimeGenerated,
    DeviceName,
    LoggedOnUser,
    AccountUPN,
    Process,
    Command,
    JobTitle,
    EmployeeId,
    SHA1,
    SHA256