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 == "ServerVulnerability" // thus source == "Built-in Qualys vulnerability assessment"
| extend
    source = tostring(properties["additionalData"]["source"]), // 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"]),
    threat = tostring(properties["additionalData"]["threat"]),
    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, subscriptionId, location, resourceGroup, assessedResourceType, displayName, description, impact, remediation, threat)
    by source, status, patchable, assessmentType, severity, category, tenantId, resourceId, assessmentId, cvssv2, cvssv3, cve
| summarize
    Scanners = make_set(source),
    Vulnerabilities = make_list(pack(
        "CVE", cve,
        "Category", category,
        "DisplayName", displayName,
        //"Description", description,
        //"Impact", impact,
        //"Remediation", remediation,
        //"Threat", threat,
        "Severity", severity,
        "CVSSv2", cvssv2,
        "CVSSv3", cvssv3,
        "Patchable", patchable,
        "AssessmentId", assessmentId)),
    arg_max(timeGenerated, subscriptionId, location, resourceGroup, assessedResourceType)
    by status, assessmentType, 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,
    assessmentType,
    Vulnerabilities