let WindowsPatchVulns =
DeviceTvmSoftwareVulnerabilities
| where OSPlatform startswith "Windows"
| where isnotempty(RecommendedSecurityUpdateId)
| join kind=leftouter (
DeviceTvmSoftwareVulnerabilitiesKB
| project CveId, PublishedDate, IsExploitAvailable, CvssScore
) on CveId
| extend PatchAgeDays = datetime_diff("day", now(), PublishedDate);
WindowsPatchVulns
| summarize
OldestMissingPatchDays = max(PatchAgeDays),
OldestVulnPublished = min(PublishedDate),
MissingKBs = make_set(RecommendedSecurityUpdateId, 20),
MissingUpdateNames = make_set(RecommendedSecurityUpdate, 20),
CriticalCount = countif(VulnerabilitySeverityLevel == "Critical"),
HighCount = countif(VulnerabilitySeverityLevel == "High"),
ExploitableCount = countif(IsExploitAvailable == true),
TotalVulns = dcount(CveId)
by DeviceName, DeviceId, OSPlatform, OSVersion
| extend PatchBucket = case(
OldestMissingPatchDays <= 30, "0-30 days",
OldestMissingPatchDays <= 60, "31-60 days",
OldestMissingPatchDays <= 90, "61-90 days",
OldestMissingPatchDays <= 180, "91-180 days",
"180+ days"
)
| order by OldestMissingPatchDays desc