Query Details
MDO Email Attachment File Extensions
Query
# Defender for Office - Email File Attachment File Extensions
## Query Information
### Description
Use the below queries to retireve information about email attachment formats / legacy formats.
#### References
#### Credits
thanks to [Gianni](https://twitter.com/castello_johnny) for the dotcount method to identify the file extension
### Microsoft 365 Defender
Email attachment file extension overview
```kql
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where DeliveryAction != 'Delivered'
| where DotCount > 0
| project FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes
| summarize Out = countif(EmailDirection == 'Outbound'),In = countif(EmailDirection == 'Inbound'), Intra = countif(EmailDirection == 'Intra-org'), Unknown = countif(EmailDirection == 'Unknown') by FileExtension
| sort by FileExtension
```
Email attachment file extension details
```kql
let FileExt = ".ISO";
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension == FileExt
| where DeliveryAction != 'Delivered'
| where DotCount > 0
| project Timestamp, FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes, NetworkMessageId
```
legacy office file formats
```kql
let legacyofficeformats = dynamic([".doc",".dot",".ppt",".pot",".ppa","pps",".xls",".xla",".xlt",".xlw",".mdb"]);
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension in (legacyofficeformats)
| where DeliveryAction != 'Delivered'
| where DotCount > 0
| project FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes
| summarize Out = countif(EmailDirection == 'Outbound'),In = countif(EmailDirection == 'Inbound'), Intra = countif(EmailDirection == 'Intra-org'), Unknown = countif(EmailDirection == 'Unknown') by FileExtension
```
legacy office file formats
```kql
let legacyofficeformats = dynamic([".doc",".dot",".ppt",".pot",".ppa","pps",".xls",".xla",".xlt",".xlw",".mdb"]);
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension in (legacyofficeformats)
| where DeliveryAction != 'Delivered'
| where DotCount > 0
| project Timestamp, FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes, NetworkMessageId
```
Email attachment file extension details
```kql
let legacyofficeformats = dynamic([".doc",".dot",".ppt",".pot",".ppa","pps",".xls",".xla",".xlt",".xlw",".mdb"]);
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension in (legacyofficeformats)
| where DeliveryAction != 'Delivered'
| where DotCount > 0
| where EmailDirection == 'Inbound'
| project Timestamp, FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes, DeliveryAction, DeliveryLocation, DetectionMethods, EmailAction, ThreatNames, NetworkMessageId
| join kind=leftouter EmailPostDeliveryEvents
on $left. NetworkMessageId == $right.NetworkMessageId
```
Email attachment file extension details
```kql
let legacyofficeformats = dynamic([".doc",".dot",".ppt",".pot",".ppa","pps",".xls",".xla",".xlt",".xlw",".mdb"]);
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension in (legacyofficeformats)
| where DotCount > 0
| where EmailDirection == 'Inbound'
| project Timestamp, RecipientEmailAddress, SenderFromAddress, SenderMailFromAddress, SenderFromDomain, FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes, DeliveryAction, DeliveryLocation, DetectionMethods, EmailAction, ThreatNames, NetworkMessageId
| summarize count() by DeliveryLocation
| render piechart
```
file formats blocked configured in policy
```kql
let legacyofficeformats = dynamic([".ace",".ani",".app",".docm",".exe","jar",".reg",".scr",".vbe",".vbs"]);
EmailEvents
| join EmailAttachmentInfo
on $left. NetworkMessageId == $right.NetworkMessageId
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])
| where FileExtension in (legacyofficeformats)
| where DotCount > 0
| where EmailDirection == 'Inbound'
| project Timestamp, RecipientEmailAddress, SenderFromAddress, SenderMailFromAddress, SenderFromDomain, FileName, FileExtension, FileType, DotCount, EmailDirection, ThreatTypes, DeliveryAction, DeliveryLocation, DetectionMethods, EmailAction, ThreatNames, NetworkMessageId
// | summarize count() by DeliveryLocation
// | render piechart
```
legacy files on sharepoint , onedrive
```kql
let legacyofficeformats = dynamic([".doc",".dot",".ppt",".pot",".ppa","pps",".xls",".xla",".xlt",".xlw",".mdb"]);
CloudAppEvents
| where ActionType startswith "File"
| where ObjectType == @"File"
| where ActionType == @"FileUploaded" or ActionType == @"FileDownloaded"
| extend FileLocation = ActivityObjects[0].Name
| extend FileName = tostring(RawEventData.SourceFileName)
| extend DotCount = countof(FileName,".")
| extend FileExtension = strcat(".", split(FileName,".",DotCount)[0])| where FileExtension in (legacyofficeformats)
| summarize count() by FileExtension
```
Device File Creation events
```kql
let legacyofficeformats = dynamic(["doc","dot","ppt","pot","ppa","pps","xls","xla","xlt","xlw","mdb"]);
DeviceFileEvents
| extend FileInfo = parse_path(FolderPath)
| extend FileExtension = FileInfo.Extension
| extend Folder = FileInfo.DirectoryPath
| where FileExtension in (legacyofficeformats)
| where ActionType == @"FileCreated"
| project Timestamp, DeviceName,FileName,FileExtension,Folder, InitiatingProcessFileName, InitiatingProcessVersionInfoProductName,InitiatingProcessAccountUpn, InitiatingProcessAccountName
```
// filter results - only office apps
// Device File Creation events
```kql
let legacyofficeformats = dynamic(["doc","dot","ppt","pot","ppa","pps","xls","xla","xlt","xlw","mdb"]);
let officeapps = dynamic(["WINWORD.EXE","EXCEL.EXE","POWERPNT.EXE"]);
DeviceFileEvents
| extend FileInfo = parse_path(FolderPath)
| extend FileExtension = FileInfo.Extension
| extend Folder = FileInfo.DirectoryPath
| where FileExtension in (legacyofficeformats)
| where ActionType == @"FileCreated"
| project Timestamp, DeviceName,FileName,FileExtension,Folder, InitiatingProcessFileName, InitiatingProcessVersionInfoProductName,InitiatingProcessAccountUpn, InitiatingProcessAccountName
| where InitiatingProcessVersionInfoProductName != @"Commvault"
| where InitiatingProcessFileName != @"System"
| where InitiatingProcessFileName != @"msiexec.exe"| where InitiatingProcessFileName != @"explorer.exe"
| where InitiatingProcessFileName in (officeapps)
```
Explanation
The queries provided retrieve information about email attachment formats and legacy formats.
The first query provides an overview of email attachment file extensions, including the count of outbound, inbound, intra-org, and unknown email directions for each file extension.
The second query provides details about email attachment file extensions for a specific file extension (specified in the "FileExt" variable). It includes information such as the timestamp, file name, file extension, file type, email direction, threat types, and network message ID.
The third and fourth queries focus on legacy office file formats. They retrieve information about email attachments with file extensions that belong to a predefined list of legacy office formats. The third query provides an overview of the count of outbound, inbound, intra-org, and unknown email directions for each legacy office file extension. The fourth query provides details about the email attachments, including the timestamp, file name, file extension, file type, email direction, threat types, delivery action, delivery location, detection methods, email action, threat names, and network message ID.
The fifth query retrieves details about email attachment file extensions for legacy office formats, specifically for inbound emails. It includes additional information such as the recipient email address, sender information, delivery action, delivery location, detection methods, email action, threat names, and network message ID. It also performs a left outer join with EmailPostDeliveryEvents.
The sixth query provides details about email attachment file extensions for legacy office formats, specifically for inbound emails. It includes additional information such as the recipient email address, sender information, delivery action, delivery location, detection methods, email action, threat names, and network message ID. It also summarizes the count of events by delivery location and renders a pie chart.
The seventh query focuses on file formats blocked by the policy. It retrieves details about email attachment file extensions for a predefined list of legacy office formats. It includes information such as the timestamp, recipient email address, sender information, file name, file extension, file type, email direction, threat types, delivery action, delivery location, detection methods, email action, threat names, and network message ID.
The eighth query retrieves details about legacy office files on SharePoint and OneDrive. It includes information such as the file extension and the count of files for each legacy office file extension.
The ninth query focuses on device file creation events. It retrieves details about files with legacy office file extensions that were created on devices. It includes information such as the timestamp, device name, file name, file extension, folder, initiating process file name, initiating process version info product name, initiating process account UPN, and initiating process account name.
The tenth query is a filtered version of the ninth query, specifically focusing on device file creation events related to office apps. It retrieves details about files with legacy office file extensions that were created on devices by office apps. It includes information such as the timestamp, device name, file name, file extension, folder, initiating process file name, initiating process version info product name, initiating process account UPN, and initiating process account name. It filters out certain processes like "Commvault," "System," "msiexec.exe," and "explorer.exe" and only includes files created by specific office apps (specified in the "officeapps" variable).
Details
Alex Verboon
Released: November 23, 2023
Tables
Keywords
Operators