Vulnerability Action Treemap

// VULNERABILITY ACTION TREEMAP
// Scope: Windows 7 / Windows 10 / Windows 11 endpoints
// Excludes: Windows Server and Microsoft software
// Current = vulnerabilities 30 days old or less
// Backlog = vulnerabilities more than 30 days old

let Devices =
    DeviceInfo
    | summarize arg_max(Timestamp, *) by DeviceId
    | where OSPlatform in~ ("Windows7", "Windows10", "Windows11")
    | project DeviceId, DeviceName;

DeviceTvmSoftwareVulnerabilities
| join kind=inner Devices on DeviceId
| join kind=leftouter (
    DeviceTvmSoftwareVulnerabilitiesKB
    | project CveId,
              PublishedDate,
              VulnerabilitySeverityLevel,
              IsExploitAvailable
) on CveId
| join kind=leftouter (
    DeviceTvmSoftwareInventory
    | summarize arg_max(Timestamp, *) by DeviceId, SoftwareName
    | project DeviceId, SoftwareName, SoftwareVendor
) on DeviceId
| where isnotempty(SoftwareName)
| where SoftwareVendor !~ "Microsoft"
| extend
    Weaknesses = 1,
    AgeDays = datetime_diff("day", now(), PublishedDate),
    Critical = iff(VulnerabilitySeverityLevel =~ "Critical", 1, 0),
    High = iff(VulnerabilitySeverityLevel =~ "High", 1, 0),
    Exploitable = iff(IsExploitAvailable == 1, 1, 0)
| extend
    Current = iff(AgeDays <= 30, 1, 0),
    Backlog = iff(AgeDays > 30, 1, 0),
    NextAction = case(
        Exploitable == 1 and VulnerabilitySeverityLevel =~ "Critical", "Emergency remediation",
        VulnerabilitySeverityLevel =~ "Critical", "Patch critical vulnerabilities",
        Exploitable == 1 and VulnerabilitySeverityLevel =~ "High", "Patch exploitable high vulnerabilities",
        VulnerabilitySeverityLevel =~ "High", "Patch high vulnerabilities",
        AgeDays > 30, "Immediate remediation plan required",
        "Continue monitoring"
    )
| summarize
    Weaknesses = sum(Weaknesses),
    Current = sum(Current),
    Backlog = sum(Backlog),
    Critical = sum(Critical),
    High = sum(High),
    Exploitable = sum(Exploitable),
    ExposedMachines = dcount(DeviceId)
    by SoftwareName, NextAction
| extend
    CriticalHigh = Critical + High,
    TotalMachines = toscalar(Devices | summarize dcount(DeviceId)),
    ExposedPercentage = iff(TotalMachines > 0, round(100.0 * todouble(ExposedMachines) / todouble(TotalMachines), 2), 0.0)
| project
    SoftwareName,
    NextAction,
    Weaknesses,
    Current,
    Backlog,
    Critical,
    High,
    CriticalHigh,
    Exploitable,
    ExposedMachines,
    TotalMachines,
    ExposedPercentage
| order by Current desc