Automated Remediation Efficiency Over Time

id: e189343d-f3f1-4d2d-a82e-29b4619d9091
name: Automated Remediation Efficiency Over Time
description: |
  This query trends the outcome (success versus failure) of Microsoft Defender for Office 365 automated remediation actions over time, using the EmailPostDeliveryEvents table.
description-detailed: |
  Automated Investigation and Response (AIR) can remediate malicious mail after delivery. This query counts automated remediation events by result over time, deduplicated to the latest event per message and recipient, so SOC teams can see whether automated remediation is succeeding and spot spikes in failures that warrant attention.
  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:
  - EmailPostDeliveryEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  EmailPostDeliveryEvents
  | where Timestamp > ago(30d)
  | where ActionType == "Automated Remediation"
  | extend Key = strcat(NetworkMessageId, '-', RecipientEmailAddress)
  | summarize arg_max(Timestamp, *) by Key
  | summarize Count = count() by bin(Timestamp, 1d), ActionResult
  | render timechart
version: 1.0.0