Applied KQL

Helping others get things done using Kusto Query Language (KQL).

KQL query: List devices vulnerable to CISA known exploited vulnerabilities

by

A list of devices vulnerable to CISA known exploited vulnerabilities - Copilot Designer
A list of devices vulnerable to CISA known exploited vulnerabilities – Copilot Designer

Identify devices in your Defender tenant that are vulnerable to known exploited vulnerabilities maintained by CISA.

Prerequisites for using this KQL query

To use this KQL query, your devices must be licensed for Defender Threat and Vulnerability Management. You can run this query from Defender > Hunting > Advanced hunting.

KQL query to list devices vulnerable to CISA known exploited vulnerabilities

let KnowExploitedVulnsCISA = externaldata(cveID: string, vendorProject: 
string, product: string, vulnerabilityName: string, dateAdded: datetime, 
shortDescription: string, requiredAction: string, dueDate: datetime, 
notes: string)
[@"https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv"] 
with (format="csv", ignoreFirstRecord=True);
DeviceTvmSoftwareVulnerabilities
| join KnowExploitedVulnsCISA on $left.CveId == $right.cveID
| summarize
     TotalVulnerableDevices = count(),
     DeviceList = make_set(DeviceName),
     Description = make_set(shortDescription)
     by cveID
| sort by TotalVulnerableDevices desc

// credit: Bert-JanP/Hunting-Queries-Detection-Rules (github.com)

This returns a result set with columns cveID, TotalVulnerableDevices, DeviceList (array of vulnerable device names), and Description (array of short descriptions from the CVE).

Deconstructing the KQL query

Read on to understand HOW the query above works.

The objective of this query is to analyze known exploited vulnerabilities (CVEs) and their impact on vulnerable devices. It identifies which vulnerabilities impact the most devices and provide relevant details for further analysis.

let KnowExploitedVulnsCISA = externaldata(cveID: string, vendorProject: 
string, product: string, vulnerabilityName: string, dateAdded: datetime, 
shortDescription: string, requiredAction: string, dueDate: datetime, 
notes: string)
[@"https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv"] 
with (format="csv", ignoreFirstRecord=True);

We start by using externaldata to define an external data source called KnowExploitedVulnsCISA.

  • It reads data from a CSV file hosted at this URL.
  • The CSV file contains columns and we specify the data type to use for those columns.
  • We ignore the first record (header) in the CSV using ignoreFirstRecord=True.

This is a great example of how you can pull in external data to enrich your query results.

DeviceTvmSoftwareVulnerabilities
| join KnowExploitedVulnsCISA on $left.CveId == $right.cveID

We perform a join between DeviceTvmSoftwareVulnerabilities and KnowExploitedVulnsCISA. We match rows where the CveId in the left table (DeviceTvmSoftwareVulnerabilities) equals the cveID in the right table (KnowExploitedVulnsCISA).

| summarize
     TotalVulnerableDevices = count(),
     DeviceList = make_set(DeviceName),
     Description = make_set(shortDescription)
     by cveID

We use summarize to aggregate our join results:

  • TotalVulnerableDevices: Count of vulnerable devices for each CVE.
  • DeviceList: A set (unique list) of affected device names.
  • Description: A set of short vulnerability descriptions.
| sort by TotalVulnerableDevices desc

Finally, we use sort by to sort the summarized results by the total number of vulnerable devices. The vulnerability with the highest number of affected devices will appear first.

Reference