Email Threat Posture

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

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

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

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

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")

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

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)

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)

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

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

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

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

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

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

reports/email-threat-posture/Email_Threat_Protection_Report_YYYYMMDD_HHMMSS.md

# 📧 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> |

reports/email-threat-posture/Email_Threat_Protection_Report_YYYYMMDD_HHMMSS.md