KQL Search

// Use here: https://portal.azure.com/#view/HubsExtension/ArgQueryBlade
SecurityResources
| where type =~ "microsoft.security/assessments/subassessments" and id has "providers/Microsoft.Compute/virtualMachines"
| extend assessedResourceType = tostring(properties["additionalData"]["assessedResourceType"])
| where assessedResourceType == "ServerVulnerabilityTvm" // thus source == "Microsoft Defender vulnerability management"
| extend
    source = tostring(properties["additionalData"]["source"]), // scanner
    timeGenerated = todatetime(properties["timeGenerated"]),
    status = tostring(properties["status"]["code"]),
    category = tostring(properties["category"]),
    assessmentDisplayName = tostring(properties["displayName"]),
    assessmentDescription = tostring(properties["description"]),
    assessmentCvssv3 = tostring(properties["additionalData"]["cvssV30Score"]),
    assessmentSeverity = tostring(properties["status"]["severity"]),
    softwareVendor = tostring(properties["additionalData"]["softwareVendor"]),
    softwareName = tostring(properties["additionalData"]["softwareName"]),
    softwareVersion = tostring(properties["additionalData"]["softwareVersion"]),
    recommendedVendor = tostring(properties["additionalData"]["recommendedVendor"]),
    recommendedProgram = tostring(properties["additionalData"]["recommendedProgram"]),
    recommendedVersion = tostring(properties["additionalData"]["recommendedVersion"]),
    resourceId = tostring(properties["resourceDetails"]["id"]),
    assessmentId = tostring(properties["id"]),
    cve = properties["additionalData"]["cve"]
| mv-expand cve = iff(array_length(cve) == 0, dynamic([""]), cve)
| extend
    cve,
    description = tostring(cve["description"]),
    severity = tostring(cve["severity"]),
    cvss = tostring(cve["cvssScore"]),
    cvssVersion = toint(cve["cvssVersion"]),
    hasPublicExploit = toboolean(cve["hasPublicExploit"]),
    isExploitVerified = toboolean(cve["isExploitVerified"]),
    isExploitInKit = toboolean(cve["isExploitInKit"]),
    exploitabilityLevel = tostring(cve["exploitabilityLevel"]),
    exploitTypes = strcat_array(cve["exploitTypes"], ", "),
    isZeroDay = toboolean(cve["isZeroDay"])
| extend
    cve = tostring(cve["title"]),
    cvssv2 = iff(cvssVersion == 2, cvss, ""),
    cvssv3 = iff(cvssVersion == 3, cvss, ""),
    hasExploit = exploitabilityLevel != "NoExploit"
| sort by isExploitInKit desc, isExploitVerified desc, hasPublicExploit desc
| project timeGenerated, resourceId, tenantId, subscriptionId, location, resourceGroup, assessedResourceType, source, status, category, assessmentDisplayName, assessmentDescription, assessmentCvssv3, assessmentSeverity, softwareVendor, softwareName, softwareVersion, recommendedVendor, recommendedProgram, recommendedVersion, assessmentId, cve, description, severity, cvssv2, cvssv3, hasPublicExploit, isExploitVerified, isExploitInKit, exploitabilityLevel, exploitTypes, isZeroDay, hasExploit
| summarize
    Scanners = make_set(source),
    Vulnerabilities = make_list(pack(
        "CVE", cve,
        "Description", description,
        "Severity", severity,
        "CVSSv2", cvssv2,
        "CVSSv3", cvssv3,
        "HasExploit", hasExploit,
        // "HasPublicExploit", hasPublicExploit,
        // "IsExploitVerified", isExploitVerified,
        // "IsExploitInKit", isExploitInKit,
        // "ExploitTypes", exploitTypes,
        // "IsZeroDay", isZeroDay
        "AssessmentDisplayName", assessmentDisplayName,
        "AssessmentId", assessmentId
        )),
    HasExploitableVulnerability = binary_all_or(tolong(hasExploit)),
    arg_max(timeGenerated, subscriptionId, location, resourceGroup, assessedResourceType, assessmentDisplayName, assessmentDescription, assessmentSeverity, assessmentCvssv3)
    by category, status, tenantId, resourceId, assessmentId
| summarize
    Scanners = make_set(Scanners),
    Vulnerabilities = make_list(Vulnerabilities),
    AssessmentsDisplayNames = strcat_array(make_list(assessmentDisplayName), ", "),
    AssessmentsCVSSv3 = max(toreal(assessmentCvssv3)),
    HasExploitableVulnerability = binary_all_or(tolong(HasExploitableVulnerability)),
    arg_max(timeGenerated, subscriptionId, location, resourceGroup, assessedResourceType)
    by category, status, tenantId, resourceId
| join kind=leftouter (
    ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project subscriptionId, subscriptionName = name
    ) on subscriptionId
| sort by tenantId asc, subscriptionName asc, status asc, resourceId asc
| project
    resourceId,
    tenantId,
    subscriptionId,
    subscriptionName,
    location,
    resourceGroup,
    assessedResourceType,
    Scanners,
    status,
    category,
    AssessmentsDisplayNames,
    AssessmentsCVSSv3,
    HasExploitableVulnerability,
    Vulnerabilities