KQL Search

Query Details

HomeAI ToolsDevice Query

Email Malware Detection List

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 : |
  //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

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.

Details

Kijo Girardi profile picture

Kijo Girardi

Released: April 3, 2023

Tables

EmailEventsEmailAttachmentInfo

Keywords

Email,EmailEvents,EmailAttachmentInfo,Timestamp,ThreatNames,ThreatName,ThreatNumber,bin,render,join,NetworkMessageId,ThreatFamilyAttachment,RecipientEmailAddress,Case,ThreatFamily_wih_Attachment

Operators

toscalar()arg_max()count()mv-expandwhereisnotempty()extendsplit()summarizerenderjoinstrcat()make_list()array_length()projectsort by

Actions