Email Events Visualize Blocked Email Percentage
Query
//Visualize how much is email is being blocked as a percentage of total email over time
//Data connector required for this query - M365 Defender - Email* tables
EmailEvents
| where TimeGenerated > ago (30d)
| where EmailDirection == "Inbound"
| summarize
TotalCount=count(),
BlockedCount=countif(DeliveryAction in ("Blocked", "Junked"))
by bin(TimeGenerated, 6h)
| extend Percentage=(todouble(BlockedCount) * 100 / todouble(TotalCount))
| project-away TotalCount, BlockedCount
| render timechart with (title="Percentage of email blocked over time", ymax=100)Explanation
This query visualizes the percentage of blocked email compared to the total email over a period of time. It uses the M365 Defender - Email* tables as the data source. The query filters for inbound emails in the last 30 days. It then calculates the total count of emails and the count of blocked or junked emails within 6-hour intervals. The percentage of blocked emails is calculated and displayed in a timechart, with the title "Percentage of email blocked over time" and a maximum y-axis value of 100. The final result only includes the percentage data.
Details
Matt Zorich
Released: June 17, 2022
Tables
EmailEvents
Keywords
EmailEventsTimeGeneratedEmailDirectionDeliveryActionBlockedJunkedTotalCountBlockedCountPercentage
Operators
whereagocountcountifbybinextendtodoubleproject-awayrender