email-threat-posture

name	email-threat-posture
description	Generate email threat protection reports and assess email security posture. Triggers on keywords like "email threat report", "email security posture", "phishing report", "MDO report", "Defender for Office 365 report", "ZAP effectiveness", "Safe Links report", "DMARC report", "spam report", "email volume report". Queries EmailEvents, EmailPostDeliveryEvents, UrlClickEvents, and EmailAttachmentInfo in Advanced Hunting for a posture assessment covering inbound mail flow, threat composition, phishing detection, email authentication (DMARC/DKIM/SPF), post-delivery remediation (ZAP), Safe Links click protection, attachment analysis, detection method effectiveness, and delivery disposition. Supports inline chat, markdown file, and SVG dashboard output.
threat_pulse_domains	["email"]
drill_down_prompt	Run email threat posture report — phishing trends, delivery gaps, protection effectiveness

Email Threat Protection Posture — Instructions

Purpose

This skill generates an Email Threat Protection Posture Report using Microsoft Defender for Office 365 (MDO) telemetry available through Advanced Hunting. It provides C-level visibility into how effectively the organization's email security stack is detecting, blocking, and remediating email-based threats.

What this skill covers:

Domain	Key Questions Answered
📬 Mail Flow Overview	How many inbound emails? What's the daily trend? Who are the top senders?
🛡️ Threat Composition	How many phishing, spam, and malware threats were detected?
🎯 Phishing Protection	How many phishing emails were blocked vs delivered? Who are the most targeted users?
🔐 Email Authentication	What are the DMARC/DKIM/SPF/CompAuth pass rates? Which domains fail authentication?
🧹 Post-Delivery Remediation	How effective is ZAP? How many remediations succeeded vs failed?
🔗 Safe Links Protection	How many URL clicks were scanned? Were any phishing clicks allowed through?
📎 Attachment Analysis	What attachment types are flowing through email? Were any malicious?
📊 Detection Methods	What detection technologies are catching threats (URL detonation, fingerprinting, etc.)?
📦 Delivery Disposition	Where do emails end up — inbox, junk, quarantine, blocked?
🚨 MDO Incidents	How many security incidents were generated by Defender for Office? What severity, status, and types?

Data sources: EmailEvents, EmailPostDeliveryEvents, UrlClickEvents, EmailAttachmentInfo, SecurityAlert, SecurityIncident (Advanced Hunting)

References:

• Microsoft Docs — EmailEvents table
• Microsoft Docs — EmailPostDeliveryEvents table
• Microsoft Docs — UrlClickEvents table
• Microsoft Docs — EmailAttachmentInfo table
• MDO Efficacy Query — Microsoft Learn

🔴 URL Registry — Canonical Links for Report Generation

MANDATORY: When generating reports, copy URLs verbatim from this registry. NEVER construct, guess, or paraphrase a URL.

Label	Canonical URL
DOCS_EMAILEVENTS	https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-emailevents-table
DOCS_EMAILPOSTDELIVERY	https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-emailpostdeliveryevents-table
DOCS_URLCLICKEVENTS	https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-urlclickevents-table
DOCS_EMAILATTACHMENTINFO	https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-emailattachmentinfo-table
DOCS_MDO_EFFICACY	https://learn.microsoft.com/en-us/defender-office-365/reports-mdo-email-collaboration-dashboard#appendix-advanced-hunting-efficacy-query-in-defender-for-office-365-plan-2
DOCS_MDO_OVERVIEW	https://learn.microsoft.com/en-us/defender-office-365/mdo-about
DOCS_SECURITY_ALERT	https://learn.microsoft.com/en-us/azure/sentinel/data-connectors/microsoft-sentinel-security-alert
DOCS_ZAP	https://learn.microsoft.com/en-us/defender-office-365/zero-hour-auto-purge
DOCS_SAFE_LINKS	https://learn.microsoft.com/en-us/defender-office-365/safe-links-about

---

📑 TABLE OF CONTENTS

1. Critical Workflow Rules — Mandatory rules
2. Email Protection Score Formula — Composite posture scoring
3. Execution Workflow — Phase-by-phase query plan
4. Sample KQL Queries — All queries (Q1–Q14)
5. Output Modes — Inline vs Markdown report
6. Inline Report Template — Chat-rendered format
7. Markdown File Report Template — Disk-saved format
8. Known Pitfalls — Schema quirks and edge cases
9. Quality Checklist — Pre-delivery validation
10. SVG Dashboard Generation — Visual dashboard from report

---

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

1. Use RunAdvancedHuntingQuery by default — EmailEvents and related tables are XDR-native tables available in Advanced Hunting. Use Timestamp as the datetime column. If a query fails in AH, fall back to Sentinel Data Lake (query_lake) using TimeGenerated.

2. Default lookback: 7 days — Unless the user specifies a different period. This provides a meaningful weekly snapshot for executive reporting while staying within AH's 30-day retention.

3. ASK the user for output format before generating the report:

   • Inline chat summary (quick review in chat)
   • Markdown file report (detailed, archived to reports/email-threat-posture/)
   • Both (markdown + inline summary)

4. ⛔ MANDATORY: Evidence-based analysis only — Report ONLY what query results show. Use the explicit absence pattern (✅ No [finding] detected) when queries return 0 results. Never fabricate data.

5. Run queries in parallel batches where possible — Phase 1 queries (Q1–Q4) are independent. Phase 2 queries (Q5–Q8) are independent. Phase 3 queries (Q9–Q12) are independent.

6. PII handling — Do NOT include recipient email addresses in inline reports or markdown files. Aggregate by domain or use anonymized references (e.g., "2 users in the contoso.com domain"). Top sender domains from external sources are acceptable.

7. Percentages must be grounded — Always show both the percentage AND the raw count (e.g., "99.8% clean (5,851 of 5,864)").

---

Email Protection Score Formula

The Email Protection Score is a composite posture indicator summarizing the effectiveness of email security controls. Higher scores indicate stronger protection (inverse of a risk score).

Scoring Dimensions

$$ \text{EmailProtectionScore} = \sum_{i} \text{DimensionScore}_i $$

Each dimension contributes 0–20 points to a maximum of 100:

Dimension	Max	🟢 High (16–20)	🟡 Medium (8–15)	🔴 Low (0–7)
Threat Block Rate	20	≥95% of threats not in inbox (post-ZAP final state)	80–94% remediated	<80% remediated (threats remain in inbox)
Email Authentication	20	SPF+DMARC+DKIM all ≥95%	Any one 80–94%	Any one <80%
ZAP Effectiveness	20	≥95% ZAP success rate + 0 failed ZAPs	80–94% success OR 1–2 failures	<80% success OR ≥3 failures
Safe Links Protection	20	0 phishing click-throughs AND active scanning	1–2 phishing click-throughs	≥3 phishing click-throughs OR no scanning
Phishing Delivery Rate	20	0 phishing emails delivered (post-ZAP)	1–5 phishing delivered (post-ZAP)	>5 phishing still in mailboxes (post-ZAP)

Interpretation Scale

Score	Rating	Action
85–100	✅ Strong	Excellent posture — maintain current configurations
65–84	🟡 Good	Minor gaps — review flagged dimensions
45–64	🟠 Needs Improvement	Multiple weaknesses — prioritize remediation
0–44	🔴 Critical	Significant exposure — immediate action required

---

Execution Workflow

Phase 0: Prerequisites

1. Confirm RunAdvancedHuntingQuery is available (EmailEvents tables are AH-native)
2. Ask user for output format (inline / markdown / both)
3. Confirm lookback period (default: 7 days)

Phase 1: Mail Flow & Threat Overview (Q1–Q4)

Run in parallel — no dependencies between queries.

Query	Purpose
Q1	Inbound email summary with threat breakdown
Q2	Email volume trend by day
Q3	Delivery action and location breakdown
Q4	Detection methods breakdown

Phase 2: Protection Effectiveness (Q5–Q8)

Run in parallel — no dependencies between queries.

Query	Purpose
Q5	Email authentication pass rates (DMARC/DKIM/SPF/CompAuth)
Q6	ZAP and post-delivery remediation summary
Q7	Safe Links click activity summary
Q8	Phishing emails delivered (not blocked)

Phase 3: Deep Dives & Governance (Q9–Q12)

Run in parallel — no dependencies between queries.

Query	Purpose
Q9	Top phishing sender domains
Q10	Most targeted recipients (aggregated)
Q11	Attachment type distribution
Q12	Post-ZAP threat state (latest delivery location)

Phase 4: MDO Security Incidents (Q13–Q14)

Run in parallel — no dependencies between queries.

Query	Purpose
Q13	MDO incident summary by severity and status
Q14	MDO incident type breakdown (top alert-driven incidents)

⚠️ SecurityAlert.Status is IMMUTABLE — always "New" regardless of actual state. These queries use the canonical SecurityAlert→SecurityIncident join to get real Status and Classification from the SecurityIncident table. See copilot-instructions.md Known Table Pitfalls.

Phase 5: Score Computation & Report Generation

1. Compute per-dimension scores from Phase 1–4 data
2. Sum dimension scores for composite Email Protection Score
3. Generate report in requested output mode
4. Offer SVG dashboard if not already requested

---

Sample KQL Queries

All queries below are verified against the EmailEvents family of tables. Use them exactly as written, substituting only the lookback period where noted. These queries use Timestamp for Advanced Hunting. If falling back to Data Lake, replace Timestamp with TimeGenerated.

Query 1: Inbound Email Summary with Threat Breakdown

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| summarize
    TotalInbound = count(),
    Clean = countif(isempty(ThreatTypes)),
    Phish = countif(ThreatTypes has "Phish"),
    Malware = countif(ThreatTypes has "Malware"),
    Spam = countif(ThreatTypes has "Spam"),
    HighConfPhish = countif(ConfidenceLevel has "High" and ThreatTypes has "Phish"),
    Blocked = countif(DeliveryAction == "Blocked"),
    Delivered = countif(DeliveryAction == "Delivered"),
    Junked = countif(DeliveryAction == "Junked"),
    DistinctSenders = dcount(SenderFromAddress),
    DistinctRecipients = dcount(RecipientEmailAddress)
| project TotalInbound, Clean, Phish, Malware, Spam, HighConfPhish,
    Blocked, Delivered, Junked, DistinctSenders, DistinctRecipients

Query 2: Email Volume Trend by Day

EmailEvents
| where Timestamp > ago(7d)
| summarize
    Inbound = countif(EmailDirection == "Inbound"),
    Outbound = countif(EmailDirection == "Outbound"),
    IntraOrg = countif(EmailDirection == "Intra-org")
    by Day = bin(Timestamp, 1d)
| order by Day asc

Query 3: Delivery Action & Location Breakdown

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| summarize Count = count() by DeliveryAction, DeliveryLocation
| order by Count desc

Query 4: Detection Methods Breakdown

EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(DetectionMethods) and DetectionMethods != "{}"
| extend DetMethods = parse_json(DetectionMethods)
| extend FirstDetection = tostring(bag_keys(DetMethods)[0])
| extend FirstSubcategory = iif(
    FirstDetection != "" and array_length(DetMethods[FirstDetection]) > 0,
    strcat(FirstDetection, ": ", tostring(DetMethods[FirstDetection][0])),
    FirstDetection)
| summarize Count = count() by FirstSubcategory
| order by Count desc

Query 5: Email Authentication Pass Rates

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| extend AuthDetails = parse_json(AuthenticationDetails)
| extend
    DMARC = tostring(AuthDetails.DMARC),
    DKIM = tostring(AuthDetails.DKIM),
    SPF = tostring(AuthDetails.SPF),
    CompAuth = tostring(AuthDetails.CompAuth)
| summarize
    TotalEmails = count(),
    DMARCPass = countif(DMARC == "pass"),
    DMARCFail = countif(DMARC == "fail"),
    DKIMPass = countif(DKIM == "pass"),
    DKIMFail = countif(DKIM == "fail"),
    SPFPass = countif(SPF == "pass"),
    SPFFail = countif(SPF == "fail"),
    CompAuthPass = countif(CompAuth has "pass"),
    CompAuthFail = countif(CompAuth == "fail")

Query 6: ZAP & Post-Delivery Remediation Summary

EmailPostDeliveryEvents
| where Timestamp > ago(7d)
| summarize
    TotalActions = count(),
    PhishZAP = countif(ActionType == "Phish ZAP"),
    MalwareZAP = countif(ActionType == "Malware ZAP"),
    SpamZAP = countif(ActionType == "Spam ZAP"),
    ThreatZAPTotal = countif(ActionType in ("Phish ZAP", "Malware ZAP", "Spam ZAP")),
    ManualRemediation = countif(ActionType has "Admin"),
    SuccessCount = countif(ActionResult == "Success"),
    ErrorCount = countif(ActionResult == "Error")
| project TotalActions, PhishZAP, MalwareZAP, SpamZAP, ThreatZAPTotal, ManualRemediation, SuccessCount, ErrorCount

Query 7: Safe Links Click Activity Summary

UrlClickEvents
| where Timestamp > ago(7d)
| summarize
    TotalClicks = count(),
    BlockedClicks = countif(ActionType == "ClickBlocked"),
    AllowedClicks = countif(ActionType == "ClickAllowed"),
    ClickedThrough = countif(IsClickedThrough == true),
    PhishClicks = countif(ThreatTypes has "Phish"),
    DistinctUrls = dcount(Url),
    DistinctUsers = dcount(AccountUpn)

Query 8: Phishing Emails Delivered (Not Blocked)

EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Phish"
| where DeliveryAction == "Delivered" or LatestDeliveryAction == "Delivered"
| summarize
    DeliveredPhish = count(),
    DistinctRecipients = dcount(RecipientEmailAddress),
    DistinctSenders = dcount(SenderFromAddress),
    Subjects = make_set(Subject, 5)

Query 9: Top Phishing Sender Domains

EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Phish"
| summarize
    Count = count(),
    DistinctRecipients = dcount(RecipientEmailAddress),
    DeliveredCount = countif(DeliveryAction == "Delivered" or LatestDeliveryAction == "Delivered")
    by SenderFromDomain
| top 10 by Count

Query 10: Most Targeted Recipients (Aggregated by Domain)

EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(ThreatTypes) and EmailDirection == "Inbound"
| extend RecipientDomain = tostring(split(RecipientEmailAddress, "@")[1])
| summarize
    ThreatCount = count(),
    PhishCount = countif(ThreatTypes has "Phish"),
    SpamCount = countif(ThreatTypes has "Spam"),
    MalwareCount = countif(ThreatTypes has "Malware"),
    DistinctRecipients = dcount(RecipientEmailAddress)
    by RecipientDomain
| order by ThreatCount desc

Query 11: Attachment Type Distribution

EmailAttachmentInfo
| where Timestamp > ago(7d)
| summarize
    Count = count(),
    DistinctFiles = dcount(FileName),
    ThreatCount = countif(isnotempty(ThreatTypes))
    by FileType
| order by Count desc
| take 15

Query 12: Post-ZAP Threat State (Latest Delivery Location)

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| where isnotempty(ThreatTypes)
| summarize Count = count() by LatestDeliveryAction, LatestDeliveryLocation, ThreatTypes
| order by Count desc

Query 13: MDO Incident Summary by Severity and Status

Uses the canonical SecurityAlert→SecurityIncident join. Filters to ProductName == "Office 365 Advanced Threat Protection" and excludes Communication Compliance alerts (CC_ prefix).

let MDOAlerts = SecurityAlert
| where TimeGenerated > ago(7d)
| where ProductName == "Office 365 Advanced Threat Protection"
| where AlertName !startswith "CC_"
| summarize arg_max(TimeGenerated, *) by SystemAlertId
| project SystemAlertId;
SecurityIncident
| where CreatedTime > ago(7d)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| mv-expand AlertId = AlertIds
| extend AlertId = tostring(AlertId)
| join kind=inner MDOAlerts on $left.AlertId == $right.SystemAlertId
| summarize IncidentCount = dcount(IncidentNumber) by Severity, Status, Classification
| order by Severity asc, IncidentCount desc

Query 14: MDO Incident Type Breakdown (Top Alert-Driven Incidents)

Groups incidents by title and alert composition to show the most common MDO-generated incident types.

let MDOAlerts = SecurityAlert
| where TimeGenerated > ago(7d)
| where ProductName == "Office 365 Advanced Threat Protection"
| where AlertName !startswith "CC_"
| summarize arg_max(TimeGenerated, *) by SystemAlertId
| project SystemAlertId, AlertName, AlertSeverity, ProductName;
SecurityIncident
| where CreatedTime > ago(7d)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| mv-expand AlertId = AlertIds
| extend AlertId = tostring(AlertId)
| join kind=inner MDOAlerts on $left.AlertId == $right.SystemAlertId
| summarize
    IncidentCount = dcount(IncidentNumber),
    AlertCount = count(),
    OpenCount = dcountif(IncidentNumber, Status == "New" or Status == "Active"),
    ClosedCount = dcountif(IncidentNumber, Status == "Closed"),
    TruePositives = dcountif(IncidentNumber, Classification == "TruePositive")
    by AlertName, Severity
| order by IncidentCount desc
| take 10

---

Output Modes

Mode 1: Inline Chat Summary

Render the full analysis directly in the chat response. Best for quick review and C-level briefings.

Mode 2: Markdown File Report

Save a comprehensive report to disk at:

reports/email-threat-posture/Email_Threat_Protection_Report_YYYYMMDD_HHMMSS.md

Mode 3: Both

Generate the markdown file AND provide an inline summary in chat.

Always ask the user which mode before generating output.

---

Inline Report Template

Render the following sections in order. Omit sections only if explicitly noted as conditional.

🔴 URL Rule: All hyperlinks in the report MUST be copied verbatim from the URL Registry above. Do NOT generate, recall from memory, or paraphrase any URL. If a needed URL is not in the registry, use plain text (no hyperlink).

# 📧 Email Threat Protection Report

**Generated:** YYYY-MM-DD HH:MM UTC
**Data Source:** Microsoft Defender for Office 365 (Advanced Hunting)
**Analysis Period:** <StartDate> → <EndDate> (<N> days)
**Protected Mailboxes:** <DistinctRecipients>
**Total Inbound Emails:** <N>
**Email Protection Score:** <Score>/100 — <RATING>

---

## Executive Summary

<2-3 sentences: total inbound volume, threat detection rate, key findings, overall posture rating>

**Email Protection Score:** 🟢/🟡/🟠/🔴 <RATING> (<Score>/100)

---

## Key Metrics

| Metric | Value |
|--------|-------|
| Total Inbound Emails | <N> |
| Clean Email Rate | <N>% (<clean> of <total>) |
| Threats Detected | <N> (Phish: <N>, Spam: <N>, Malware: ) |
| Threats Blocked Pre-Delivery |  |
| Phishing Delivered (Now Remediated) |  |
| Threat ZAP Actions |  (Phish: , Malware: , Spam: ) |
| Total Post-Delivery Actions |  (includes system events) |
| ZAP Success Rate | % (Failed: ) |
| Threats Still in Mailboxes (Post-ZAP) |  (Phish: , Spam: ) |
| Safe Links Clicks Scanned |  |
| Phishing Click-Throughs |  |
| Distinct Senders |  |
| Protected Mailboxes |  |

---







| Day | Inbound | Outbound | Intra-org |
|-----|---------|----------|-----------|
|  |  |  |  |

 

---





| Category | Count | % of Threats |
|----------|-------|-------------|
| Phishing |  | % |
| Spam |  | % |
| Malware |  | % |
| High-Confidence Phishing |  | — |



| Method | Count |
|--------|-------|
|  |  |



| Domain | Phish Count | Delivered | Recipients Hit |
|--------|-------------|-----------|----------------|
|  |  |  |  |


✅ No phishing sender domains detected.

---





| Action | Location | Count |
|--------|----------|-------|
| Delivered | Inbox/folder |  |
| Blocked | Dropped |  |
| Blocked | Quarantine |  |
| Junked | Junk folder |  |





| Latest Action | Location | Threat Type | Count |
|---------------|----------|-------------|-------|
|  |  |  |  |



| Current Location | Threat Count | % of Threats |
|-----------------|-------------|-------------|
| 🟢 Quarantine |  | % |
| 🟢 Junk folder |  | % |
| 🟢 Blocked/Dropped/Failed |  | % |
| 🟢 Deleted items |  | % |
| 🔴  |  |  |
|  |  |  |



---



| Protocol | Pass Rate | Pass Count | Fail Count | Other/None |
|----------|-----------|------------|------------|------------|
| SPF | % |  |  |  |
| DMARC | % |  |  |  |
| DKIM | % |  |  |  |
| CompAuth | % |  |  |  |




  

---



| Metric | Value |
|--------|-------|
| Threat ZAP Actions |  (Phish: , Malware: , Spam: ) |
| Total Post-Delivery Actions |  (includes system events, admin actions) |
| ZAP Success Rate | % ( of ) |
| Failed Remediations |  |



 0:>
⚠️  — manual follow-up recommended. Threats may remain in user mailboxes.


✅ All post-delivery remediations completed successfully.

---



| Metric | Value |
|--------|-------|
| Total Clicks Scanned |  |
| Clicks Blocked |  |
| Clicks Allowed |  |
| Phishing Clicks |  |
| Click-Through Overrides |  |
| Distinct URLs Scanned |  |
| Users Protected |  |

 0:>
🔴  — investigate affected users for credential compromise.


✅ No phishing URL click-throughs detected.

---





| File Type | Count | Distinct Files | Threats Detected |
|-----------|-------|----------------|------------------|
|  |  |  |  |

 0:>
⚠️  — verify delivery status and endpoint execution.


✅ No malicious attachments detected in email flow.

---



| Recipient Domain | Threat Count | Phish | Spam | Malware | Recipients |
|-----------------|-------------|-------|------|---------|------------|
|  |  |  |  |  |  |

---





---




| Severity | Open | Closed | True Positive | Total |
|----------|------|--------|---------------|-------|
| 🔴 High |  |  |  |  |
| 🟠 Medium |  |  |  |  |
| 🟡 Low |  |  |  |  |
| 🔵 Informational |  |  |  |  |
|  |  |  |  |  |



| Alert Name | Severity | Incidents | Open | Closed | True Positives |
|------------|----------|-----------|------|--------|----------------|
|  |  |  |  |  |  |


✅ No MDO-generated security incidents in the analysis period.

---


| Factor | Finding |
|--------|---------|
|   |  |

---



   — 
 ...

---



| Query | Description | Records | Time |
|-------|-------------|---------|------|
| Q1 | Inbound Email Summary |  |  |
| Q2 | Daily Volume Trend |  |  |
| ... | ... | ... | ... |
| Q13 | MDO Incident Summary |  |  |
| Q14 | MDO Incident Types |  |  |

---

Markdown File Report Template

When outputting to markdown file, use the same structure as the Inline Report Template above, saved to:

reports/email-threat-posture/Email_Threat_Protection_Report_YYYYMMDD_HHMMSS.md

Include the following additional sections in the file report that are omitted from inline:

1. Top sender domains table (full top 10 by volume with phish/spam breakdown)
2. Authentication failure breakdown by domain (domains failing DMARC/DKIM/SPF)
3. Overridden threats (emails detected as threats but allowed by policy)
4. Complete detection methods table (all detection categories, not just top)
5. First-contact phishing attempts (emails from never-before-seen senders flagged as phish)
6. MDO security incidents — Full severity × status breakdown + top incident types from Q13/Q14
7. Raw query references — note that full query definitions are in this SKILL.md file

Markdown Section Ordering

Follow this exact section order in markdown file reports:

Order	Section	Source
1	Header (with Total Inbound + Score)	Template header
2	Executive Summary	Template
3	Key Metrics	Template
4	Mail Flow Overview (daily trend)	Q2
5	Threat Composition (categories + detection methods + top phish senders)	Q1, Q4, Q9
6	Delivery Disposition (initial + post-ZAP threat state)	Q3, Q12
7	Email Authentication (with auth failures by domain)	Q5, QM2
8	Post-Delivery Remediation (ZAP)	Q6
9	Safe Links Protection	Q7
10	Attachment Analysis	Q11
11	Targeted Recipients	Q10
12	— Deep-dive sections start here —
13	Overridden Threats	QM3
14	First-Contact Phishing	QM4
15	MDO Security Incidents	Q13, Q14
16	Top Sender Domains by Volume	QM1
17	— Score and assessment —
18	Email Protection Score Card	Computed
19	Security Assessment	Synthesized
20	Recommendations	Synthesized
21	Appendix: Query Execution Summary	All queries
22	References	URL Registry

Key rule: Score Card → Assessment → Recommendations always come AFTER all data sections (including deep dives). This ensures the reader sees all evidence before the overall assessment.

Additional Queries for Markdown File Deep Dives

These queries provide enrichment data for the markdown file report only. Skip for inline mode.

QM1: Top Sender Domains by Volume

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| summarize
    EmailCount = count(),
    PhishCount = countif(ThreatTypes has "Phish"),
    SpamCount = countif(ThreatTypes has "Spam"),
    DistinctSenders = dcount(SenderFromAddress)
    by SenderFromDomain
| order by EmailCount desc
| take 10

QM2: Authentication Failures by Domain

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| extend AuthDetails = parse_json(AuthenticationDetails)
| extend
    DMARC = tostring(AuthDetails.DMARC),
    DKIM = tostring(AuthDetails.DKIM),
    SPF = tostring(AuthDetails.SPF)
| summarize
    TotalEmails = count(),
    DMARCFail = countif(DMARC == "fail"),
    DKIMFail = countif(DKIM == "fail"),
    SPFFail = countif(SPF == "fail")
    by SenderFromDomain
| where DMARCFail > 0 or DKIMFail > 0 or SPFFail > 0
| order by TotalEmails desc
| take 15

QM3: Overridden Threats (Allow Policies)

EmailEvents
| where Timestamp > ago(7d)
| where OrgLevelAction == "Allow" and isnotempty(ThreatTypes)
| summarize Count = count() by ThreatTypes, OrgLevelPolicy, DetectionMethods
| order by Count desc

QM4: First-Contact Phishing Attempts

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| where IsFirstContact == true
| where ThreatTypes has "Phish" or UrlCount > 3
| summarize
    FirstContactCount = count(),
    PhishCount = countif(ThreatTypes has "Phish"),
    HighUrlCount = countif(UrlCount > 3),
    DistinctSenders = dcount(SenderFromAddress)

File Report Header

# Email Threat Protection Report

**Generated:** YYYY-MM-DD HH:MM UTC
**Data Source:** Microsoft Defender for Office 365 (Advanced Hunting)
**Analysis Period:** <StartDate> → <EndDate> (<N> days)
**Protected Mailboxes:** <DistinctRecipients>
**Total Inbound Emails:** <N>
**Email Protection Score:** <Score>/100 — <RATING>

---

---

Known Pitfalls

1. DetectionMethods Is a JSON String

Problem: DetectionMethods looks like it should be dynamic but is a string column containing JSON. Direct property access fails.

Solution: Always parse_json(DetectionMethods) before accessing sub-keys:

| extend DetMethods = parse_json(DetectionMethods)
| extend FirstDetection = tostring(bag_keys(DetMethods)[0])

2. AuthenticationDetails Is a JSON String

Problem: Same as DetectionMethods — AuthenticationDetails is a string column, not dynamic.

Solution: Always parse_json(AuthenticationDetails):

| extend AuthDetails = parse_json(AuthenticationDetails)
| extend DMARC = tostring(AuthDetails.DMARC)

3. ThreatTypes Is Pipe-Delimited

Problem: ThreatTypes can contain multiple values pipe-delimited (e.g., "Phish|Spam"). Using == will miss multi-category threats.

Solution: Always use has operator:

| where ThreatTypes has "Phish"   // ✅ Correct
| where ThreatTypes == "Phish"    // ❌ Misses "Phish|Spam"

4. Timestamp vs TimeGenerated

Problem: Advanced Hunting uses Timestamp for XDR-native tables. Sentinel Data Lake uses TimeGenerated.

Solution: Default queries use Timestamp (AH). If falling back to Data Lake, replace Timestamp with TimeGenerated throughout.

5. IsFirstContact May Be Null

Problem: IsFirstContact can be null for outbound or intra-org emails. Filtering on it without scoping to inbound emails may miss records.

Solution: Always filter EmailDirection == "Inbound" before using IsFirstContact.

6. LatestDeliveryAction vs DeliveryAction

Problem: DeliveryAction is the initial delivery disposition. LatestDeliveryAction reflects the current state after ZAP or manual remediation. Reporting only DeliveryAction overstates the number of threats in mailboxes.

Solution: When assessing current threat exposure, use LatestDeliveryAction and LatestDeliveryLocation. When assessing initial filter effectiveness, use DeliveryAction.

7. DKIM Pass Rate May Be Lower Than Expected

Problem: DKIM pass rate can appear low because many legitimate emails (especially bulk/marketing) don't sign with DKIM at all. An email with no DKIM signature isn't a DKIM "fail" — it simply has no result. The DKIM field from AuthenticationDetails may be empty or "none" rather than "fail".

Solution: When computing DKIM pass rate, note the denominator: emails with a DKIM result vs total emails. A lower DKIM rate is expected and doesn't necessarily indicate spoofing. Compare against DMARC and CompAuth for a better authentication picture.

8. ZAP ErrorCount May Include Non-Threat Emails

Problem: ZAP errors can occur for legitimate reasons: shared mailboxes, retention policies preventing purge, user-moved emails. A ZAP error doesn't always mean a threat is still active.

Solution: When reporting ZAP failures, note that manual investigation may confirm the threat was already handled. Don't over-alarm on ZAP errors without context.

9. ZAP TotalActions ≠ Threat ZAP Count

Problem: EmailPostDeliveryEvents includes all post-delivery events — not just ZAP threat remediations. The TotalActions count from Q6 includes system-initiated events (message trace updates, delivery location changes, admin investigation submissions). Reporting TotalActions as "ZAP Remediations" in Key Metrics massively overstates the threat remediation picture (e.g., 7,790 total when only 674 are actual threat ZAPs).

Solution: Always use ThreatZAPTotal (PhishZAP + MalwareZAP + SpamZAP) for headline ZAP metrics. Show TotalActions separately with a clarifying note: "includes system events". In Key Metrics, use "Threat ZAP Actions: 674" not "ZAP Remediations: 7,790".

10. Threat Block Rate — Post-ZAP vs Pre-Delivery

Problem: The scoring dimension "Threat Block Rate" can be interpreted two ways: (a) pre-delivery block rate (threats blocked before reaching inbox), or (b) final disposition rate (threats not in inbox after ZAP). These give different numbers — e.g., 72.5% pre-delivery vs 81.2% post-ZAP.

Solution: The dimension measures final threat disposition (post-ZAP) — the percentage of detected threats that are NOT currently in user inboxes. This is the operationally relevant metric because it reflects actual user exposure. The dimension description explicitly says "not in inbox (post-ZAP final state)".

---

Quality Checklist

Before delivering the report, verify:

• All percentage values show both percentage AND raw count
• All queries used Timestamp (AH) or TimeGenerated (Data Lake) consistently
• Zero-result queries are reported with explicit absence confirmation (✅ pattern)
• The Email Protection Score calculation is transparent with per-dimension evidence
• Detection methods show the full breakdown, not just "threats detected"
• ZAP effectiveness distinguishes threat ZAP count vs TotalActions (no inflated headline metric)
• Key Metrics ZAP row shows ThreatZAPTotal (Phish+Malware+Spam), NOT TotalActions
• Safe Links section distinguishes blocked vs allowed vs click-through
• Email authentication covers all four protocols with Other/None column
• Threats-in-mailbox summary breaks down phishing vs spam (not just total)
• Markdown report follows section ordering guidance (data → deep dives → score → assessment)
• Post-ZAP state (Q12) shows where threats currently reside, not just initial delivery
• MDO incidents section shows severity × status breakdown with open/closed/TP counts
• Incident queries use canonical SecurityAlert→SecurityIncident join (NOT SecurityAlert.Status)
• Recommendations are prioritized and evidence-based
• All hyperlinks copied verbatim from the URL Registry — no fabricated URLs
• No recipient PII (email addresses) in the report — aggregate by domain only
• Daily volume trend includes at least a note on peak/anomaly days

---

SVG Dashboard Generation

📊 Optional post-report step. After an Email Threat Protection report is generated, the user can request a visual SVG dashboard.

Trigger phrases: "generate SVG dashboard", "create a visual dashboard", "visualize this report", "SVG from the report"

How to Request a Dashboard

• Same chat: "Generate an SVG dashboard from the report" — data is already in context.
• New chat: Attach or reference the report file, e.g. #file:reports/email-threat-posture/Email_Threat_Protection_Report_<date>.md
• Customization: Edit svg-widgets.yaml before requesting — the renderer reads it at generation time.

Execution

Step 1:  Read svg-widgets.yaml (this skill's widget manifest)
Step 2:  Read .github/skills/svg-dashboard/SKILL.md (rendering rules — Manifest Mode)
Step 3:  Read the completed report file (data source)
Step 4:  Render SVG → save to reports/email-threat-posture/{report_name}_dashboard.svg

The YAML manifest is the single source of truth for layout, widgets, field mappings, colors, and data source documentation. All customization happens there.