Windows Server & Client Missing Updates

DeviceTvmSoftwareVulnerabilities | where SoftwareVendor == 'microsoft' | where SoftwareName has_any ('windows_11','windows_10','Windows_Server') | where isnotempty(RecommendedSecurityUpdate) | distinct DeviceId, RecommendedSecurityUpdate, RecommendedSecurityUpdateId, SoftwareName | join kind=leftouter ( DeviceInfo | where isnotempty(OSPlatform) | where OnboardingStatus == 'Onboarded' | where isnotempty(OSVersionInfo) | summarize arg_max(Timestamp, *) by DeviceId) on $left.DeviceId == $right.DeviceId | summarize MissingDevices = make_set(DeviceName) by SoftwareName | extend TotalMissingKBDevice = array_length(MissingDevices) | project ['Total Exposed devices'] = TotalMissingKBDevice, SoftwareName

Explanation

This KQL query is designed to identify Windows devices that are missing important security updates. Here's a simplified explanation of what the query does:

Data Source: It starts by looking at the DeviceTvmSoftwareVulnerabilities table, which contains information about software vulnerabilities on devices.
Filter Criteria:
- It filters for vulnerabilities related to Microsoft software.
- It specifically looks for devices running Windows 10, Windows 11, or Windows Server.
- It checks for entries where there is a recommended security update available.
Distinct Entries: The query selects distinct combinations of device IDs, recommended security updates, update IDs, and software names.
Join with Device Info: It performs a left outer join with the DeviceInfo table to get additional information about the devices, such as their operating system platform and version, but only for devices that are onboarded.
Summarize Missing Devices: It groups the data by software name and creates a list of devices that are missing the recommended updates.
Calculate Total Missing: It calculates the total number of devices missing updates for each software name.
Project Results: Finally, it outputs the total number of exposed devices (those missing updates) for each software name.

In summary, this query identifies and counts Windows devices that are missing critical security updates, categorized by the type of Windows software they are running.

KQL Search