KQL Search

// Use here: https://portal.azure.com/#view/HubsExtension/ArgQueryBlade
SecurityResources
| where type =~ "microsoft.security/assessments/subassessments" and id has "41503391-efa5-47ee-9282-4eff6131462c" //and id has "providers/Microsoft.ContainerService/managedClusters"
// | extend assessedResourceType = tostring(properties["additionalData"]["assessedResourceType"])
// | where assessedResourceType == "GeneralVulnerability" // Qualys scanner
| extend
    cluster = extract(@"\/Microsoft\.ContainerService\/managedClusters\/([^\/]+)", 1, id),
    containers = todynamic(tostring(properties["additionalData"]["data"]["Containers"])),
    imageId = tostring(properties["additionalData"]["data"]["ImageId"])
| extend
    registryHost = extract(@"^([^\/]+)\/", 1, imageId),
    repositoryName = extract(@"^[^\/]+\/([^\@]+)\@", 1, imageId),
    imageDigest = extract(@"^[^\/]+\/[^\@]+\@(.+)$", 1, imageId),
    imageTag = tostring(properties["additionalData"]["data"]["Tag"]),
    imageOs = tostring(todynamic(tostring(properties["additionalData"]["data"]["ImageDetails"]))["OS"]),
    imageOsDetails = tostring(todynamic(tostring(properties["additionalData"]["data"]["ImageDetails"]))["OSDetails"]),
    //source = tostring(properties["additionalData"]["data"]["InventorySource"]),
    timeGenerated = todatetime(properties["timeGenerated"]),
    status = tostring(properties["status"]["code"]),
    statusCause = tostring(properties["status"]["cause"]),
    patchable = toboolean(properties["additionalData"]["data"]["Patchable"]),
    //assessmentType = tostring(properties["additionalData"]["data"]["Type"]),
    severity = tostring(properties["status"]["severity"]),
    category = tostring(properties["category"]),
    displayName = tostring(properties["displayName"]),
    description = tostring(properties["description"]),
    impact = tostring(properties["impact"]),
    remediation = tostring(properties["remediation"]),
    cvssv2 = tostring(todynamic(tostring(properties["additionalData"]["data"]["Cvss"]))["2.0"]["base"]),
    cvssv2Vector = tostring(todynamic(tostring(properties["additionalData"]["data"]["Cvss"]))["2.0"]["cvssVectorString"]),
    cvssv3 = tostring(todynamic(tostring(properties["additionalData"]["data"]["Cvss"]))["3.0"]["base"]),
    cvssv3Vector = tostring(todynamic(tostring(properties["additionalData"]["data"]["Cvss"]))["3.0"]["cvssVectorString"]),
    resourceId = tostring(properties["resourceDetails"]["id"]),
    assessmentId = toint(properties["id"]),
    cve = todynamic(tostring(properties["additionalData"]["data"]["Cve"]))
| mv-expand containers = iff(array_length(containers) == 0, dynamic([""]), containers)
| extend container = tostring(containers["Name"])
| mv-expand cve = iff(array_length(cve) == 0, dynamic([""]), cve)
| extend cveId = tostring(cve["Id"])
| summarize
    Vulnerabilities = make_set_if(pack(
        "CVE", cveId,
        "Category", category,
        "DisplayName", displayName,
        //"Description", description,
        //"Impact", impact,
        //"Remediation", remediation,
        "Severity", severity,
        "CVSSv2", cvssv2,
        //"CVSSv2Vector", cvssv2Vector,
        "CVSSv3", cvssv3,
        //"CVSSv3Vector", cvssv3Vector,
        "Patchable", patchable,
        "AssessmentId", assessmentId
    ), status == "Unhealthy"),
    container = make_set(container),
    arg_max(timeGenerated, registryHost, repositoryName, imageDigest, imageOs, imageOsDetails)
        //severity, category, description, impact, remediation, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector)
    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
        )),
    arg_max(timeGenerated, registryHost, repositoryName, imageDigest, imageOs, imageOsDetails,
        Vulnerabilities)
        //severity, category, description, impact, remediation, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector)
    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,
    Vulnerabilities