KQL get all CVE's in an array

Question

I'm running the following KQL query in Azure Graph Explorer

securityresources
| where type == "microsoft.security/assessments/subassessments"
| extend assessmentKey = extract(".*assessments/(.+?)/.*",1,  id)
| where assessmentKey == "dbd0cb49-b563-45e7-9724-889e799fa648"

This returns my raws with [Results][1]

If I click on See details I can see that a given vulnerability has 2 CVE's assigned (CVE-2020-25709 and CVE-2020-25710)

{
    "description": "Debian has released security update for openldap to fix the vulnerabilities.<P>",
    "displayName": "Debian Security Update for openldap (DLA 2481-1)",
    "resourceDetails": {
        "id": "/repositories/foo/images/sha256:fb47732ef36b285b1f3fbda69ab8411a430b1dc43823ae33d5992f0295c945f4",
        "source": "Azure"
    },
    "additionalData": {
        "assessedResourceType": "ContainerRegistryVulnerability",
        "vendorReferences": [
            {
                "title": "DLA 2481-1",
                "link": "https://lists.debian.org/debian-lts-announce/2020/12/msg00008.html"
            }
        ],
        "publishedTime": "2020-12-09T13:44:37.0000000Z",
        "repositoryName": "foo",
        "metadata": {
            "isPreview": false
        },
        "registryHost": "acrtestdev2.azurecr.io",
        "patchable": true,
        "imageDigest": "sha256:fb47732ef36b285b1f3fbda69ab8411a430b1dc43823ae33d5992f0295c945f4",
        "cicdData": {
            "status": "Incomplete"
        },
        "scanner": "Trivy",
        "type": "Vulnerability",
        "cvss": {
            "2.0": {
                "cvssVectorString": "CVSS:2.0/AV:N/AC:L/Au:N/C:N/I:N/A:P/E:U/RL:OF/RC:C",
                "base": 5
            },
            "3.0": {
                "cvssVectorString": "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:H/E:U/RL:O/RC:C",
                "base": 7.5
            }
        },
        "cve": [
            {
                "title": "CVE-2020-25709",
                "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2020-25709"
            },
            {
                "title": "CVE-2020-25710",
                "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2020-25710"
            }
        ],
        "imageDetails": {
            "osDetails": "Debian Linux 9.3",
            "os": "Linux"
        }
    },
    "timeGenerated": "2022-08-11T08:58:48.5588955Z",
    "status": {
        "severity": "Medium",
        "code": "Unhealthy"
    },
    "remediation": "Refer to Debian LTS Announce <A HREF=\"https://lists.debian.org/debian-lts-announce/2020/12/msg00008.html\" TARGET=\"_blank\">DLA 2481-1</A> to address this issue and obtain further details.\n<P>Patch:<BR>\nFollowing are links for downloading patches to fix the vulnerabilities:\n<P> <A HREF=\"https://lists.debian.org/debian-lts-announce/2020/12/msg00008.html\" TARGET=\"_blank\">DLA 2481-1:Debian</A>",
    "id": "178251",
    "category": "Debian",
    "impact": "Successful exploitation allows attacker to compromise the system."
}

How could I access that two values in the CVE array/list and output them in a single column, say CVE?

Thanks a lot for help on this ! [1]: https://i.sstatic.net/n6PH2.png

Answer 1

I used a part from the query above and used it to build the following:

securityresources
| where type == "microsoft.security/assessments/subassessments"
| parse id with * "assessments/" assessmentKey "/" *
| where assessmentKey == "dbd0cb49-b563-45e7-9724-889e799fa648"
| mv-expand with_itemindex=i cve = properties.additionalData.cve
| extend cve["title"], cve["link"]
| extend description = properties.description,
         displayName = properties.displayName,
         resourceId = properties.resourceDetails.id,
         resourceSource = properties.resourceDetails.source,
         category = properties.category,
         severity = properties.status.severity,
         code = properties.status.code,
         timeGenerated = properties.timeGenerated,
         remediation = properties.remediation,
         impact = properties.impact,
         vulnId = properties.id,
         additionalData = properties.additionalData,
         digest = properties.additionalData.imageDigest,
         repositoyName = properties.additionalData.repositoryName     
| project timeGenerated,repositoyName,severity,vulnId,cve_title, cve_link, description, displayName, category, remediation, impact      

Answer 2

1. You can use the simpler syntax of the parse operator instead of extract().
2. Use the mv-expand operator to explode the properties.additionalData.cve array.
3. title seem to be a special word, so use cve["title"] (instead of cve.title, which results in syntax error).

---

securityresources
| where type == "microsoft.security/assessments/subassessments"
| parse id with * "assessments/" assessmentKey "/" *
| where assessmentKey == "dbd0cb49-b563-45e7-9724-889e799fa648"
| mv-expand with_itemindex=i cve = properties.additionalData.cve
| extend cve["title"], cve["link"]