KQL-MTP-TVM-Worklist

// Scoring for the CVEs
let Critical = int(40);
let High = int(10);
let Medium = int(3);
let Low = int(1);
let Informational = int(0);
// Determine OS Version based on MDATP ClientVersion
 let OSInformation = (
DeviceInfo
| extend OperatingsystemType = case(ClientVersion hasprefix "10.3720.16299.2", "Windows Server",
"WindowsClient")
| project DeviceId, DeviceName, ClientVersion, OSArchitecture, OSPlatform, OSBuild, OperatingsystemType
);
// 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,
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
| join kind=leftouter OSInformation on DeviceId
| sort by TotalRiskScore desc

This should also be projected | project AffectedSoftware, VulnerabilityDescription