KQL Search

Query Details

HomeAI ToolsDevice Query

Critical Vulnerabilities

Query

let VersionsWithCVEs = DeviceTvmSoftwareVulnerabilities
| distinct SoftwareName, SoftwareVersion, CveId
| join kind=leftouter DeviceTvmSoftwareVulnerabilitiesKB on CveId  // preserves same CVE on older versions
| where VulnerabilitySeverityLevel == "Critical"
| summarize CriticalCVEs = count() by SoftwareName, SoftwareVersion;
let VersionsWithExploits = DeviceTvmSoftwareVulnerabilities
| distinct SoftwareName, SoftwareVersion, CveId
| join kind=leftouter DeviceTvmSoftwareVulnerabilitiesKB on CveId  // preserves same CVE on older versions
| where VulnerabilitySeverityLevel == "Critical"
| where IsExploitAvailable == 1
| summarize ExploitsAvailable = count() by SoftwareName, SoftwareVersion, Exploitable = "Yes";
DeviceTvmSoftwareInventory
| summarize Devices = count() by SoftwareName, SoftwareVersion
| join VersionsWithCVEs on SoftwareName, SoftwareVersion
| join kind=leftouter VersionsWithExploits on SoftwareName, SoftwareVersion  // preverves vulnerabilities without exploits
| project SoftwareName, SoftwareVersion, Devices, CriticalCVEs, Exploitable, ExploitsAvailable
| where CriticalCVEs > 0
| sort by ExploitsAvailable desc, SoftwareName asc, SoftwareVersion asc

Explanation

This query is summarizing data related to software vulnerabilities and exploits.

First, it identifies the software versions with critical vulnerabilities and counts the number of critical vulnerabilities for each software version.

Then, it identifies the software versions with exploits available and counts the number of exploits available for each software version.

Next, it summarizes the number of devices using each software version.

It then joins the data from the previous steps based on the software name and version.

Finally, it selects and sorts the resulting data, only including software versions with at least one critical vulnerability, and sorting by the number of exploits available in descending order, followed by the software name and version in ascending order.

Details

C.J. May profile picture

C.J. May

Released: January 3, 2022

Tables

DeviceTvmSoftwareVulnerabilitiesDeviceTvmSoftwareVulnerabilitiesKBDeviceTvmSoftwareInventory

Keywords

DeviceTvmSoftwareVulnerabilities,DeviceTvmSoftwareVulnerabilitiesKB,VulnerabilitySeverityLevel,Critical,SoftwareName,SoftwareVersion,CveId,IsExploitAvailable,Exploitable,DeviceTvmSoftwareInventory,Devices,ExploitsAvailable

Operators

distinctjoinkindwheresummarizecountbyleftouterprojectsortasc

Actions