Using Advanced Hunting Query to identify Devices missing patch

[Paul Bendall]

Morning,

Using the following scenario as an example. Microsoft recategorised CVE-2022-37958 in December 2022, it was initially patched in September 2022. 

I want to query my environment to determine the level of exposure and turned to Advanced Hunting to generate a query. IT looks like the table "DeviceTvmSoftwareVulnerabilities" contains relevant information regarding missing patches. I'm not experienced with generating KQL queries but came up with:

DeviceTvmSoftwareVulnerabilities  
| where RecommendedSecurityUpdate contains "September 2022 Security Updates"
| summarize by DeviceId, DeviceName, RecommendedSecurityUpdate

Is there a better, more optimised way to meet the original requirement?

Paul

[#2]

@Paul Bendall

Your approach is fine but if you want to be specific about the CVE, the following will do the trick:

//Amend CVE list in first line to suit
 let CVEcheck = dynamic(["CVE-2022-37958","CVE-XXXX-XXXXX"]);
 DeviceTvmSoftwareVulnerabilities 
 |where CveId in (CVEcheck)
 |summarize Securitypatches= count(),make_set(CveId) by DeviceName,OSPlatform,KBID=RecommendedSecurityUpdateId