KQL Search

Query Details

HomeAI ToolsDevice Query

Total Device Risk Score

Query

// Scoring for the CVEs
let Critical = int(40);
let High = int(10);
let Medium = int(3);
let Low = int(1);
let Informational = int(0);
// Get All the CVEs
let AllCVE = (DeviceTvmSoftwareVulnerabilities
| project DeviceId, DeviceName, VulnerabilitySeverityLevel, CveId, SoftwareVendor
| extend RiskScore = case(VulnerabilitySeverityLevel == "Critical", Critical,
VulnerabilitySeverityLevel == "High", High,
VulnerabilitySeverityLevel == "Medium", Medium,
VulnerabilitySeverityLevel == "Low", Low,
Informational)
);
// Get all CVE information
let CVEScore = (DeviceTvmSoftwareVulnerabilitiesKB
);
AllCVE | join kind=leftouter CVEScore on CveId
// Create the column Criticality to count all critical and high CVEs with an available exploit
| extend Criticality = case(IsExploitAvailable == "1" and VulnerabilitySeverityLevel == "Critical", "Critical"
,IsExploitAvailable == "1" and VulnerabilitySeverityLevel == "High", "High"
,"Lower")
| summarize TotalRiskScore = sum(RiskScore), TotalCVE = count(CveId), AverageScore = avg(RiskScore), Vendors = makeset(SoftwareVendor), Exploitable = countif(IsExploitAvailable==1), CriticalCVE = countif(Criticality == "Critical" or Criticality == "High") ,CVSSNone = countif(isempty(CvssScore)), CVSSLow = countif(CvssScore between (0.1 .. 3.9)), CVSSMedium = countif(CvssScore between (4.0 .. 6.9)), CVSSHigh = countif(CvssScore between (7.0 .. 8.9)), CVSSCritical = countif(CvssScore between (9 .. 10)) by DeviceName, DeviceId
| sort by TotalRiskScore desc

Explanation

This KQL query is designed to analyze and score software vulnerabilities (CVEs) on devices based on their severity and exploitability. Here's a simplified breakdown of what the query does:

  1. Define Severity Scores: It assigns numerical scores to different severity levels of vulnerabilities:

    • Critical: 40
    • High: 10
    • Medium: 3
    • Low: 1
    • Informational: 0

  2. Extract Vulnerability Data: It retrieves data about vulnerabilities from the DeviceTvmSoftwareVulnerabilities table, including device information, severity level, CVE ID, and software vendor. It also calculates a risk score for each vulnerability based on its severity.

  3. Join Additional CVE Information: It joins the extracted vulnerability data with additional information from the DeviceTvmSoftwareVulnerabilitiesKB table using the CVE ID.

  4. Determine Criticality: It creates a new column called "Criticality" to identify vulnerabilities that are both critical or high severity and have an available exploit.

  5. Summarize Data: It summarizes the data for each device, calculating:

    • Total risk score by summing up the risk scores of all vulnerabilities.
    • Total number of CVEs.
    • Average risk score.
    • List of software vendors associated with the vulnerabilities.
    • Count of exploitable vulnerabilities.
    • Count of critical and high vulnerabilities with available exploits.
    • Count of vulnerabilities in different CVSS score ranges (None, Low, Medium, High, Critical).

  6. Sort Results: Finally, it sorts the summarized data by total risk score in descending order, highlighting devices with the highest risk.

Overall, this query helps prioritize devices based on the severity and exploitability of their vulnerabilities, aiding in risk management and mitigation efforts.

Details

Jay Kerai profile picture

Jay Kerai

Released: November 11, 2024

Tables

DeviceTvmSoftwareVulnerabilitiesDeviceTvmSoftwareVulnerabilitiesKB

Keywords

DeviceTvmSoftwareVulnerabilitiesCveIdSoftwareVendorVulnerabilitySeverityLevelCvssScoreDeviceNameDeviceId

Operators

letintprojectextendcasejoinonkindsummarizesumcountavgmakesetcountifisemptybetweenbysortdesc

Actions