KQL Search

Query Details

HomeAI ToolsDevice Query

Email Events Macro Receivedby Email

Query

//When a macro is received via email from an external sender, find all users who receieved the same file

//Data connector required for this query - M365 Defender - Email* tables

//Microsoft Sentinel query
EmailEvents
| where EmailDirection == "Inbound"
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    NetworkMessageId
| join kind=inner (EmailAttachmentInfo) on NetworkMessageId
| project
    TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    FileName
| where FileName endswith ".xlsm" or FileName endswith ".xstm"
| summarize Recipient=make_set(RecipientEmailAddress) by FileName, SenderMailFromAddress

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

EmailEvents
| where EmailDirection == "Inbound"
| project
    Timestamp,
    SenderMailFromAddress,
    RecipientEmailAddress,
    NetworkMessageId
| join kind=inner (EmailAttachmentInfo) on NetworkMessageId
| project
    Timestamp,
    SenderMailFromAddress,
    RecipientEmailAddress,
    FileName
| where FileName endswith ".xlsm" or FileName endswith ".xstm"
| summarize Recipient=make_set(RecipientEmailAddress) by FileName, SenderMailFromAddress

Explanation

This query is used to find all users who received the same file when a macro is received via email from an external sender. It uses the M365 Defender - Email* tables as the data connector. The query filters for inbound emails, retrieves the time generated, sender email address, recipient email address, and network message ID. It then joins with the EmailAttachmentInfo table on the network message ID to retrieve the file name. The query further filters for files with the extensions ".xlsm" or ".xstm". Finally, it summarizes the results by creating a set of recipient email addresses grouped by the file name and sender email address.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

EmailEventsEmailAttachmentInfo

Keywords

EmailEvents,EmailDirection,TimeGenerated,SenderMailFromAddress,RecipientEmailAddress,NetworkMessageId,EmailAttachmentInfo,FileName,make_set

Operators

EmailEventswhereEmailDirection=="Inbound"projectTimeGeneratedSenderMailFromAddressRecipientEmailAddressNetworkMessageIdjoinkind=inner(EmailAttachmentInfo)onNetworkMessageIdwhereFileNameendswith".xlsm"orFileNameendswith".xstm"summarizeRecipient=make_set(RecipientEmailAddress)byFileNameSenderMailFromAddressTimestamp

Actions