Build the Email Protection Dashboard

60-90 minutes Modules: M9, M10, M6

Objective

Build the 8-tile email protection dashboard described in Module 9.9. By the end of this lab, you have a Sentinel workbook that provides continuous visibility into your email protection posture — the dashboard you present to management monthly.

Required: Microsoft Sentinel workspace with the Defender XDR connector enabled. Defender for Office 365 P2 (for Threat Explorer data). Security Reader role (minimum).

Step 1: Create the workbook

Navigate to: Sentinel → Workbooks → Add workbook → Edit.

Set the title: “Email Protection Dashboard — [Your Org]”

Add a text element at the top:

## Email Protection Health
Last 30 days | Auto-refresh: 1 hour
Phishing delivery rate target: < 10% | ZAP gap target: < 15 minutes

Step 2: Tile 1 — Anti-Phishing Detections

Add a query tile. Visualization: time chart. Time range: 30 days.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
EmailEvents
| where TimeGenerated > ago(30d)
| where ThreatTypes has "Phish"
| summarize
    TotalPhish = count(),
    Delivered = countif(DeliveryAction == "Delivered"),
    Blocked = countif(DeliveryAction == "Blocked"),
    Junked = countif(DeliveryAction == "Junked")
    by bin(TimeGenerated, 1d)
| order by TimeGenerated asc

What to look for: The Delivered line should be consistently below the Blocked line. Spikes in TotalPhish indicate campaigns. Spikes in Delivered indicate campaigns that bypassed protection.

Step 3: Tile 2 — Delivery Rate KPI

Add a query tile. Visualization: single value (stat).

1
2
3
4
5
6
7
8
EmailEvents
| where TimeGenerated > ago(30d)
| where ThreatTypes has "Phish"
| summarize
    Total = count(),
    Delivered = countif(DeliveryAction == "Delivered")
| extend DeliveryRate = round(100.0 * Delivered / Total, 1)
| project DeliveryRate

Format as percentage. Conditional formatting: green if < 10%, yellow if 10-20%, red if > 20%.

Step 4: Tile 3 — Safe Links Click Tracking

Add a query tile. Visualization: time chart.

1
2
3
4
5
6
7
UrlClickEvents
| where TimeGenerated > ago(30d)
| summarize
    Allowed = countif(ActionType == "ClickAllowed"),
    Blocked = countif(ActionType == "ClickBlocked")
    by bin(TimeGenerated, 1d)
| order by TimeGenerated asc

What to look for: Blocked clicks are Safe Links working. Allowed clicks on days with known phishing campaigns indicate bypass.

Step 5: Tile 4 — Safe Attachments Malware

Add a query tile. Visualization: bar chart.

1
2
3
4
5
6
7
EmailAttachmentInfo
| where TimeGenerated > ago(30d)
| where ThreatTypes has "Malware"
| extend FileExtension = tostring(split(FileName, ".")[-1])
| summarize Count = count() by FileExtension
| order by Count desc
| take 10

What to look for: Which file types are being targeted. Dominant types should be blocked at the transport layer (Module 9.7).

Step 6: Tile 5 — ZAP Timing

Add a query tile. Visualization: single values (stat grid).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
let Deliveries = EmailEvents
| where TimeGenerated > ago(30d)
| where DeliveryAction == "Delivered"
| project DeliveryTime = TimeGenerated, NetworkMessageId;
EmailPostDeliveryEvents
| where TimeGenerated > ago(30d)
| where ActionType == "ZAP"
| project ZapTime = TimeGenerated, NetworkMessageId
| join kind=inner Deliveries on NetworkMessageId
| extend GapMinutes = datetime_diff('minute', ZapTime, DeliveryTime)
| summarize
    AvgGap = round(avg(GapMinutes), 0),
    MedianGap = round(percentile(GapMinutes, 50), 0),
    P95Gap = round(percentile(GapMinutes, 95), 0),
    TotalZAPs = count()

Target: AvgGap < 15, P95Gap < 60.

Step 7: Tile 6 — DMARC Compliance

Add a query tile. Visualization: pie chart.

1
2
3
4
5
6
EmailEvents
| where TimeGenerated > ago(30d)
| where SenderFromDomain == "northgateeng.com" // REPLACE
| extend AuthDetails = parse_json(AuthenticationDetails)
| extend DMARC = tostring(AuthDetails.DMARC)
| summarize count() by DMARC

Target: 100% pass. Any fail or none: investigate the sender (Module 9.6).

Step 8: Tile 7 — Transport Rule Hits

Add a query tile. Visualization: table.

1
2
3
4
5
6
7
EmailEvents
| where TimeGenerated > ago(30d)
| where DeliveryAction == "Blocked"
| extend RuleName = tostring(parse_json(tostring(AdditionalFields)).TransportRule)
| where isnotempty(RuleName)
| summarize Hits = count() by RuleName
| order by Hits desc

Step 9: Tile 8 — User-Reported Phishing

Add a query tile. Visualization: time chart.

1
2
3
4
EmailPostDeliveryEvents
| where TimeGenerated > ago(30d)
| where ActionType == "PhishReport"
| summarize Reports = count() by bin(TimeGenerated, 1d)

What to look for: Steady baseline of 1-5 reports/day is healthy (users are engaged). Spike of 10+ in one day: active campaign that users are reporting.

Step 10: Save and schedule

Save the workbook. Pin to your Sentinel dashboard. Set auto-refresh: 1 hour.

Monthly reporting workflow: On the first Monday of each month, open this workbook, set the time range to the previous month, and screenshot or export each tile. Compile into a one-page email protection report for management.

Verification

You have completed this lab when:

Workbook saved in Sentinel with 8 tiles
Each tile returns data (no errors, no “no results”)
Delivery rate KPI has conditional formatting (green/yellow/red)
Time charts show 30-day trend
DMARC tile shows your domain’s compliance status
Auto-refresh configured

This workbook is a production asset. Copy it to your production workspace. Present it in your next security review meeting.

← All Labs