KQL Search

let missingkb = 
// Details missing KBs Windows Server
DeviceTvmSoftwareVulnerabilities
| where SoftwareVendor == 'microsoft'
| where SoftwareName startswith '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 MissingKBs = make_set(RecommendedSecurityUpdate) by DeviceName
| extend TotalMissingKB = array_length(MissingKBs);
DeviceInfo
| where OnboardingStatus == "Onboarded"
| where isnotempty(OSPlatform) 
| summarize arg_max(Timestamp,*) by DeviceName
| where OSPlatform contains "WindowsServer2012R2" or OSPlatform contains "WindowsServer2016"
| extend Agent = case(ClientVersion startswith "10.3720", "MMA", ClientVersion startswith "10.8", "UnifiedClient","Other")
| summarize by DeviceName, OSPlatform, OnboardingStatus,Agent, ClientVersion
| sort by ClientVersion asc
| join kind=leftouter  (missingkb)
on $left. DeviceName ==  $right.DeviceName