KQL Search

// Use here: https://portal.azure.com/#view/HubsExtension/ArgQueryBlade
SecurityResources
| where type =~ "microsoft.security/assessments/subassessments" and id has "c609cf0f-71ab-41e9-a3c6-9a1f7fe1b8d5" //and id has "providers/Microsoft.ContainerService/managedClusters"
// | extend assessedResourceType = tostring(properties["additionalData"]["assessedResourceType"])
// | where assessedResourceType == "AzureRuntimeImageVulnerability" // scanner == "MicrosoftDefenderVulnerabilityManagement"
| extend
    cluster = tostring(properties["additionalData"]["clusterDetails"]["clusterName"]),
    workloads = todynamic(tostring(properties["additionalData"]["kubernetesContext"]["workloads"])),
    registryHost = tostring(properties["additionalData"]["artifactDetails"]["registryHost"]),
    repositoryName = tostring(properties["additionalData"]["artifactDetails"]["repositoryName"]),
    imageDigest = tostring(properties["additionalData"]["artifactDetails"]["digest"]),
    imageTag = strcat_array(properties["additionalData"]["artifactDetails"]["tags"], " - "),
    imageOs = tostring(properties["additionalData"]["softwareDetails"]["osDetails"]["osPlatform"]),
    imageOsDetails = tostring(properties["additionalData"]["softwareDetails"]["osDetails"]["osVersion"]),
    //source = tostring(properties["additionalData"]["metadata"]["inventorySource"]),
    timeGenerated = todatetime(properties["timeGenerated"]),
    status = tostring(properties["status"]["code"]),
    statusCause = tostring(properties["status"]["cause"]),
    patchable = toboolean(properties["additionalData"]["softwareDetails"]["patchable"]),
    //assessmentType = tostring(properties["additionalData"]["data"]["Type"]),
    assessmentSeverity = tostring(properties["status"]["severity"]),
    category = tostring(properties["additionalData"]["softwareDetails"]["category"]),
    displayName = tostring(properties["displayName"]),
    description = tostring(properties["description"]),
    remediation = tostring(properties["remediation"]),
    cvssv2 = tostring(properties["additionalData"]["vulnerabilityDetails"]["cvss"]["2.0"]["base"]),
    cvssv2Vector = tostring(properties["additionalData"]["vulnerabilityDetails"]["cvss"]["2.0"]["cvssVectorString"]),
    cvssv3 = tostring(properties["additionalData"]["vulnerabilityDetails"]["cvss"]["3.0"]["base"]),
    cvssv3Vector = tostring(properties["additionalData"]["vulnerabilityDetails"]["cvss"]["3.0"]["cvssVectorString"]),
    resourceId = tostring(properties["resourceDetails"]["id"]),
    assessmentId = tostring(properties["id"]),
    cveId = tostring(properties["additionalData"]["vulnerabilityDetails"]["cveId"]),
    vulnerabilitySeverity = tostring(properties["additionalData"]["vulnerabilityDetails"]["severity"]),
    hasPublicExploit = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["isPubliclyDisclosed"]),
    isExploitVerified = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["isVerified"]),
    isExploitInKit = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["isInExploitKit"]),
    softwareLanguage = tostring(properties["additionalData"]["softwareDetails"]["language"]),
    softwareVendor = tostring(properties["additionalData"]["softwareDetails"]["vendor"]),
    softwareName = tostring(properties["additionalData"]["softwareDetails"]["packageName"]),
    softwareVersion = tostring(properties["additionalData"]["softwareDetails"]["version"]),
    //softwareFixStatus = tostring(properties["additionalData"]["softwareDetails"]["fixStatus"]), // == "FixAvailable"
    softwareFixVersion = tostring(properties["additionalData"]["softwareDetails"]["fixedVersion"]),
    softwareEvidence = tostring(properties["additionalData"]["softwareDetails"]["evidence"][0])
| extend hasExploit = hasPublicExploit or isExploitVerified or isExploitInKit
| mv-expand workloads = iff(array_length(workloads) == 0, dynamic([""]), workloads)
| mv-expand ownedResources = iff(array_length(workloads["ownedResources"]) == 0, dynamic([""]), workloads["ownedResources"])
| mv-expand containers = case(array_length(ownedResources["containers"]) > 0, ownedResources["containers"], array_length(workloads["containers"]) > 0, workloads["containers"], dynamic([""]))
| extend container = tostring(containers["name"])
| summarize
    SoftwareVersions = make_set(pack(
        "SoftwareVersion", softwareVersion,
        "SoftwareFixVersion", softwareFixVersion
    )),
    arg_max(timeGenerated, displayName, description, remediation, patchable, assessmentSeverity, cveId, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, hasExploit, registryHost, repositoryName, imageDigest, imageTag, imageOs, imageOsDetails, softwareVendor, category, softwareLanguage, softwareEvidence)
    by status, statusCause, tenantId, subscriptionId, resourceGroup, cluster, container, resourceId, imageTag, assessmentId, softwareName
| summarize
    VulnerableSoftware = make_set(pack(
        "SoftwareName", softwareName,
        "SoftwareVersions", SoftwareVersions,
        "SoftwareVendor", softwareVendor,
        "SoftwareCategory", category,
        "SoftwareLanguage", softwareLanguage,
        "SoftwareEvidence", softwareEvidence
    )),
    category = strcat_array(make_set(category), ", "),
    arg_max(timeGenerated, displayName, description, remediation, patchable, assessmentSeverity, cveId, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, hasExploit, registryHost, repositoryName, imageDigest, imageTag, imageOs, imageOsDetails)
    by status, statusCause, tenantId, subscriptionId, resourceGroup, cluster, container, resourceId, imageTag, assessmentId
| summarize
    Vulnerabilities = make_set_if(pack(
        "CVE", cveId,
        "Category", category,
        "DisplayName", displayName,
        "Description", description,
        //"Impact", impact,
        "Remediation", remediation,
        "Severity", assessmentSeverity,
        "CVSSv2", cvssv2,
        //"CVSSv2Vector", cvssv2Vector,
        "CVSSv3", cvssv3,
        //"CVSSv3Vector", cvssv3Vector,
        "Patchable", patchable,
        "AssessmentId", assessmentId,
        "VulnerableSoftware", VulnerableSoftware
    ), status == "Unhealthy"),
    container = make_set(container),
    HasExploitableVulnerability = binary_all_or(tolong(hasExploit)),
    arg_max(timeGenerated, registryHost, repositoryName, imageDigest, imageOs, imageOsDetails)
        //severity, category, description, impact, remediation, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, VulnerableSoftware)
    by status, statusCause, tenantId, subscriptionId, resourceGroup, cluster, resourceId, imageTag
        //, assessmentId, displayName, cveId
| join kind=leftouter (
    ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project subscriptionId, subscriptionName = name
    ) on subscriptionId
// The same image can be used in different clusters
| summarize
    Containers = make_list(pack(
        "subscriptionName", subscriptionName,
        "resourceGroup", resourceGroup,
        "cluster", cluster,
        "containers", container
        )),
    Vulnerabilities = make_set(Vulnerabilities),
    HasExploitableVulnerability = binary_all_or(HasExploitableVulnerability),
    arg_max(timeGenerated, registryHost, repositoryName, imageDigest, imageOs, imageOsDetails)
        //severity, category, description, impact, remediation, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, VulnerableSoftware)
    by status, statusCause, tenantId, resourceId, imageTag
        //, assessmentId, displayName, cveId
| sort by tenantId asc, status asc, resourceId asc
| project
    tenantId,
    resourceId,
    Containers,
    registryHost,
    repositoryName,
    imageDigest,
    imageTag,
    imageOs,
    imageOsDetails,
    status,
    statusCause,
    HasExploitableVulnerability,
    Vulnerabilities