Process – [CE A6.4] Exporting a list of Missing Security Patches for Windows devices
- Go to Microsoft Defender Admin Portal – security.microsoft.com
- Click on Hunting
- Click on Advanced Hunting
- Paste in the following KQL query, depending on if you want Windows 10 devices or Windows Servers:
- Windows 10 Devices:
DeviceTvmSoftwareVulnerabilities
| where OSPlatform == "Windows10"
| where isnotempty(RecommendedSecurityUpdate)
| summarize MissingPatches = make_set(RecommendedSecurityUpdate), PatchCount = dcount(RecommendedSecurityUpdate) by DeviceName
| join kind=inner (
DeviceNetworkEvents
| summarize LastSeen = max(Timestamp) by DeviceName
) on DeviceName
| join kind=inner (
DeviceLogonEvents
| summarize arg_max(Timestamp, *) by DeviceName
| project DeviceName, LastLoggedOnUser = AccountName
) on DeviceName
| project DeviceName, LastLoggedOnUser, LastSeen, PatchCount , MissingPatches
DeviceTvmSoftwareVulnerabilities
| where OSPlatform startswith "WindowsServer"
| where isnotempty(RecommendedSecurityUpdate)
| summarize MissingPatches = make_set(RecommendedSecurityUpdate), PatchCount = dcount(RecommendedSecurityUpdate) by DeviceName
| join kind=inner (
DeviceNetworkEvents
| summarize LastSeen = max(Timestamp) by DeviceName
) on DeviceName
| join kind=inner (
DeviceLogonEvents
| summarize arg_max(Timestamp, *) by DeviceName
| project DeviceName, LastLoggedOnUser = AccountName
) on DeviceName
| project DeviceName, LastSeen, PatchCount, MissingPatches
- Click on Run
- Click on Export to export to a .CSV
