KQL Search

Query Details

HomeAI ToolsDevice Query

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 profile picture

Matt Zorich

Released: June 17, 2022

Tables

EmailEvents

Keywords

EmailEvents,TimeGenerated,EmailDirection,DeliveryAction,Blocked,Junked,TotalCount,BlockedCount,Percentage

Operators

whereagocountcountifbybinextendtodoubleproject-awayrender

Actions