Adversaries may use phishing emails to compromise user accounts, which this rule detects by tracking the volume of emails and those identified by Defender for Office 365. SOC teams should proactively hunt for this behavior to identify potential phishing campaigns and mitigate account compromise risks in their Azure Sentinel environment.
KQL Query
let QueryTime = 30d;
let Reports = CloudAppEvents
| where Timestamp > ago(QueryTime)
| where ActionType == "UserSubmission" or ActionType == "AdminSubmission"
| extend MessageDate = todatetime((parse_json(RawEventData)).MessageDate)
| extend NetworkMessageID = tostring((parse_json(RawEventData)).ObjectId)
| extend Date_value = tostring(format_datetime( MessageDate, "yyyy-MM-dd"))
| distinct Date_value,NetworkMessageID
| summarize count() by Date_value
| project Date_value, MessagesGotReported=count_;
let ThreatByAutomation = (AlertEvidence | where Title == "Email reported by user as malware or phish")
| extend LastVerdictfromAutomation = tostring((parse_json(AdditionalFields)).LastVerdict)
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd"))
| extend DetectionFromAIR = iif(isempty(LastVerdictfromAutomation), "NoThreatsFound", tostring(LastVerdictfromAutomation))
| summarize PostDeliveryTotalAIRInvestigations = count(),
PostDeliveryAirNoThreatsFound = countif(DetectionFromAIR contains "NoThreatsFound"),
PostDeliveryAirSuspicious = countif(DetectionFromAIR contains "Suspicious"),
PostDeliveryAirMalicious = countif(DetectionFromAIR contains "Malicious")
by Date_value //Date Reported from Message Submissions from CloudAppEvents does not match to the AIR Investigations from Alert playbooks
| project Date_value, PostDeliveryTotalAIRInvestigations, PostDeliveryAirNoThreatsFound, PostDeliveryAirSuspicious, PostDeliveryAirMalicious;
let DeliveryInboundEvents = (EmailEvents | where EmailDirection == "Inbound" and Timestamp > ago(QueryTime)
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd"))
| project Date_value, Timestamp, NetworkMessageId, DetectionMethods ,RecipientEmailAddress);
let PostDeliveryEvents = (EmailPostDeliveryEvents | where ActionType contains "ZAP" and ActionResult == "Success"| join DeliveryInboundEvents on RecipientEmailAddress, NetworkMessageId //Only successful ZAP Events, there could still be more, join on Recipient and NetID
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd")) //Zap Timestamp is used and not MessageDate received
| summarize PostDeliveryZAP=count() by Date_value);
let DeliveryByThreat = (DeliveryInboundEvents
| where Timestamp > ago(QueryTime)
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd"))
| 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)"))
| summarize TotalEmails = count(),
Clean = countif(FirstSubcategory contains "Clean"),
Malware = countif(FirstSubcategory contains "Malware"),
Phish = countif(FirstSubcategory contains "Phish"),
Spam = countif(FirstSubcategory contains "Spam" and FirstSubcategory !contains "Bulk"),
Bulk = countif(FirstSubcategory contains "Bulk")
by Date_value;
DeliveryByThreat
| join kind=fullouter Reports on Date_value
| join kind=fullouter PostDeliveryEvents on Date_value
| join kind=fullouter ThreatByAutomation on Date_value
| sort by Date_value asc
| project Date_value, Clean, Malware, Phish, Spam, Bulk, MessagesGotReported, PostDeliveryZAP, PostDeliveryTotalAIRInvestigations, PostDeliveryAirNoThreatsFound, PostDeliveryAirMalicious, PostDeliveryAirSuspicious
| where isnotempty(Date_value) // As Reports from CloudAppEvents Submissions could contain messages submitted before 30 days it is good to remove all > 30 days, otherwise EMailEvents wouldn't have a date
id: deb4b2c6-c10e-4044-8cf4-84243e40db73
name: MDO daily detection summary report
description: |
This query helps report daily on total number of emails, total number of emails detected aby Defender for Office 365
description-detailed: |
This query helps report daily on total number of emails, total number of emails detected as Malware, Phish, Spam, Bulk, total number of user or admin submissions, total number of ZAP events, total number of AIR investigations and their result
Reference - https://learn.microsoft.com/en-us/defender-office-365/mdo-about
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
dataTypes:
- CloudAppEvents
- AlertEvidence
- EmailEvents
- EmailPostDeliveryEvents
tactics:
- InitialAccess
relevantTechniques:
- T1566
query: |
let QueryTime = 30d;
let Reports = CloudAppEvents
| where Timestamp > ago(QueryTime)
| where ActionType == "UserSubmission" or ActionType == "AdminSubmission"
| extend MessageDate = todatetime((parse_json(RawEventData)).MessageDate)
| extend NetworkMessageID = tostring((parse_json(RawEventData)).ObjectId)
| extend Date_value = tostring(format_datetime( MessageDate, "yyyy-MM-dd"))
| distinct Date_value,NetworkMessageID
| summarize count() by Date_value
| project Date_value, MessagesGotReported=count_;
let ThreatByAutomation = (AlertEvidence | where Title == "Email reported by user as malware or phish")
| extend LastVerdictfromAutomation = tostring((parse_json(AdditionalFields)).LastVerdict)
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd"))
| extend DetectionFromAIR = iif(isempty(LastVerdictfromAutomation), "NoThreatsFound", tostring(LastVerdictfromAutomation))
| summarize PostDeliveryTotalAIRInvestigations = count(),
PostDeliveryAirNoThreatsFound = countif(DetectionFromAIR contains "NoThreatsFound"),
PostDeliveryAirSuspicious = countif(DetectionFromAIR contains "Suspicious"),
PostDeliveryAirMalicious = countif(DetectionFromAIR contains "Malicious")
by Date_value //Date Reported from Message Submissions from CloudAppEvents does not match to the AIR Investigations from Alert playbooks
| project Date_value, PostDeliveryTotalAIRInvestigations, PostDeliveryAirNoThreatsFound, PostDeliveryAirSuspicious, PostDeliveryAirMalicious;
let DeliveryInboundEvents = (EmailEvents | where EmailDirection == "Inbound" and Timestamp > ago(QueryTime)
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd"))
| project Date_value, Timestamp, NetworkMessageId, DetectionMethods ,RecipientEmailAddress);
let PostDeliveryEvents = (EmailPostDeliveryEvents | where ActionType contains "ZAP" and ActionResult == "Success"| join DeliveryInboundEvents on RecipientEmailAddress, NetworkMessageId //Only successful ZAP Events, there could still be more, join on Recipient and NetID
| extend Date_value = tostring(format_datetime( Timestamp, "yyyy-MM-dd")) //Zap Timestamp is used and | Sentinel Table | Notes |
|---|---|
AlertEvidence | Ensure this data connector is enabled |
CloudAppEvents | Ensure this data connector is enabled |
EmailEvents | Ensure this data connector is enabled |
Scenario: Scheduled report generation by Defender for Office 365
Description: The rule may trigger when Defender for Office 365 generates its daily detection summary report as part of its scheduled reporting process.
Filter/Exclusion: Exclude events where the source is Microsoft Defender for Office 365 and the action is related to “report generation” or “daily summary”.
Scenario: Admin task: Exporting detection statistics
Description: An administrator may manually export detection statistics from the Microsoft 365 Admin Center or via PowerShell for reporting purposes.
Filter/Exclusion: Exclude events where the user is a Global Administrator and the activity is related to “exporting reports” or “exporting detection data”.
Scenario: Automated backup or sync job
Description: A scheduled backup or synchronization job (e.g., using Microsoft 365 Backup or third-party tools like Veeam) may generate a large number of emails or trigger detection logic.
Filter/Exclusion: Exclude events where the source is a backup tool (e.g., Veeam, Commvault) or the activity is related to “backup” or “sync”.
Scenario: Email archiving or migration process
Description: Email archiving tools (e.g., Microsoft Purview Archiving, Mimecast, Symantec Enterprise Vault) or migration processes (e.g., Microsoft 365 Migration Wizard) can generate high volumes of emails that may be flagged by the detection rule.
Filter/Exclusion: Exclude events where the source is an email archiving tool or the activity is related to “migration”, “archiving”, or “bulk email transfer”.
Scenario: System-generated email notifications
Description: System-generated emails (e