KQL Search

// Use here: https://portal.azure.com/#view/HubsExtension/ArgQueryBlade
SecurityResources
| where type =~ "microsoft.security/assessments/subassessments" and id has "providers/Microsoft.ContainerRegistry/registries/"
| extend assessedResourceType = tostring(properties["additionalData"]["assessedResourceType"])
| where assessedResourceType == "ContainerRegistryVulnerability" // thus scanner in ("Qualys", "Trivy")
| extend
    registryHost = tostring(properties["additionalData"]["registryHost"]),
    repositoryName = tostring(properties["additionalData"]["repositoryName"]),
    imageDigest = tostring(properties["additionalData"]["imageDigest"]),
    imageOs = tostring(properties["additionalData"]["imageDetails"]["os"]),
    imageOsDetails = tostring(properties["additionalData"]["imageDetails"]["osDetails"]),
    scanner = tostring(properties["additionalData"]["scanner"]),
    timeGenerated = todatetime(properties["timeGenerated"]),
    status = tostring(properties["status"]["code"]),
    patchable = toboolean(properties["additionalData"]["patchable"]),
    assessmentType = tostring(properties["additionalData"]["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(properties["additionalData"]["cvss"]["2.0"]["base"]),
    cvssv3 = tostring(properties["additionalData"]["cvss"]["3.0"]["base"]),
    resourceId = tostring(properties["resourceDetails"]["id"]),
    assessmentId = toint(properties["id"]),
    cve = properties["additionalData"]["cve"]
| mv-expand cve = iff(array_length(cve) == 0, dynamic([""]), cve)
| extend cve = tostring(cve["title"])
// Some "cve" dynamic objects in "properties" contain repeated CVEs, we need to deduplicate
| summarize arg_max(timeGenerated, location, resourceGroup, repositoryName, imageDigest, imageOs, imageOsDetails, assessedResourceType, displayName, description, impact, remediation)
    by scanner, status, patchable, assessmentType, severity, category, tenantId, subscriptionId, registryHost, resourceId, assessmentId, cvssv2, cvssv3, cve
| summarize
    Scanners = make_set(scanner),
    Vulnerabilities = make_list(pack(
        "CVE", cve,
        "Category", category,
        "DisplayName", displayName,
        //"Description", description,
        //"Impact", impact,
        //"Remediation", remediation,
        "Severity", severity,
        "CVSSv2", cvssv2,
        "CVSSv3", cvssv3,
        "Patchable", patchable,
        "AssessmentId", assessmentId
        )),
    arg_max(timeGenerated, location, resourceGroup, repositoryName, imageDigest, imageOs, imageOsDetails, assessedResourceType)
    by status, assessmentType, tenantId, subscriptionId, registryHost, 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,
    registryHost,
    resourceGroup,
    repositoryName,
    imageDigest,
    imageOs,
    imageOsDetails,
    assessedResourceType,
    Scanners,
    status,
    assessmentType,
    Vulnerabilities