Calculate overall MDO efficacy

Description

This query helps calculate overall efficacy of MDO based on threats blocked pre-delivery, post-delivery cleanups, or were uncaught.

Query · kql

let _startTime = ago(30d); 
let _endTime = now(); 
// Get all mailflow detected as clean at time of delivery 
let EmailEventsClean = materialize( 
    EmailEvents 
    | where Timestamp between (_startTime .. _endTime) and EmailDirection == "Inbound" 
    | where ThreatTypes !contains "Phish" and ThreatTypes !contains "Malware" 
    | project NetworkMessageId,ThreatTypes 
); 
// Get all mailflow detected as phish or malware at time of delivery 
let EmailEventsThreats = materialize( 
    EmailEvents 
    | where Timestamp between (_startTime .. _endTime) and EmailDirection == "Inbound" 
    | where ThreatTypes contains "Phish" or ThreatTypes contains "Malware" 
    | extend MDO_detection = parse_json(DetectionMethods) 
    | extend FirstDetection = iif(isempty(MDO_detection), "Clean", tostring(bag_keys(MDO_detection)[0])) 
    | extend FirstSubcategory = iif(FirstDetection != "Clean" and array_length(MDO_detection[FirstDetection]) > 0, strcat(FirstDetection, ": ", tostring(MDO_detection[FirstDetection][0])), "No Detection (clean)") 
    | project NetworkMessageId,FirstDetection,FirstSubcategory,MDO_detection,ThreatTypes 
); 
// Get all post delivery ZAP / Redelivery events, and arg_max them to ensure we have the latest verdict to work with for each
let EmailPostDeliveryFiltered = materialize( 
    EmailPostDeliveryEvents 
    | where Timestamp between (_startTime .. datetime_add('day', 7, _endTime)) 
    | where ActionType in ("Malware ZAP","Phish ZAP","Redelivery") 
    | extend Key = strcat(NetworkMessageId , "-", RecipientEmailAddress) 
    | summarize arg_max(Timestamp, *) by Key 
    | project Action,ActionType,ActionResult,ThreatTypes,NetworkMessageId 
); 
// Optional - Get all admin submissions for malware or phish, so we can also count these in the miss bucket. 
let CloudAppEventsFiltered = materialize( 
    CloudAppEvents 
    | where Timestamp between (_startTime .. datetime_add('day', 7, _endTime)) 
    | where ActionType == "AdminSubmissionSubmitted" 
    | extend SubmissionType = tostring(parse_json(RawEventData).SubmissionType) 
    | extend NetworkMessageId = tostring(parse_json(RawEventData).ObjectId) 
    | where SubmissionType in ("1", "2") 
    | project SubmissionType,NetworkMessageId 
); 
// Get the number of threats caught in mailflow 
let Mal_Phish_Mailflow = toscalar( 
    EmailEventsThreats 
    | summarize count() 
); 
// Get the number of threats caught in mailflow which turned out to be false positives (FPs) so we can correct the calculation 
let FP_ZAP = toscalar( 
    EmailPostDeliveryFiltered 
    | where ThreatTypes !contains "Phish" and ThreatTypes !contains "Malware" and ActionType == "Redelivery" 
    | join kind=leftsemi (EmailEventsThreats) on NetworkMessageId 
    | summarize count() 
); 
// Get the number of threats successfully cleaned up post delivery, ignoring where administrative policy stopped action 
let FN_ZAP_Successful = toscalar( 
    EmailPostDeliveryFiltered 
    | where ActionType in ("Malware ZAP","Phish ZAP") and ActionResult in ("Success","AdminPolicy") 
    | join kind=leftsemi (EmailEventsClean) on NetworkMessageId 
    | summarize count() 
); 
// Get the number of threats unsuccessfully cleaned up post delivery. 
let FN_ZAP_Unsuccessful = toscalar( 
    EmailPostDeliveryFiltered 
    | where ActionType in ("Malware ZAP","Phish ZAP") and ActionResult !in ("Success","AdminPolicy") 
    | join kind=leftsemi (EmailEventsClean) on NetworkMessageId 
    | summarize count() 
); 
// Join the admin submissions to clean mailflow to find the additional miss 
let FN_Admin_Submissions = toscalar( 
    CloudAppEventsFiltered 
    | join kind=rightsemi (EmailEventsClean) on NetworkMessageId 
    | summarize count() 
    ); 
// Print each result, and run the formula to calculate effectiveness at time of delivery and post delivery. 
union withsource=Table 
    (print StatisticName="Mal/Phish Mailflow totals - Minus FPs", Value=toreal(Mal_Phish_Mailflow) - toreal(FP_ZAP)), 
    (print StatisticName="Admin Mal/Phish FNs Submitted", Value=toreal(FN_Admin_Submissions)), 
    (print StatisticName="Mal/Phish FPs Reverse Zapped", Value=toreal(FP_ZAP)), 
    (print StatisticName="Mal/Phish Successfully Zapped", Value=toreal(FN_ZAP_Successful)), 
    (print StatisticName="Mal/Phish UN-Successfully Zapped", Value=toreal(FN_ZAP_Unsuccessful)), 
    (print StatisticName="Effectiveness Post Delivery", Value=abs(round(((toreal(FN_Admin_Submissions)+toreal(FN_ZAP_Unsuccessful))/(toreal(Mal_Phish_Mailflow)+toreal(FN_ZAP_Successful)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_Admin_Submissions)-toreal(FP_ZAP))*100-100),2))), 
    (print StatisticName="Effectiveness Pre-Delivery", Value=abs(round(((toreal(FN_Admin_Submissions)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_ZAP_Successful))/(toreal(Mal_Phish_Mailflow)+toreal(FN_ZAP_Successful)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_Admin_Submissions)-toreal(FP_ZAP))*100-100),2)))
| project StatisticName, Value
Raw source Calculate overall MDO efficacy · KQL
Esc
Published by Azure/Azure-Sentinel ↗, licensed under MIT ↗. Reproduced here unmodified. 
id: f2206cb7-62ca-4596-9d3a-544b61963799
name: Calculate overall MDO efficacy
description: |
  This query helps calculate overall efficacy of MDO based on threats blocked pre-delivery, post-delivery cleanups, or were uncaught.
description-detailed: |
  This query helps calculate overall efficacy of MDO based on threats blocked pre-delivery, items that were removed after delivery to the mailbox, or were uncaught because they were already deleted by an Admin/User etc.
  Based on the query used within the Overview dashboard. Reference - https://learn.microsoft.com/en-gb/defender-office-365/reports-mdo-email-collaboration-dashboard#appendix-advanced-hunting-efficacy-query-in-defender-for-office-365-plan-2
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - EmailEvents
tactics:
- InitialAccess
relevantTechniques:
  - T1566
query: |
  let _startTime = ago(30d); 
  let _endTime = now(); 
  // Get all mailflow detected as clean at time of delivery 
  let EmailEventsClean = materialize( 
      EmailEvents 
      | where Timestamp between (_startTime .. _endTime) and EmailDirection == "Inbound" 
      | where ThreatTypes !contains "Phish" and ThreatTypes !contains "Malware" 
      | project NetworkMessageId,ThreatTypes 
  ); 
  // Get all mailflow detected as phish or malware at time of delivery 
  let EmailEventsThreats = materialize( 
      EmailEvents 
      | where Timestamp between (_startTime .. _endTime) and EmailDirection == "Inbound" 
      | where ThreatTypes contains "Phish" or ThreatTypes contains "Malware" 
      | extend MDO_detection = parse_json(DetectionMethods) 
      | extend FirstDetection = iif(isempty(MDO_detection), "Clean", tostring(bag_keys(MDO_detection)[0])) 
      | extend FirstSubcategory = iif(FirstDetection != "Clean" and array_length(MDO_detection[FirstDetection]) > 0, strcat(FirstDetection, ": ", tostring(MDO_detection[FirstDetection][0])), "No Detection (clean)") 
      | project NetworkMessageId,FirstDetection,FirstSubcategory,MDO_detection,ThreatTypes 
  ); 
  // Get all post delivery ZAP / Redelivery events, and arg_max them to ensure we have the latest verdict to work with for each
  let EmailPostDeliveryFiltered = materialize( 
      EmailPostDeliveryEvents 
      | where Timestamp between (_startTime .. datetime_add('day', 7, _endTime)) 
      | where ActionType in ("Malware ZAP","Phish ZAP","Redelivery") 
      | extend Key = strcat(NetworkMessageId , "-", RecipientEmailAddress) 
      | summarize arg_max(Timestamp, *) by Key 
      | project Action,ActionType,ActionResult,ThreatTypes,NetworkMessageId 
  ); 
  // Optional - Get all admin submissions for malware or phish, so we can also count these in the miss bucket. 
  let CloudAppEventsFiltered = materialize( 
      CloudAppEvents 
      | where Timestamp between (_startTime .. datetime_add('day', 7, _endTime)) 
      | where ActionType == "AdminSubmissionSubmitted" 
      | extend SubmissionType = tostring(parse_json(RawEventData).SubmissionType) 
      | extend NetworkMessageId = tostring(parse_json(RawEventData).ObjectId) 
      | where SubmissionType in ("1", "2") 
      | project SubmissionType,NetworkMessageId 
  ); 
  // Get the number of threats caught in mailflow 
  let Mal_Phish_Mailflow = toscalar( 
      EmailEventsThreats 
      | summarize count() 
  ); 
  // Get the number of threats caught in mailflow which turned out to be false positives (FPs) so we can correct the calculation 
  let FP_ZAP = toscalar( 
      EmailPostDeliveryFiltered 
      | where ThreatTypes !contains "Phish" and ThreatTypes !contains "Malware" and ActionType == "Redelivery" 
      | join kind=leftsemi (EmailEventsThreats) on NetworkMessageId 
      | summarize count() 
  ); 
  // Get the number of threats successfully cleaned up post delivery, ignoring where administrative policy stopped action 
  let FN_ZAP_Successful = toscalar( 
      EmailPostDeliveryFiltered 
      | where ActionType in ("Malware ZAP","Phish ZAP") and ActionResult in ("Success","AdminPolicy") 
      | join kind=leftsemi (EmailEventsClean) on NetworkMessageId 
      | summarize count() 
  ); 
  // Get the number of threats unsuccessfully cleaned up post delivery. 
  let FN_ZAP_Unsuccessful = toscalar( 
      EmailPostDeliveryFiltered 
      | where ActionType in ("Malware ZAP","Phish ZAP") and ActionResult !in ("Success","AdminPolicy") 
      | join kind=leftsemi (EmailEventsClean) on NetworkMessageId 
      | summarize count() 
  ); 
  // Join the admin submissions to clean mailflow to find the additional miss 
  let FN_Admin_Submissions = toscalar( 
      CloudAppEventsFiltered 
      | join kind=rightsemi (EmailEventsClean) on NetworkMessageId 
      | summarize count() 
      ); 
  // Print each result, and run the formula to calculate effectiveness at time of delivery and post delivery. 
  union withsource=Table 
      (print StatisticName="Mal/Phish Mailflow totals - Minus FPs", Value=toreal(Mal_Phish_Mailflow) - toreal(FP_ZAP)), 
      (print StatisticName="Admin Mal/Phish FNs Submitted", Value=toreal(FN_Admin_Submissions)), 
      (print StatisticName="Mal/Phish FPs Reverse Zapped", Value=toreal(FP_ZAP)), 
      (print StatisticName="Mal/Phish Successfully Zapped", Value=toreal(FN_ZAP_Successful)), 
      (print StatisticName="Mal/Phish UN-Successfully Zapped", Value=toreal(FN_ZAP_Unsuccessful)), 
      (print StatisticName="Effectiveness Post Delivery", Value=abs(round(((toreal(FN_Admin_Submissions)+toreal(FN_ZAP_Unsuccessful))/(toreal(Mal_Phish_Mailflow)+toreal(FN_ZAP_Successful)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_Admin_Submissions)-toreal(FP_ZAP))*100-100),2))), 
      (print StatisticName="Effectiveness Pre-Delivery", Value=abs(round(((toreal(FN_Admin_Submissions)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_ZAP_Successful))/(toreal(Mal_Phish_Mailflow)+toreal(FN_ZAP_Successful)+toreal(FN_ZAP_Unsuccessful)+toreal(FN_Admin_Submissions)-toreal(FP_ZAP))*100-100),2)))
  | project StatisticName, Value
version: 1.0.0

Detection rules belong to the projects that publish them and remain under their own licenses. This site indexes and links to them; it claims no rights in them.