Detections by detection methods

id: 1c51e10e-7f77-40bc-bd37-6aa55cdf94d6
name: Detections by detection methods
description: |
  This query helps reviewing malicious email detections by detection methods
description-detailed: |
  This query helps reviewing malicious email detections by detection methods in Defender for Office 365
  Reference - https://learn.microsoft.com/en-us/defender-office-365/step-by-step-guides/understand-detection-technology-in-email-entity
requiredDataConnectors:
  - connectorId: MicrosoftThreatProtection
    dataTypes:
      - EmailEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  EmailEvents
  | where Timestamp > ago(7d)
  | where isnotempty(DetectionMethods)
  | extend MDO_detection = parse_json(DetectionMethods)
  | summarize TotalEmailCount = count(),
              Phish_detection = countif(isnotempty(MDO_detection.Phish)),
              Malware_detection = countif(isnotempty(MDO_detection.Malware)),
              Spam_detection = countif(isnotempty( MDO_detection.Spam)),
              URL_malicious_reputation = countif(MDO_detection.Phish == @'["URL malicious reputation"]' or MDO_detection.Malware == @'["URL malicious reputation"]'),
              URL_detonation_reputation = countif(MDO_detection.Phish == @'["URL detonation reputation"]' or MDO_detection.Malware == @'["URL detonation reputation"]'),
              URL_detonation = countif(MDO_detection.Phish == @'["URL detonation"]' or MDO_detection.Malware == @'["URL detonation"]'),
              Advanced_filter = countif(MDO_detection.Phish == @'["Advanced filter"]'),
              General_filter = countif(MDO_detection.Phish == @'["General filter"]'),
              Spoof_intra_org = countif(MDO_detection.Phish == @'["Spoof intra-org"]'),
              Spoof_external_domain = countif(MDO_detection.Phish ==  @'["Spoof external domain"]'),
              Spoof_DMARC = countif(MDO_detection.Phish == @'["Spoof DMARC"]'),
              Impersonation_brand = countif(MDO_detection.Phish == @'["Impersonation brand"]'),
              Impersonation_user = countif(MDO_detection.Phish == @'["Impersonation user"]'),
              Impersonation_domain = countif(MDO_detection.Phish == @'["Impersonation domain"]'),
              Mixed_analysis_detection= countif(MDO_detection.Phish == @'["Mixed analysis detection"]'),
              File_reputation = countif(MDO_detection.Phish == @'["File reputation"]' or MDO_detection.Malware == @'["File reputation"]'),
              File_detonation = countif(MDO_detection.Phish == @'["File detonation"]' or MDO_detection.Malware == @'["File detonation"]'),
              File_detonation_reputation = countif(MDO_detection.Phish == @'["File detonation reputation"]' or MDO_detection.Malware == @'["File detonation reputation"]'),
              Antimalware_engine = countif(MDO_detection.Malware == @'["Antimalware engine"]'),
              Fingerprint_matching = countif(MDO_detection.Phish == @'["Fingerprint matching"]'), 
              Mailbox_intelligence_impersonation = countif(MDO_detection.Phish == @'["Mailbox intelligence impersonation"]'),
              Campaign = countif(MDO_detection.Phish == @'["Campaign"]' or MDO_detection.Malware == @'["Campaign"]') by bin(Timestamp, 1d)
  | project Timestamp, TotalEmailCount, Phish_detection, Malware_detection, Spam_detection,URL_malicious_reputation,URL_detonation_reputation ,URL_detonation,Advanced_filter, General_filter,Spoof_intra_org,Spoof_external_domain,Spoof_DMARC,Impersonation_brand,Impersonation_user,Impersonation_domain,
  Mixed_analysis_detection,File_reputation,File_detonation,File_detonation_reputation,Antimalware_engine,Fingerprint_matching,Mailbox_intelligence_impersonation,Campaign
version: 1.0.0