Microsoft Defender for Endpoint collects the threat and vulnerability data from all the enrolled systems. This process is triggered every two hours based on network connectivity.
When you first look at all the list, it can be quite intimidating, so I was looking for a way to score and sort all the threats. This way, you can give the engineers and administrators a way to prioritize their work. In the following query, I have created six steps to make this possible.
- Create the scoring for the threat levels
- Get all the unpatched CVEs
- Create a list with information of all the CVEs
- Create a criticality column
- Create a scoring per system and sort descending
// 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 = (DeviceTvmSoftwareInventoryVulnerabilities
| project DeviceId, DeviceName, VulnerabilitySeverityLevel, CveId, SoftwareVendor
| extend RiskScore = case(VulnerabilitySeverityLevel == "Critical", Critical,
VulnerabilitySeverityLevel == "High", High,
VulnerabilitySeverityLevel == "Medium", Medium,
VulnerabilitySeverityLevel == "Low", Low,
// 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"
| 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