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

id: 633f848f-bb17-4813-81b4-098d277b00f7
name: Top Spoof DMARC detections by Sender domain (P1/P2)
description: |
  This query visualises total emails with Spoof-DMARC fails detections summarizing the data by the top email sender P2 domain (SenderFromDomain) and sender P1 domain (SenderMailFromDomain).
description-detailed: |
  This query visualises total emails with Spoof-DMARC fails detections summarizing the data by the top email sender P2 domain (SenderFromDomain) and sender P1 domain (SenderMailFromDomain). Adding additional insights for total inbound emails and DMARC fail 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(),
  DMARCFailCount = countif(DetectionMethods has_any ('Phish":["Spoof DMARC"]')) by P1Sender=SenderMailFromDomain, P2Sender=SenderFromDomain
  | extend DMARCFail_Traffic_Percentage = todouble(round(DMARCFailCount / todouble(TotalEmailCount) * 100, 2))
  | where DMARCFailCount !=0
  | sort by DMARCFailCount desc 
  | project P1Sender,P2Sender,DMARCFailCount,TotalEmailCount,DMARCFail_Traffic_Percentage
  | top 10 by DMARCFailCount
version: 1.0.0