# Asset and Vulnerability Report

**Created:** 2026-03-03  
**Platform:** Microsoft Defender XDR  
**Tables:** DeviceTvmSoftwareVulnerabilities, DeviceTvmSoftwareVulnerabilitiesKB, DeviceInfo, DeviceLogonEvents, AlertEvidence  
**Keywords:** vulnerability, CVE, CVSS, exposure, asset inventory, TVM, vulnerability management, exploit, patch, remediation, software, device, machine  
**MITRE:** T1190, T1203, T1068  
**Domains:** endpoint, exposure  
**Timeframe:** Current state (TVM tables are snapshots, not time-series)

---

## Column Availability Notes

| Requested Column | Available | Source | Notes |
|---|---|---|---|
| Name | ✅ | `DeviceTvmSoftwareVulnerabilities.DeviceName` | |
| DNS Name | ✅ | Same as DeviceName | DeviceName in Defender IS the FQDN |
| Last seen | ✅ | `DeviceInfo.Timestamp` (latest) | |
| Exposure level | ✅ | `DeviceInfo.ExposureLevel` | Low / Medium / High |
| Newest logon | ✅ | `DeviceLogonEvents` (pre-aggregated) | 90-day window |
| Most logons account | ✅ | `DeviceLogonEvents` (pre-aggregated) | Account with most interactive logons |
| Operating System | ✅ | `DeviceTvmSoftwareVulnerabilities.OSPlatform` + `OSVersion` | |
| CVE Name | ✅ | `DeviceTvmSoftwareVulnerabilities.CveId` | |
| Description | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.VulnerabilityDescription` | |
| Remediation | ✅ | `DeviceTvmSoftwareVulnerabilities.RecommendedSecurityUpdate` | Patch/update name |
| Severity | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.VulnerabilitySeverityLevel` | |
| CVSS v3 | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.CvssScore` | |
| EPSS score | ❌ | **Not in AH or Export API** | Only via FIRST.org public API post-export |
| Age (days) | ✅ | Computed from `PublishedDate` | |
| Published | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.PublishedDate` | |
| First detected | ❌ AH / ✅ Export | Export API: `FirstSeenTimestamp` | Per device × CVE; not in any AH table |
| Updated | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.LastModifiedTime` | |
| Has Exploit | ✅ | `DeviceTvmSoftwareVulnerabilitiesKB.IsExploitAvailable` | |
| Has Known Threats | ⚠️ | `CveTags` approximation | `HasKnownThreats` not in Export API (confirmed) |
| Has Associated Alerts | ✅ | Join with `SecurityAlert` | 90-day window |
| Related Software | ✅ | `DeviceTvmSoftwareVulnerabilities.SoftwareName` + `SoftwareVersion` | |
| Status | ❌ | **Not in AH tables** | Active/Patched/Exception is VM portal only |

---

## Quick Reference — Query Index

| # | Query | Use Case | Key Table |
|---|-------|----------|-----------|
| 1 | [Full Asset + Vulnerability Join (Priority-Filtered)](#query-1-full-asset--vulnerability-join-priority-filtered) | Investigation | `DeviceInfo` + multi |
| 2 | [Batched Export — Run in Alphabet Slices](#query-2-batched-export--run-in-alphabet-slices) | Investigation | `CvssScore` + multi |
| 2 | [b: Batched Export — Priority-Filtered per Batch](#query-2b-batched-export--priority-filtered-per-batch) | Investigation | `DeviceInfo` + multi |
| 3 | [Per-Device Rollup (Fits in AH for Any Environment)](#query-3-per-device-rollup-fits-in-ah-for-any-environment) | Investigation | `DeviceInfo` + multi |
| 4 | [Org-Wide CVE Summary (Executive / Dashboard View)](#query-4-org-wide-cve-summary-executive--dashboard-view) | Dashboard | `CvssScore` + multi |
| 5 | [High-Risk Workstation / Desktop CVEs](#query-5-high-risk-workstation--desktop-cves) | Triage | `DeviceInfo` + multi |
| 6 | [All Workstations — Per-Device Rollup (No Row-Limit Risk)](#query-6-all-workstations--per-device-rollup-no-row-limit-risk) | Investigation | `DeviceInfo` + multi |


## Scale Warning: Large Environments

Advanced Hunting has hard row limits: **30,000 rows in portal, 100,000 via API**.  
With a large device fleet × ~50–200 CVEs each, row counts easily reach into the hundreds of thousands. **AH will always truncate this.**

See the [Recommended Approach for Large Environments](#recommended-approach-for-large-environments) section at the bottom.

---

## Query 1: Full Asset + Vulnerability Join (Priority-Filtered)

This is the core query. In large environments you **must** apply at least one filter to stay under AH limits.
The default filter here targets the highest-priority subset: exploitable + Critical/High severity + High/Medium exposure.

```kql
// ──────────────────────────────────────────────────────────────
// ASSET & VULNERABILITY REPORT — Priority-Filtered
// Adjust the filters in the SCOPE CONTROLS block as needed.
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// ──────────────────────────────────────────────────────────────
// ── SCOPE CONTROLS (edit these) ───────────────────────────────
let OnlyExploitable = true;   // true = only CVEs with a public exploit (IsExploitAvailable == 1)
let SeverityFilter = dynamic(["Critical", "High"]);  // Add "Medium", "Low" to widen
let ExposureFilter = dynamic(["High", "Medium"]);    // Device exposure level filter
// ──────────────────────────────────────────────────────────────
// Pre-aggregate: latest device state per DeviceId
let LatestDeviceInfo = DeviceInfo
    | summarize arg_max(Timestamp, DeviceName, ExposureLevel, OSPlatform, OSVersion, MachineGroup) by DeviceId
    | where ExposureLevel in (ExposureFilter);
// Pre-aggregate: per-device logon counts, newest logon overall + most-active account
// Step 1: counts per device+account
let LogonBase = DeviceLogonEvents
    | where Timestamp > ago(90d)
    | where ActionType == "LogonSuccess"
    | where LogonType in ("Interactive", "RemoteInteractive", "Unlock")
    | summarize LogonCount = count(), NewestLogon = max(Timestamp) by DeviceId, AccountName;
// Step 2a: newest logon across all accounts per device
let LogonNewest = LogonBase
    | summarize NewestLogon = max(NewestLogon) by DeviceId;
// Step 2b: account with the highest logon count per device
let LogonMost = LogonBase
    | summarize arg_max(LogonCount, AccountName) by DeviceId
    | project DeviceId, MostLogonsAccount = AccountName, MostLogonsCount = LogonCount;
let LogonSummary = LogonNewest
    | join kind=leftouter LogonMost on DeviceId
    | project DeviceId, NewestLogon, MostLogonsAccount, MostLogonsCount;
// Pre-aggregate: distinct alert count per device (past 90 days)
// Uses AlertEvidence (AH table) joined on DeviceId — more reliable than SecurityAlert.CompromisedEntity
// Note: IsExploitAvailable is SByte/Int64, not bool — compare with == 1, not == true
let AlertedDevices = AlertEvidence
    | where Timestamp > ago(90d)
    | where isnotempty(DeviceId)
    | summarize AlertCount = dcount(AlertId) by DeviceId;
// Main join: vuln table → KB → DeviceInfo → logons → alerts
DeviceTvmSoftwareVulnerabilities
| where VulnerabilitySeverityLevel in (SeverityFilter)
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where OnlyExploitable == false or IsExploitAvailable == 1
) on CveId
| join kind=leftouter LatestDeviceInfo on DeviceId
| where isnotempty(DeviceName)
| join kind=leftouter LogonSummary on DeviceId
| join kind=leftouter AlertedDevices on DeviceId
// Output columns mapped to customer's requested fields
| project
    // ── Asset Data ──────────────────────────────────────
    Name                 = DeviceName,
    DNSName              = DeviceName,          // DeviceName in Defender IS the FQDN
    LastSeen             = Timestamp,
    ExposureLevel        = ExposureLevel,
    MachineGroup         = MachineGroup,
    NewestLogon          = NewestLogon,
    MostLogonsAccount    = MostLogonsAccount,
    OperatingSystem      = strcat(OSPlatform, " ", OSVersion),
    // ── Vulnerability Data ──────────────────────────────
    CVEName              = CveId,
    Description          = VulnerabilityDescription,
    Remediation          = RecommendedSecurityUpdate,
    Severity             = VulnerabilitySeverityLevel1,   // VulnerabilitySeverityLevel1 = KB's column (deduped after join)
    CvssV3               = CvssScore,
    // EPSS: not in AH or Export API — enrich post-export via FIRST.org: https://api.first.org/data/1.0/epss?cve=<CVE-ID>
    AgeDays              = datetime_diff('day', now(), PublishedDate),
    Published            = PublishedDate,
    Updated              = LastModifiedTime,
    HasExploit           = IsExploitAvailable,   // 1 = exploit available, 0 = not
    // HasKnownThreats: CveTags approximation — HasKnownThreats field not in Export API (confirmed)
    HasKnownThreats      = array_length(CveTags) > 0,
    HasZeroDay           = CveTags has "ZeroDay",
    HasAssociatedAlerts  = coalesce(AlertCount > 0, false),
    RelatedSoftware      = strcat(SoftwareName, " ", SoftwareVersion),
    SoftwareVendor       = SoftwareVendor,
    RecommendedUpdateId  = RecommendedSecurityUpdateId,
    // Status (Active/Patched/Exception) is NOT in AH or Export API — Defender portal only
    RawCveTags           = CveTags
| order by Name asc, Severity asc, AgeDays desc
```

---

## Query 2: Batched Export — Run in Alphabet Slices

When you need the full unfiltered dataset, split by first letter of DeviceName across multiple runs.
Each slice typically stays well under 100,000 rows even in large environments.

```kql
// ── CHANGE THE REGEX PER BATCH ────────────────────────────────
// Suggested batches (adjust based on your naming distribution):
//   Batch 1: @"^[a-fA-F]"   → devices starting with A–F
//   Batch 2: @"^[g-lG-L]"   → devices starting with G–L
//   Batch 3: @"^[m-rM-R]"   → devices starting with M–R
//   Batch 4: @"^[s-zS-Z]"   → devices starting with S–Z
//   Batch 5: @"^[^a-zA-Z]"  → devices starting with digits/symbols
// ──────────────────────────────────────────────────────────────
// Note: string range operators (>= / <) are not supported in AH;
//       use matches regex for letter-based batching.
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// ──────────────────────────────────────────────────────────────
let LatestDeviceInfo = DeviceInfo
    | summarize arg_max(Timestamp, DeviceName, ExposureLevel, OSPlatform, OSVersion) by DeviceId;
DeviceTvmSoftwareVulnerabilities
| where DeviceName matches regex @"^[a-fA-F]"   // <-- change this for each batch
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| join kind=leftouter LatestDeviceInfo on DeviceId
| project
    Name              = DeviceName,
    DNSName           = DeviceName,
    LastSeen          = Timestamp,
    ExposureLevel,
    OperatingSystem   = strcat(OSPlatform, " ", OSVersion),
    CVEName           = CveId,
    Description       = VulnerabilityDescription,
    Remediation       = RecommendedSecurityUpdate,
    Severity          = VulnerabilitySeverityLevel1,   // KB's column after join dedup
    CvssV3            = CvssScore,
    AgeDays           = datetime_diff('day', now(), PublishedDate),
    Published         = PublishedDate,
    Updated           = LastModifiedTime,
    HasExploit        = IsExploitAvailable,            // 1 = exploit available, 0 = not
    HasKnownThreats   = array_length(CveTags) > 0,
    RelatedSoftware   = strcat(SoftwareName, " ", SoftwareVersion)
| order by Name asc, Severity asc
```

Run this 4–5 times changing only the regex character class per batch. Export each run to CSV and combine externally.

---

## Query 2b: Batched Export — Priority-Filtered per Batch

Same batching approach as Query 2, but adds scope controls identical to Query 1.
Use this when you want the full column set across all devices, but only for high-priority CVEs —
so each batch fits comfortably under the 100k row cap without needing separate alert/logon joins.

```kql
// ── CHANGE THE REGEX AND SCOPE CONTROLS PER BATCH ─────────────
// Regex batches (same as Query 2):
//   Batch 1: @"^[a-fA-F]"   → devices starting with A–F
//   Batch 2: @"^[g-lG-L]"   → devices starting with G–L
//   Batch 3: @"^[m-rM-R]"   → devices starting with M–R
//   Batch 4: @"^[s-zS-Z]"   → devices starting with S–Z
//   Batch 5: @"^[^a-zA-Z]"  → devices starting with digits/symbols
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// ──────────────────────────────────────────────────────────────
// ── SCOPE CONTROLS (edit these) ───────────────────────────────
let OnlyExploitable = true;   // true = only CVEs with a public exploit (IsExploitAvailable == 1)
let SeverityFilter = dynamic(["Critical", "High"]);  // Add "Medium", "Low" to widen
let ExposureFilter = dynamic(["High", "Medium"]);    // Device exposure level filter
// ──────────────────────────────────────────────────────────────
let LatestDeviceInfo = DeviceInfo
    | summarize arg_max(Timestamp, DeviceName, ExposureLevel, OSPlatform, OSVersion) by DeviceId
    | where ExposureLevel in (ExposureFilter);
DeviceTvmSoftwareVulnerabilities
| where DeviceName matches regex @"^[a-fA-F]"   // <-- change this for each batch
| where VulnerabilitySeverityLevel in (SeverityFilter)
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where OnlyExploitable == false or IsExploitAvailable == 1
) on CveId
| join kind=inner LatestDeviceInfo on DeviceId
| project
    Name              = DeviceName,
    DNSName           = DeviceName,
    LastSeen          = Timestamp,
    ExposureLevel,
    OperatingSystem   = strcat(OSPlatform, " ", OSVersion),
    CVEName           = CveId,
    Description       = VulnerabilityDescription,
    Remediation       = RecommendedSecurityUpdate,
    Severity          = VulnerabilitySeverityLevel1,
    CvssV3            = CvssScore,
    AgeDays           = datetime_diff('day', now(), PublishedDate),
    Published         = PublishedDate,
    Updated           = LastModifiedTime,
    HasExploit        = IsExploitAvailable,
    HasKnownThreats   = array_length(CveTags) > 0,
    HasZeroDay        = CveTags has "ZeroDay",
    RelatedSoftware   = strcat(SoftwareName, " ", SoftwareVersion)
| order by Name asc, Severity asc, AgeDays desc
```

Run 4–5 times changing the regex per batch. The `ExposureFilter` join uses `kind=inner` so devices not matching the exposure level are excluded rather than producing nulls.

---

## Query 3: Per-Device Rollup (Fits in AH for Any Environment)

One row per device. Always fits within AH limits regardless of device count.

```kql
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// Note: IsExploitAvailable is SByte/Int64 — use == 1, not == true
// Note: VulnerabilitySeverityLevel (from vuln table) used in summarize;
//       VulnerabilitySeverityLevel1 (from KB) is available if you need the KB version.
DeviceTvmSoftwareVulnerabilities
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| join kind=leftouter (
    DeviceInfo | summarize arg_max(Timestamp, ExposureLevel, OSPlatform, OSVersion) by DeviceId
) on DeviceId
| summarize
    TotalCVEs         = dcount(CveId),
    CriticalCount     = countif(VulnerabilitySeverityLevel == "Critical"),
    HighCount         = countif(VulnerabilitySeverityLevel == "High"),
    MediumCount       = countif(VulnerabilitySeverityLevel == "Medium"),
    LowCount          = countif(VulnerabilitySeverityLevel == "Low"),
    ExploitableCVEs   = countif(IsExploitAvailable == 1),
    ZeroDayCVEs       = countif(CveTags has "ZeroDay"),
    OldestCVE         = min(PublishedDate),
    AvgAgeDays        = avg(datetime_diff('day', now(), PublishedDate)),
    ExposureLevel     = take_any(ExposureLevel),
    OperatingSystem   = take_any(strcat(OSPlatform, " ", OSVersion))
    by DeviceName
| extend RiskScore = (CriticalCount * 10) + (HighCount * 3) + ExploitableCVEs + (ZeroDayCVEs * 5)
| order by RiskScore desc
```

---

## Query 4: Org-Wide CVE Summary (Executive / Dashboard View)

Highest-level view — one row per CVE across the entire fleet.
Shows how many devices are affected by each CVE, sorted by impact.
Always fits in AH regardless of environment size.

```kql
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// Note: IsExploitAvailable is SByte/Int64 — use == 1, not == true
DeviceTvmSoftwareVulnerabilities
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| summarize
    AffectedDevices   = dcount(DeviceId),
    Severity          = take_any(VulnerabilitySeverityLevel1),
    CvssV3            = take_any(CvssScore),
    HasExploit        = max(IsExploitAvailable),        // 1 if any device's record has exploit
    HasZeroDay        = take_any(CveTags has "ZeroDay"),
    AgedDays          = max(datetime_diff('day', now(), PublishedDate)),
    Published         = take_any(PublishedDate),
    Description       = take_any(VulnerabilityDescription),
    Remediation       = take_any(RecommendedSecurityUpdate),
    AffectedSoftware  = make_set(SoftwareName, 10)      // up to 10 distinct software titles
    by CveId
| extend RiskScore = (AffectedDevices * case(Severity == "Critical", 10, Severity == "High", 5, Severity == "Medium", 2, 1))
    + (HasExploit * 20)
    + (iff(HasZeroDay, 30, 0))
| order by RiskScore desc, AffectedDevices desc
```

Paired with a fleet-level breakdown (run once, no joins needed):

```kql
// Fleet-wide severity breakdown — how many unique CVEs and affected devices per severity
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
DeviceTvmSoftwareVulnerabilities
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| summarize
    UniqueCVEs        = dcount(CveId),
    AffectedDevices   = dcount(DeviceId),
    ExploitableCVEs   = dcountif(CveId, IsExploitAvailable == 1),
    ZeroDayCVEs       = dcountif(CveId, CveTags has "ZeroDay")
    by Severity = VulnerabilitySeverityLevel1
| order by case(
    Severity == "Critical", 0,
    Severity == "High", 1,
    Severity == "Medium", 2,
    Severity == "Low", 3,
    4) asc
```

---

## Query 5: High-Risk Workstation / Desktop CVEs

Scoped to workstation-class devices only (excludes servers) with **Critical or High severity CVEs that have a known public exploit**.
This is the fastest path to prioritizing end-user device risk — the subset is small enough to fit in AH for most environments without batching.

> `DeviceType` comes from `DeviceInfo` and distinguishes device classes. Common values:
> `Workstation`, `Laptop`, `Desktop` (end-user devices) vs `Server`, `NetworkDevice`, `Printer` (infrastructure).

```kql
// ──────────────────────────────────────────────────────────────
// HIGH-RISK WORKSTATION / DESKTOP CVEs
// Scope: workstation-class devices only (Workstation, Laptop, Desktop)
//        CVEs must be Critical or High severity WITH a known public exploit.
//        Servers and infrastructure devices are excluded via inner join on DeviceType.
// Tested: Advanced Hunting (RunAdvancedHuntingQuery)
// ──────────────────────────────────────────────────────────────
// ── SCOPE CONTROLS (edit if needed) ───────────────────────────
let WorkstationTypes = dynamic(["Workstation", "Laptop", "Desktop"]);
// ──────────────────────────────────────────────────────────────
// Latest device snapshot — inner join ensures only workstation-class devices are returned
let LatestDeviceInfo = DeviceInfo
    | summarize arg_max(Timestamp, DeviceName, DeviceType, ExposureLevel, OSPlatform, OSVersion, MachineGroup) by DeviceId
    | where DeviceType in (WorkstationTypes);
// Main query — exploit required (IsExploitAvailable is SByte/Int64, compare with == 1 not == true)
DeviceTvmSoftwareVulnerabilities
| where VulnerabilitySeverityLevel in ("Critical", "High")
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where IsExploitAvailable == 1   // known public exploit only
) on CveId
| join kind=inner LatestDeviceInfo on DeviceId   // inner = non-workstation devices dropped
| extend SeverityRank = case(VulnerabilitySeverityLevel1 == "Critical", 0, 1)  // Critical first
| project
    // ── Asset ──────────────────────────────────────────────────
    Name              = DeviceName,
    DeviceType        = DeviceType,
    MachineGroup      = MachineGroup,
    ExposureLevel     = ExposureLevel,
    OperatingSystem   = strcat(OSPlatform, " ", OSVersion),
    // ── CVE ────────────────────────────────────────────────────
    CVEName           = CveId,
    Severity          = VulnerabilitySeverityLevel1,
    CvssV3            = CvssScore,
    HasZeroDay        = CveTags has "ZeroDay",
    AgeDays           = datetime_diff('day', now(), PublishedDate),
    Published         = PublishedDate,
    Description       = VulnerabilityDescription,
    Remediation       = RecommendedSecurityUpdate,
    RecommendedUpdateId = RecommendedSecurityUpdateId,
    RelatedSoftware   = strcat(SoftwareName, " ", SoftwareVersion),
    SoftwareVendor    = SoftwareVendor,
    SeverityRank
| order by SeverityRank asc, CvssV3 desc, Name asc
| project-away SeverityRank
```

---

## Query 6: All Workstations — Per-Device Rollup (No Row-Limit Risk)

When Query 5's severity/exploit filters exclude devices (e.g., 3,000 out of 11,000 workstations),
this rollup returns **one row per workstation** with a full CVE breakdown — all severities, all CVEs.
Guaranteed to fit within AH limits regardless of fleet size.

Use this to get the **complete workstation fleet picture**, then drill into specific devices as needed.

```kql
// ──────────────────────────────────────────────────────────────
// ALL WORKSTATIONS — PER-DEVICE CVE ROLLUP
// One row per workstation/laptop/desktop. No severity or exploit filter.
// Always fits in AH (row count = number of devices, not device × CVE).
// Tested: Advanced Hunting (RunAdvancedHuntingQuery) — 2026-03-04
// ──────────────────────────────────────────────────────────────
let WorkstationTypes = dynamic(["Workstation", "Laptop", "Desktop"]);
DeviceTvmSoftwareVulnerabilities
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| join kind=inner (
    DeviceInfo
    | summarize arg_max(Timestamp, DeviceName, DeviceType, ExposureLevel, OSPlatform, OSVersion, MachineGroup) by DeviceId
    | where DeviceType in (WorkstationTypes)
) on DeviceId
| summarize
    TotalCVEs       = dcount(CveId),
    CriticalCount   = countif(VulnerabilitySeverityLevel == "Critical"),
    HighCount       = countif(VulnerabilitySeverityLevel == "High"),
    MediumCount     = countif(VulnerabilitySeverityLevel == "Medium"),
    LowCount        = countif(VulnerabilitySeverityLevel == "Low"),
    ExploitableCVEs = countif(IsExploitAvailable == 1),
    ZeroDayCVEs     = countif(CveTags has "ZeroDay"),
    ExposureLevel   = take_any(ExposureLevel),
    MachineGroup    = take_any(MachineGroup),
    OperatingSystem = take_any(strcat(OSPlatform, " ", OSVersion))
    by DeviceName
| extend RiskScore = (CriticalCount * 10) + (HighCount * 3) + ExploitableCVEs + (ZeroDayCVEs * 5)
| order by RiskScore desc
```

**Why Query 5 returns fewer devices:** Query 5 filters to only `Critical`/`High` severity CVEs with `IsExploitAvailable == 1`. Devices that have **only** Medium/Low CVEs or no exploitable CVEs are excluded entirely by the `kind=inner` joins. This rollup removes those filters so every workstation with any CVE appears.

---

## Recommended Approach for Large Environments

For large environments with no row-limit constraint, use the **MDE Vulnerability Management Export API**.  
This returns SAS-signed download URLs to JSON/CSV blob files — no row limits, full dataset.

```powershell
# ── MDE Export API — Full Asset + Vulnerability Download ──────────────────────
#
# AUTHENTICATION — pick ONE block below, then proceed to step 2.
#
# ── Option A: Delegated auth (manual runs — uses your own logged-in identity) ──
# Prerequisites:
#   1. az login (or az login --tenant <tenant-id> if multi-tenant)
#   2. Your account needs the "Security Reader" or "Vulnerability Management" role in Defender
# No app registration required.
#
$TenantId = "<your-tenant-id>"   # e.g. "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$Token = (az account get-access-token `
    --resource "https://api.securitycenter.microsoft.com" `
    --tenant $TenantId | ConvertFrom-Json).accessToken

# ── Option B: App registration / service principal (automation / scheduled tasks) ──
# Prerequisites:
#   App registration with API permission: WindowsDefenderATP → Machine.Read.All (Application)
#   Admin consent granted.
#
# $TenantId     = "<your-tenant-id>"
# $ClientId     = "<app-registration-client-id>"
# $ClientSecret = "<client-secret>"
# $TokenBody = @{
#     grant_type    = "client_credentials"
#     client_id     = $ClientId
#     client_secret = $ClientSecret
#     scope         = "https://api.securitycenter.microsoft.com/.default"
# }
# $Token = (Invoke-RestMethod -Method Post `
#     -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" `
#     -Body $TokenBody).access_token

$Headers = @{ Authorization = "Bearer $Token" }

# 1. Request export — returns SAS-signed download URLs (no row limit)
# Available export endpoints:
#   /api/machines/SoftwareVulnerabilitiesExport       ← device × CVE rows
#   /api/machines/SoftwareInventoryExport             ← software inventory
#   /api/machines/SoftwareVulnerabilitiesKBExport     ← CVE reference data (KB)
$ExportResponse = Invoke-RestMethod `
    -Method Get `
    -Uri "https://api.securitycenter.microsoft.com/api/machines/SoftwareVulnerabilitiesExport" `
    -Headers $Headers

# 2. Download all parts (export is split into multiple gzip-compressed JSONL files)
$OutDir = ".\vuln_export_$(Get-Date -Format 'yyyyMMdd')"
New-Item -ItemType Directory -Path $OutDir -Force | Out-Null

$i = 1
foreach ($Url in $ExportResponse.exportFiles) {
    $OutFile = Join-Path $OutDir "vulnerabilities_part$i.json.gz"
    Write-Host "Downloading part $i of $($ExportResponse.exportFiles.Count)..."
    Invoke-WebRequest -Uri $Url -OutFile $OutFile
    $i++
}

Write-Host "Downloaded $($i-1) file(s) to $OutDir"

# 3. Decompress gzip and parse JSONL (one JSON object per line - NOT a JSON array)
# Export files are gzip-compressed; each line inside is an independent JSON object.
Add-Type -AssemblyName System.IO.Compression
$AllRows = foreach ($File in Get-ChildItem $OutDir -Filter "*.json.gz") {
    Write-Host "Parsing $($File.Name)..."
    $fs  = [System.IO.File]::OpenRead($File.FullName)
    $gz  = [System.IO.Compression.GzipStream]::new($fs, [System.IO.Compression.CompressionMode]::Decompress)
    $sr  = [System.IO.StreamReader]::new($gz)
    while (-not $sr.EndOfStream) {
        $line = $sr.ReadLine()
        if ($line.Trim()) { $line | ConvertFrom-Json }
    }
    $sr.Dispose(); $gz.Dispose(); $fs.Dispose()
}

# Available columns (confirmed from live export):
#   Id, DeviceName, RbacGroupName, OSPlatform, OSVersion, OSArchitecture,
#   SoftwareVendor, SoftwareName, SoftwareVersion,
#   CveId, CvssScore, VulnerabilitySeverityLevel, ExploitabilityLevel,
#   IsOnboarded, LastSeenTimestamp, FirstSeenTimestamp,
#   RecommendationReference, SecurityUpdateAvailable,
#   RecommendedSecurityUpdate, RecommendedSecurityUpdateId, RecommendedSecurityUpdateUrl,
#   CveBatchUrl, CveBatchTitle, DeviceId, RbacGroupId

$AllRows | Export-Csv ".\asset_vulnerability_report_$(Get-Date -Format 'yyyyMMdd').csv" -NoTypeInformation

Write-Host "Export complete. Total rows: $($AllRows.Count)"
```

### What the Export API provides vs. Advanced Hunting

> Columns confirmed from live export test (`SoftwareVulnerabilitiesExport`).

| Field | AH Available | Export API |
|---|---|---|
| `FirstSeenTimestamp` (first detected) | ❌ | ✅ |
| `ExploitabilityLevel` (text: Exploitable, etc.) | Partial (`IsExploitAvailable`) | ✅ |
| `SecurityUpdateAvailable` (bool) | ❌ | ✅ |
| `OSArchitecture` | ❌ | ✅ |
| `RbacGroupName` / `RbacGroupId` | ❌ | ✅ |
| `LastSeenTimestamp` per device | ✅ (DeviceInfo) | ✅ |
| `CvssScore`, `VulnerabilitySeverityLevel` | ✅ | ✅ |
| `RecommendedSecurityUpdate` + URL | ✅ (KB table) | ✅ |
| Row limit | 100k (API) | **No limit** |
| Freshness | Near-real-time | ~1–3 hour lag |
| File format | N/A | gzip-compressed JSONL |

### Decision Guide

```
What do you need?
│
├─ Org-wide summary / exec dashboard (no per-device detail needed)
│   ├─ Fleet breakdown by severity (4 rows total)  ──────────────►  Query 4 fleet breakdown
│   └─ Top CVEs by blast radius across fleet  ───────────────────►  Query 4 CVE summary
│
├─ Per-device risk ranking (one row per device, always fits in AH)  ►  Query 3
│
├─ Workstations / desktops only
│   ├─ High/critical + known exploit only  ───────────────────────►  Query 5
│   └─ All severities, complete fleet rollup (1 row/device)  ────►  Query 6
│
├─ Full detail per device × CVE (joins logons, alerts, exposure)
│   ├─ Priority subset only (exploitable + Critical/High)  ───────►  Query 1
│   └─ All severities, all devices
│       ├─ No row-cap concern, want simplest approach  ───────────►  MDE Export API (PowerShell)
│       ├─ Priority-filtered batches (fewer runs needed)  ────────►  Query 2b
│       └─ Full unfiltered batches  ──────────────────────────────►  Query 2
```

**Quick reference:**

| Query | Rows | Scope | Always fits in AH? |
|---|---|---|---|
| Export API | No limit | Full, all columns incl. FirstSeen | N/A (PowerShell) |
| Query 1 | Filtered subset | Priority: exploitable + High/Critical + exposed | Usually yes |
| Query 2 | Full (batched) | All devices × all CVEs, run per alphabet slice | Yes per batch |
| Query 2b | Filtered (batched) | Priority-filtered batches, fewer runs needed | Yes per batch |
| Query 3 | 1 row/device | Per-device rollup with RiskScore | Yes, always |
| Query 4 | 1 row/CVE + 4 severity rows | Org-wide CVE impact summary | Yes, always |
| Query 5 | Filtered subset | Workstations/desktops only, Critical/High + known exploit | Yes, always |
| Query 6 | 1 row/workstation | All workstations rollup, all severities, no exploit filter | Yes, always |

---

## Notes on Specific Columns

### "Has Known Threats"
The AH approximation (`array_length(CveTags) > 0`) checks whether any TVM tags exist (e.g., `ZeroDay`).
`HasKnownThreats` is **not** a field in the Export API (confirmed via live test — not present in `SoftwareVulnerabilitiesExport` columns).
The best available signal in both AH and Export API is `ExploitabilityLevel` (text) / `IsExploitAvailable` (int).
CveTags documented values include: `ZeroDay`, `NoSecurityUpdate`.

### "Status" (Active / Patched / Exception)
This is a Defender Vulnerability Management portal concept reflecting whether the vulnerability
is actively present, was remediated, or has a management exception. Not queryable via AH.
`Status` is **not** present in `SoftwareVulnerabilitiesExport` (confirmed via live test).
It is visible in the Defender portal (Vulnerability Management → Weaknesses) but not exposed via any API export endpoint at this time.

### EPSS Score
Exploit Prediction Scoring System score from FIRST.org. Not ingested into AH tables.
`EpssScore` is **not** present in `SoftwareVulnerabilitiesExport` (confirmed via live test — field does not exist in the export).
The only way to enrich with EPSS is the FIRST.org public API post-export:
  GET https://api.first.org/data/1.0/epss?cve=CVE-2021-44228

### "First Detected" per Device
The date a specific CVE was first detected on a specific device. Available in Export API
as `FirstSeenTimestamp`. Not available in any AH table.
