Total number of detections by MDO

id: e1903106-9b25-472e-b4b7-4bcc05328a48
name: Total number of detections by MDO
description: |
  Provides a summary of total number of detections
description-detailed: |
  Provides a summary of total number of detections by Defender for Office 365
  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
  - CloudAppEvents
  - EmailPostDeliveryEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let totalinbound = EmailEvents
  | where EmailDirection == "Inbound"
  | summarize Count = count()
  | extend Details = "Total Inbound Emails";
  let totalintraorg = EmailEvents
  | where EmailDirection == "Intra-org"
  | summarize Count = count()
  | extend Details = "Total Intra-org Emails";
  let totaloutbound = EmailEvents
  | where EmailDirection == "Outbound"
  | summarize Count = count()
  | extend Details = "Total Outbound Emails";
  let totalwiththreat = EmailEvents
  | where isnotempty(ThreatTypes)
  | summarize Count = count()
  | extend Details = "Total Emails with Threats";
  let phishingcount = EmailEvents
  | where ThreatTypes has ('Phish')
  | summarize Count= count()
  | extend Details = "Emails Detected as Phish";
  let malwarecount = EmailEvents
  | where ThreatTypes has ('Malware')
  | summarize Count= count()
  | extend Details = "Emails Detected as Malware";
  let spamcount = EmailEvents
  | where ThreatTypes has ('Spam')
  | summarize Count= count()
  | extend Details = "Emails Detected as Spam";
  let usersubmissioncount = CloudAppEvents
  | extend Record= (parse_json(RawEventData)).RecordType
  | extend SubmissionState = (parse_json(RawEventData)).SubmissionState
  | where Record == 29 | where ActionType == "UserSubmission"
  | summarize Count= count()
  | extend Details = "Total Emails Reported by Users";
  let adminsubmissioncount = CloudAppEvents
  | extend Record= (parse_json(RawEventData)).RecordType
  | extend SubmissionState = (parse_json(RawEventData)).SubmissionState
  | where Record == 29 | where ActionType == "AdminSubmission"
  | summarize Count= count()
  | extend Details = "Total Emails Reported by Admins";
  let zapcount = EmailPostDeliveryEvents
  | where ActionResult == "Success"
  | where ActionType == "Phish ZAP" or ActionType == "Malware ZAP"
  | summarize Count= count()
  | extend Details = "Total Emails Removed by ZAP";
  union totalinbound, phishingcount, malwarecount, spamcount, totalintraorg, totaloutbound, totalwiththreat, usersubmissioncount, zapcount, adminsubmissioncount
  | project Count, Details
  | order by Count desc
version: 1.0.0