Query

DeviceTvmSoftwareVulnerabilities
| project DeviceName, VulnerabilitySeverityLevel
| evaluate pivot(VulnerabilitySeverityLevel): (DeviceName: string,Critical: long, High: long, Medium: long, Low: long)
| sort by Critical, High, Medium, Low

About this query

List all the vulnerabilities each device has in a pivot table

Query Information

Description

This query returns a row for each device with the amount of Critical, High, Medium and Low Vulnerabilities that device has.

Defender XDR

Explanation

This query is designed to create a summary table that shows the number of vulnerabilities on each device, categorized by their severity levels: Critical, High, Medium, and Low. Here's a simple breakdown of what the query does:

Data Source: It starts by accessing the DeviceTvmSoftwareVulnerabilities table, which contains information about software vulnerabilities on devices.
Select Relevant Columns: It selects two columns from this table: DeviceName (the name of the device) and VulnerabilitySeverityLevel (the severity level of each vulnerability).
Pivot Table Creation: It uses the evaluate pivot function to transform the data into a pivot table format. This means it organizes the data so that each row corresponds to a device, and the columns represent the count of vulnerabilities at each severity level (Critical, High, Medium, Low).
Sorting: Finally, it sorts the resulting table by the number of vulnerabilities in descending order, starting with Critical, then High, Medium, and Low.

In summary, the query generates a table that lists each device along with the count of its vulnerabilities, organized by severity, and sorts the devices based on the severity of their vulnerabilities.

KQL Search