I’ve created a hunting query that that tallies 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 won’t 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
Brown Lightning is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Have you tried leftouter join instead of the default inner join in KQL.
An inner join will only return rows that have matching values in both tables, which is why devices without vulnerabilities are not appearing in your results.
A leftouter join, on the other hand, will return all rows from the left table (in this case DeviceInfo), and the matched rows from the right table (DeviceTvmSoftwareVulnerabilities).
If there is no match, the query will still return the row from the left table with null values for the columns of the right table.
Here’s an updated version of your query that uses a leftouter join and also includes handling for devices that have no vulnerabilities by ensuring that arrays do not become null:
DeviceInfo
| join kind=leftouter (DeviceTvmSoftwareVulnerabilities | where MachineGroup contains "example") on DeviceId
| extend SoftwareName = coalesce(SoftwareName, "") // Ensures SoftwareName is never null
| summarize
['Critical Severity Vulnerabilities'] = make_set_if(CveId, SoftwareName contains "server" and VulnerabilitySeverityLevel == "Critical"),
['High Severity Vulnerabilities'] = make_set_if(CveId, SoftwareName contains "server" and VulnerabilitySeverityLevel == "High"),
['Medium Severity Vulnerabilities'] = make_set_if(CveId, SoftwareName contains "server" and VulnerabilitySeverityLevel == "Medium")
by DeviceName
| project
DeviceName,
CVEServerCritical = array_length((['Critical Severity Vulnerabilities'])),
CVEServerHigh = array_length((['High Severity Vulnerabilities'])),
CVEServerMedium = array_length((['Medium Severity Vulnerabilities']))