Query Details
MDE Windows Server Client Missing Updates Summary
Query
# Windows Server & Client Missing Updates
## Query Information
### Description
#### References
### Microsoft 365 Defender
```kql
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
DeviceTvmSoftwareVulnerabilitiestable, 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
DeviceInfotable 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.
Details
Alex Verboon
Released: June 15, 2025
Tables
Keywords
Operators