Top Spoof external domain detections by Sender domain (P1/P2)

id: 09e1ca5e-cc4c-4b89-b585-448cfc1a6579
name: Top Spoof external domain detections by Sender domain (P1/P2)
description: |
  This query visualises total emails with Phish-Spoof-external domain detections summarizing the data by the top 10 email sender P2 domain (SenderFromDomain) and sender P1 domain (SenderMailFromDomain).
description-detailed: |
  This query visualises total emails with Phish-Spoof-external domain detections summarizing the data by the top 10 email sender P2 domain (SenderFromDomain) and sender P1 domain (SenderMailFromDomain). Adding additional insights for total inbound emails and Spoof external domain detection traffic percentage for each sender domain.
  Query is also included as part of the Defender for Office 365 solution in Sentinel: 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: |
  EmailEvents
  | where EmailDirection == "Inbound"
  | summarize TotalEmailCount = count(),
  SpoofExternalCount = countif(DetectionMethods has_any ('Phish":["Spoof external domain"]')) by P1Sender=SenderMailFromDomain, P2Sender=SenderFromDomain
  | extend SpoofExternal_Traffic_Percentage = todouble(round(SpoofExternalCount / todouble(TotalEmailCount) * 100, 2))
  | where SpoofExternalCount !=0
  | sort by SpoofExternalCount desc 
  | project P1Sender,P2Sender,SpoofExternalCount,TotalEmailCount,SpoofExternal_Traffic_Percentage
  | top 10 by SpoofExternalCount
version: 1.0.0