Email Top 15 Domains sending Spam with Additional Details

id: 279a8fce-0c34-437a-a6ba-eff7be3b36a8
name: Email Top 15 Domains sending Spam with Additional Details
description: |
  This query visualises total inbound emails with Spam detections summarizing the data by the top 15 email sender P2 domain (SenderFromDomain).
description-detailed: |
  This query visualises total inbound emails with Spam detections summarizing the data by the top 15 email sender P2 domain (SenderFromDomain). Adding additional insights for total inbound emails and bad traffic percentage for each sender domain.
  Taken from the the Microsoft Defender for Office 365 Detections and Insights - Microsoft Sentinel workbook.
  https://techcommunity.microsoft.com/blog/microsoftdefenderforoffice365blog/part-2-build-custom-email-security-reports-and-dashboards-with-workbooks-in-micr/4411303
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - EmailEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  //This query visualises total inbound emails with Spam detections summarizing the data by the top 15 email sender P2 domain (SenderFromDomain). Adding additional insights for total inbound emails and bad traffic percentage for each sender domain.
  EmailEvents
  | where EmailDirection == "Inbound"
  | where Timestamp > ago(30d) // last 30 days by default, replace 30d with the desired period
  | summarize TotalEmailCount = count(),
            SpamEmailCount = countif(ThreatTypes has "Spam") by SenderFromDomain
  | extend Bad_Traffic_Percentage_Inbound = todouble(round(SpamEmailCount / todouble(TotalEmailCount) * 100, 2))
  | where SpamEmailCount !=0
  | sort by SpamEmailCount desc 
  | project SenderFromDomain,SpamEmailCount,TotalEmailCount,Bad_Traffic_Percentage_Inbound
  | top 15 by SpamEmailCount
version: 1.0.0