Email Malware Detection List

Query

//case1 - Email, Weekly malware detection with linechart
EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(ThreatNames)
| extend ThreatName = split(ThreatNames, ",")
| mv-expand ThreatName
| summarize ThreatNumber = count() by tostring(ThreatName), bin(Timestamp, 1d)
| render linechart 


//case2 - Email, Weekly malware detection user list with NetworkId
EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(ThreatNames)
| join kind=inner EmailAttachmentInfo on NetworkMessageId 
| extend ThreatFamilyAttachment = strcat(ThreatNames, @"\", FileName, @"\", NetworkMessageId)
| summarize ThreatFamily_wih_Attachment= make_list(ThreatFamilyAttachment) by RecipientEmailAddress
| extend Case = array_length(ThreatFamily_wih_Attachment)
| project RecipientEmailAddress, Case, ThreatFamily_wih_Attachment 
| sort by Case desc

About this query

name : Email Malware Detection list description : Email, weekly malware detection list table :

EmailEvents
https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-emailevents-table?view=o365-worldwide
EmailAttachmentInfo
https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-emailattachmentinfo-table?view=o365-worldwide query : |

Explanation

The query is used to detect malware in emails and generate a weekly report.

In the first case, it creates a line chart showing the number of malware threats detected in emails over the past week.

In the second case, it generates a list of users who received emails with malware attachments, along with the associated threat information. The list is sorted by the number of malware cases each user has.