Macro attachment opened from rare sender
Email Macro Attachment Opened From Rare Sender
Query
// Adjust the threshold based on your organisation.
let RareSenderThreshold = 10;
let LookupPeriod = 7d;
let MacroExtensions = dynamic(['xlsm', 'xstm', 'docm', 'dotm', 'pptm', 'ppsm', 'xll', 'xlsb']);
// If you also want to include older attachments use
// let MacroExtensions = dynamic(['xlsm', 'xstm', 'docm', 'dotm', 'pptm', 'ppsm', 'xll', 'xlsb', 'doc', 'xsl', 'svg']);
// Step 1
let RareMacroSenders = EmailAttachmentInfo
| where TimeGenerated > ago(30d)
// Extract the file extension for each filename
| extend FileExtension = tostring(extract(@'.*\.(.*)', 1, FileName))
// Remove empty file extensions and SHA256 hashes, they will otherwise cause a lot of false positives
| where isnotempty(FileExtension) and isnotempty(SHA256)
// Filter only on marco extensions
| where FileExtension in~ (MacroExtensions)
| summarize TotalMacroAttachmentsSend = dcount(NetworkMessageId) by SenderObjectId
// Filter on rare senders
| where TotalMacroAttachmentsSend < RareSenderThreshold
| project SenderObjectId;
// Step 2
let RecievedMacros = EmailAttachmentInfo
| where TimeGenerated > ago(LookupPeriod)
// Filter on rare senders. Senders that often user macro's are filtered.
| where SenderObjectId in (RareMacroSenders)
// Extract the file extension for each filename
| extend FileExtension = tostring(extract(@'.*\.(.*)', 1, FileName))
// Remove empty file extensions and SHA256 hashes, they will otherwise cause a lot of false positives
| where isnotempty(FileExtension) and isnotempty(SHA256)
// Filter only on marco extensions
| where FileExtension in~ (MacroExtensions)
| project SHA256;
// Step 3
DeviceFileEvents
| where ActionType == 'FileCreated'
// Search for devices that have FileEvents with macros recieved from emails.
| where SHA256 in (RecievedMacros)
| summarize TotalDevices = dcount(DeviceName), FileLocations = make_set(FolderPath) by SHA256
// Collect the email events, to enrich the results. Step 4
| join kind=inner (EmailAttachmentInfo | project RecipientEmailAddress, NetworkMessageId, SHA256) on $left.SHA256 == $right.SHA256
| join kind=inner (EmailEvents | project SenderFromAddress, Subject, NetworkMessageId, EmailDirection) on $left.NetworkMessageId == $right.NetworkMessageId
// Only search for inbound mail
| where EmailDirection == 'Inbound'
| summarize ['Targeted Mailboxes'] = make_set(RecipientEmailAddress) by SHA256, TotalDevices, tostring(FileLocations), Subject, SenderFromAddressAbout this query
Explanation
This query is designed to detect potentially malicious macro-enabled email attachments that are opened from senders who don't frequently send such attachments. Here's a simplified breakdown of what the query does:
-
Identify Rare Senders: It first identifies email senders who have sent fewer than 10 unique emails with macro-enabled attachments in the past 30 days. These are considered "rare senders."
-
Collect Macro Attachments: It then collects the SHA256 hashes of the macro-enabled attachments sent by these rare senders within the last 7 days.
-
Track File Events: The query searches for any file creation events on devices that match the collected SHA256 hashes. This helps identify if the macro attachments were opened on any devices.
-
Enrich with Email Information: Finally, it enriches the results with additional email information, such as the recipient's email address, the subject of the email, and the sender's address, but only for inbound emails.
The query is adjustable: you can change the threshold for what constitutes a "rare sender" and the time period for which you want to look back. It helps in identifying potential phishing attempts where attackers use macro-enabled files to gain access to a network. However, it may produce false positives if a new, legitimate sender sends macro attachments for the first time.
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators