MDO Threat Protection Detections trend over time

id: 02698387-5bf0-4111-a6ca-0f592a238a8f
name: MDO Threat Protection Detections trend over time
description: |
  Graph of MDO detections trended over time
description-detailed: |
  Graph of MDO detections trended over time in 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 TimeStart = startofday(ago(30d));
  let TimeEnd = startofday(now());
  let totalinbound = EmailEvents
  | where Timestamp >= TimeStart
  | where EmailDirection == "Inbound"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Inbound Emails";
  let totalintraorg = EmailEvents
  | where Timestamp >= TimeStart
  | where EmailDirection == "Intra-org"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Intra-org Emails";
  let totaloutbound = EmailEvents
  | where Timestamp >= TimeStart
  | where EmailDirection == "Outbound"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Outbound Emails";
  let totalwiththreat = EmailEvents
  | where Timestamp >= TimeStart
  | where isnotempty(ThreatTypes)
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Emails with Threat";
  let phishingcount = EmailEvents
  | where Timestamp >= TimeStart
  | where ThreatTypes has ('Phish')
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Emails Detected as Phish";
  let malwarecount = EmailEvents
  | where Timestamp >= TimeStart
  | where ThreatTypes has ('Malware')
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Emails Detected as Malware";
  let spamcount = EmailEvents
  | where Timestamp >= TimeStart
  | where ThreatTypes has ('Spam')
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Emails Detected as Spam";
  let zapcount = EmailPostDeliveryEvents
  | where Timestamp >= TimeStart
  | where ActionResult == "Success"
  | where ActionType == "Phish ZAP" or ActionType == "Malware ZAP"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Emails Removed by ZAP";
  let usersubmissioncount = CloudAppEvents
  | where Timestamp >= TimeStart
  | extend Record= (parse_json(RawEventData)).RecordType
  | extend SubmissionState = (parse_json(RawEventData)).SubmissionState
  | where Record == 29 | where ActionType == "UserSubmission"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Emails Reported by Users";
  let adminsubmissioncount = CloudAppEvents
  | where Timestamp >= TimeStart
  | extend Record= (parse_json(RawEventData)).RecordType
  | extend SubmissionState = (parse_json(RawEventData)).SubmissionState
  | where Record == 29
  | where ActionType == "AdminSubmission"
  | make-series Count= count() on Timestamp from TimeStart to TimeEnd step 1d
  | extend Details = "Total Emails Reported by Admins";
  union totalinbound, totalintraorg, totaloutbound, totalwiththreat, phishingcount, malwarecount, spamcount, zapcount,  usersubmissioncount, adminsubmissioncount
  | project Count, Details, Timestamp
  | render timechart
version: 1.0.0