Build Better Phishing Reports with Defender and Power BI

Turning Microsoft Defender Into a Living Phishing Dashboard (That Tells the Whole Story)

What this episode covers

• The hidden signals Defender already captures (click logs, near-misses, AIR outcomes, campaign mapping)

• Why exec dashboards miss 80% of the story

• A repeatable framework for dashboards that survive schema changes and new attack tactics

• Power BI integration traps (APIs, auth, pagination, normalization) and how to dodge them

• Executive KPIs and visuals that drive action—not just compliance

Key takeaways

• Surface stats ≠ risk. Threat Explorer, AIR, and User Submissions reveal the real narrative behind “blocked” totals.

• Near-miss telemetry matters: who clicked, who reported, how fast you contained it.

• Treat dashboards as living systems: automate ingestion, model relationships, standardize KPIs, templatize visuals.

• Normalize before you visualize—consistency beats pretty.

• Tie metrics to business outcomes (response time, dwell time, high-risk entities), not vanity counts.

The hidden layer you’re ignoring

• Threat Explorer: campaign relationships (sender infra, files, who else saw it)

• AIR (Automated Investigation & Response): correlated actions, device/mailbox scopes, background playbooks

• User Submissions: human signal that often flags what filters missed

Why dashboards break

• CSVs and one-offs die on the next Defender update

• Schema drift (renamed/missing fields), mixed GUIDs vs UPNs, API pagination gaps

• DirectQuery throttling and fragile refresh schedules hide weekend spikes

Build a framework, not a screenshot

1. Reliable connectors

   • Use Defender APIs with app registrations, OAuth, and scoped permissions

   • Implement full pagination and backfill windows

2. Resilient data model

   • Unify incidents, users, devices, mailboxes, campaigns

   • Map IDs to people/devices; enforce types and surrogate keys

3. Prioritized KPIs

   • Only what informs decisions and investment

4. Visual templates

   • Anomaly-first layouts, clear escalation cues, and trend sparklines

Power BI pitfalls (and fixes)

• Auth churn: rotate secrets; prefer managed identities where possible

• Schema drift: version your transformations; add “unknown” handling

• Mixed exports + APIs: stop—pick APIs; centralize in dataflows

• Pagination gaps: test against high-volume days; log page counts

• Refresh holes: schedule intra-day pulls; add delta loads for weekends/holidays

• Performance: star schema, incremental refresh, partition by date, avoid row-level calc columns

Executive KPIs that actually change behavior

• Attack success rate: % of phish that bypassed controls AND elicited user interaction

• User click vs. report rate: who clicked, who reported, and how that’s trending

• Dwell time to remediation: detection → containment (by vector/campaign)

• High-risk entity exposure: repeat targeting of VIPs, finance, and elevated accounts

• Near-miss escalation rate: blocked post-click, Safe Links interventions, last-mile saves

• Campaign trendline: link vs attachment vs BEC patterns week over week

• Automation effectiveness: AIR closed-loop rate and manual overrides

Visualization cues

• Put anomalies top-left; totals bottom-right

• Color only for urgency (e.g., dwell time spikes)

• Sparklines for trends; badges for SLA breaches; flags for repeat targets

• Story panes: “What changed this week?” and “What to do next”

Fast wins this week

• Start pulling: AIR investigations, Threat Explorer campaign data, User Submissions

• Normalize IDs: translate GUIDs to UPNs/device names in a dataflow

• Add KPIs: dwell time, near-miss rate, high-risk exposure

• Set refresh: intra-day incremental loads; weekend coverage

• Create an exec page: 7 tiles max, anomaly-focused, with owner + next action on each

Guardrails for durability

• Version control your dataflows and measures

• Add schema-change tests to refresh pipelines

• Document KPI definitions (no silent tweaks between quarters)

• Keep a “blackout” detector: alert if expected event volume drops unexpectedly

Who should watch

• SecOps leads who sense “all green” isn’t real

• Power BI owners tired of brittle CSVs

• CISOs who want decisions, not dashboards

Common anti-patterns to retire

• “Blocked emails” as a victory lap

• Mixing manual exports with API pulls

• Monthly refreshes for weekly threat reviews

• Pie charts for anything temporal

30-day rollout plan (outline)
Week 1: API access, dataflows, pagination, baseline star schema
Week 2: Normalization, entity mapping, first set of KPIs
Week 3: Exec page + SOC page, anomaly rules, incremental refresh
Week 4: Backtesting on last quarter, drill-throughs, ops runbook

Link
Get more Microsoft 365 deep dives: m365.show/subscribe