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 == "AzureContainerRegistryVulnerability"
| extend
    //source = tostring(properties["resourceDetails"]["source"]), // == "Azure"
    timeGenerated = todatetime(properties["timeGenerated"]),
    status = tostring(properties["status"]["code"]),
    statusCause = tostring(properties["status"]["cause"]),
    statusDescription = tostring(properties["status"]["description"]),
    assessmentId = tostring(properties["id"]),
    assessmentDisplayName = tostring(properties["displayName"]),
    assessmentSeverity = tostring(properties["status"]["severity"]),
    assessmentDescription = tostring(properties["description"]),
    //assessmentCvssv3 = tostring(properties["additionalData"]["cvssV30Score"]), // == cvssv3
    assessmentRemediation = tostring(properties["remediation"]),
    cveId = tostring(properties["additionalData"]["vulnerabilityDetails"]["cveId"]),
    vulnerabilitySeverity = tostring(properties["additionalData"]["vulnerabilityDetails"]["severity"]),
    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"]),
    hasPublicExploit = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["exploitStepsPublished"]),
    isExploitVerified = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["exploitStepsVerified"]),
    isExploitInKit = toboolean(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["isInExploitKit"]),
    exploitTypes = strcat_array(properties["additionalData"]["vulnerabilityDetails"]["exploitabilityAssessment"]["types"], ", "),
    cpePart = tostring(properties["additionalData"]["vulnerabilityDetails"]["cpe"]["part"]),
    //artifactType = tostring(properties["additionalData"]["artifactDetails"]["artifactType"]), // == "ContainerImage"
    mediaType = tostring(properties["additionalData"]["artifactDetails"]["mediaType"]),
    registryHost = tostring(properties["additionalData"]["artifactDetails"]["registryHost"]),
    repositoryName = tostring(properties["additionalData"]["artifactDetails"]["repositoryName"]),
    imageDigest = tostring(properties["additionalData"]["artifactDetails"]["digest"]),
    imageTimePushed = todatetime(properties["additionalData"]["artifactDetails"]["lastPushedToRegistryUTC"]),
    imageOsPlatform = tostring(properties["additionalData"]["softwareDetails"]["osDetails"]["osPlatform"]),
    imageOsVersion = tostring(properties["additionalData"]["softwareDetails"]["osDetails"]["osVersion"]),
    softwareCategory = tostring(properties["additionalData"]["softwareDetails"]["category"]),
    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]),
    resourceId = tostring(properties["resourceDetails"]["id"])
| extend hasExploit = hasPublicExploit or isExploitVerified or isExploitInKit or isnotempty(exploitTypes)
| project resourceId, tenantId, subscriptionId, location, resourceGroup, assessedResourceType, timeGenerated, status, statusCause, statusDescription, assessmentId, assessmentDisplayName, assessmentSeverity, assessmentDescription, assessmentRemediation, cveId, vulnerabilitySeverity, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, hasExploit, hasPublicExploit, isExploitVerified, isExploitInKit, exploitTypes, cpePart, mediaType, registryHost, repositoryName, imageDigest, imageTimePushed, imageOsPlatform, imageOsVersion, softwareCategory, softwareLanguage, softwareVendor, softwareName, softwareVersion, softwareFixVersion, softwareEvidence
| summarize
    SoftwareVersions = make_list(pack(
        "AssessmentRemediation", assessmentRemediation,
        "SoftwareVersion", softwareVersion,
        "SoftwareFixVersion", softwareFixVersion
    )),
    arg_max(timeGenerated, location, resourceGroup, assessedResourceType, status, statusCause, statusDescription, assessmentDisplayName, assessmentSeverity, assessmentDescription, cveId, vulnerabilitySeverity, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, hasExploit, hasPublicExploit, isExploitVerified, isExploitInKit, exploitTypes, cpePart, mediaType, repositoryName, imageDigest, imageTimePushed, imageOsPlatform, imageOsVersion, softwareCategory, softwareLanguage, softwareVendor, softwareEvidence)
    by tenantId, subscriptionId, registryHost, resourceId, assessmentId, softwareName
| summarize
    VulnerableSoftware = make_list(pack(
        "SoftwareName", softwareName,
        "SoftwareVersions", SoftwareVersions,
        "SoftwareVendor", softwareVendor,
        "SoftwareCategory", softwareCategory,
        "SoftwareLanguage", softwareLanguage,
        "SoftwareEvidence", softwareEvidence
    )),
    arg_max(timeGenerated, location, resourceGroup, assessedResourceType, status, statusCause, statusDescription, assessmentDisplayName, assessmentSeverity, assessmentDescription, cveId, vulnerabilitySeverity, cvssv2, cvssv2Vector, cvssv3, cvssv3Vector, hasExploit, hasPublicExploit, isExploitVerified, isExploitInKit, exploitTypes, cpePart, mediaType, repositoryName, imageDigest, imageTimePushed, imageOsPlatform, imageOsVersion)
    by tenantId, subscriptionId, registryHost, resourceId, assessmentId
| summarize
    Vulnerabilities = make_list(pack(
        "CVE", cveId,
        "Severity", vulnerabilitySeverity,
        "assessmentDescription", assessmentDescription,
        "CVSSv2", cvssv2,
        "CVSSv3", cvssv3,
        "HasExploit", hasExploit,
        "VulnerableSoftware", VulnerableSoftware
    )),
    ImageOsPlatform = make_set(imageOsPlatform),
    ImageOsVersion = make_set(imageOsVersion),
    HasExploitableVulnerability = binary_all_or(tolong(hasExploit)),
    arg_max(timeGenerated, location, resourceGroup, assessedResourceType, status, statusCause, statusDescription, mediaType, repositoryName, imageDigest, imageTimePushed, imageOsPlatform, imageOsVersion)
    by tenantId, subscriptionId, registryHost, resourceId
| extend Vulnerabilities = iff(status == "Unhealthy", Vulnerabilities, dynamic(null))
| join kind=leftouter (
    ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project subscriptionId, subscriptionName = name
    ) on subscriptionId
| project
    resourceId,
    tenantId,
    subscriptionId,
    subscriptionName,
    location,
    resourceGroup,
    assessedResourceType,
    status,
    statusCause,
    statusDescription,
    mediaType,
    registryHost,
    repositoryName,
    imageDigest,
    imageTimePushed,
    ImageOsPlatform,
    ImageOsVersion,
    HasExploitableVulnerability//,
    //Vulnerabilities