Hope you can help. Im new to KQL and need some help. I’ve created a hunting query that that tally’s the number of Critical and High severity vulnerabilities up per device. Using this is quicker that the GUI and was fun to dip my feet in kql a bit. It works great, except if a device has no vulnerabilities, it simply wont appear in the results. What is the best way of going about getting all devices to appear and if they have zero vulnerabilities to display a 0?
Example Output
Code
DeviceInfo
|join DeviceTvmSoftwareVulnerabilities on DeviceId
| where MachineGroup contains "example"
| summarize ['Critical Severity Vulnerabilities']=make_set_if(CveId,SoftwareName contains "server" and SoftwareName and VulnerabilitySeverityLevel == "Critical"),
['High Severity Vulnerabilities']=make_set_if(CveId, SoftwareName contains "server" and SoftwareName and VulnerabilitySeverityLevel == "High"),
by DeviceName
| project DeviceName , CVEServerCritical=array_length((['Critical Severity Vulnerabilities'])),CVEServerHigh=array_length((['High Severity Vulnerabilities']))
Tried different joining/union commands
New contributor
Brown Lightning is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.