scstelz/email-threat-posture

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: <N>) |
| Threats Blocked Pre-Delivery | <N> |
| Phishing Delivered (Now Remediated) | <N> |
| Threat ZAP Actions | <N> (Phish: <N>, Malware: <N>, Spam: <N>) |
| Total Post-Delivery Actions | <N> (includes system events) |
| ZAP Success Rate | <N>% (Failed: <N>) |
| Threats Still in Mailboxes (Post-ZAP) | <N> (Phish: <N>, Spam: <N>) |
| Safe Links Clicks Scanned | <N> |
| Phishing Click-Throughs | <N> |
| Distinct Senders | <N> |
| Protected Mailboxes | <N> |

---

## 📬 Mail Flow Overview

### Daily Volume Trend

<Table or sparkline showing inbound/outbound/intra-org by day>

| Day | Inbound | Outbound | Intra-org |
|-----|---------|----------|-----------|
| <date> | <N> | <N> | <N> |

**Observations:** <Note any spikes, trends, or anomalies>

---

## 🛡️ Threat Composition

### Threat Categories

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

### Detection Methods

| Method | Count |
|--------|-------|
| <method> | <N> |

### Top Phishing Sender Domains

| Domain | Phish Count | Delivered | Recipients Hit |
|--------|-------------|-----------|----------------|
| <domain> | <N> | <N> | <N> |

<If Q9 returns 0 phishing domains:>
✅ No phishing sender domains detected.

---

## 📦 Delivery Disposition

### Initial Delivery Action

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

### Post-ZAP Threat State

<Shows where threats currently reside after ZAP remediation>

| Latest Action | Location | Threat Type | Count |
|---------------|----------|-------------|-------|
| <action> | <location> | <type> | <N> |

**Summary of current threat locations (post-ZAP):**

| Current Location | Threat Count | % of Threats |
|-----------------|-------------|-------------|
| 🟢 Quarantine | <N> | <N>% |
| 🟢 Junk folder | <N> | <N>% |
| 🟢 Blocked/Dropped/Failed | <N> | <N>% |
| 🟢 Deleted items | <N> | <N>% |
| 🔴 **Still in Inbox** | **<N>** | **<N>%** |
| **Total** | **<N>** | **100%** |

> Show the phishing vs spam breakdown for "Still in Inbox": e.g., "<N> phishing (<N> total threats including spam)"

---

## 🔐 Email Authentication

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

> **Note:** "Other/None" = emails with no result for that protocol (e.g., no DKIM signature). A low DKIM pass rate with 0 failures means unsigned senders, not spoofing. Compare against DMARC and CompAuth for the complete authentication picture.

**Assessment:**
- <emoji> <finding for each protocol>

---

## 🧹 Post-Delivery Remediation (ZAP)

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

> **Reporting guidance:** The Key Metrics "Threat ZAP Actions" row should show **only** the Phish + Malware + Spam ZAP count — NOT the TotalActions, which includes system-initiated post-delivery events (message trace updates, delivery location changes). TotalActions is shown separately with a clarifying note.

<If ErrorCount > 0:>
⚠️ **<N> ZAP remediation(s) failed** — manual follow-up recommended. Threats may remain in user mailboxes.

<If ErrorCount == 0:>
✅ All post-delivery remediations completed successfully.

---

## 🔗 Safe Links Protection

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

<If PhishClicks > 0:>
🔴 **<N> phishing URL click(s) detected** — investigate affected users for credential compromise.

<If PhishClicks == 0:>
✅ No phishing URL click-throughs detected.

---

## 📎 Attachment Analysis

### Top Attachment Types

| File Type | Count | Distinct Files | Threats Detected |
|-----------|-------|----------------|------------------|
| <type> | <N> | <N> | <N> |

<If any ThreatCount > 0:>
⚠️ **Malicious attachments detected in <N> file type(s)** — verify delivery status and endpoint execution.

<If all ThreatCount == 0:>
✅ No malicious attachments detected in email flow.

---

## 🎯 Targeted Recipients

| Recipient Domain | Threat Count | Phish | Spam | Malware | Recipients |
|-----------------|-------------|-------|------|---------|------------|
| <domain> | <N> | <N> | <N> | <N> | <N> |

---

## Email Protection Score Card

```
┌──────────────────────────────────────────────────────┐
│       EMAIL PROTECTION SCORE: <NN>/100               │
│             Rating: <EMOJI> <RATING>                 │
├──────────────────────────────────────────────────────┤
│ Threat Block Rate    [<bar>] <N>/20  (<detail>)      │
│ Email Authentication [<bar>] <N>/20  (<detail>)      │
│ ZAP Effectiveness    [<bar>] <N>/20  (<detail>)      │
│ Safe Links Protection[<bar>] <N>/20  (<detail>)      │
│ Phishing Delivery    [<bar>] <N>/20  (<detail>)      │
└──────────────────────────────────────────────────────┘
```

---
## 🚨 MDO Security Incidents

### Incident Summary (Last <N> Days)

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

### Top MDO Incident Types

| Alert Name | Severity | Incidents | Open | Closed | True Positives |
|------------|----------|-----------|------|--------|----------------|
| <name> | <sev> | <N> | <N> | <N> | <N> |

<If Q13 returns 0 incidents:>
✅ No MDO-generated security incidents in the analysis period.

---
## Security Assessment

| Factor | Finding |
|--------|---------|
| <emoji> **<Factor>** | <Evidence-based finding> |

---

## Recommendations

1. <emoji> **<Priority action>** — <evidence and rationale>
2. ...

---

## Appendix: Query Execution Summary

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

---

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.