Looping through JSON array and generating a new row / ForEach loop

Question

I'm trying to extract some info from the Defender for Cloud Qualys scanner through Azure Data Explorer.

What I want to do is for each row, I want to do a foreach for each CVE and then display each one with the server name and QID e.g.

"cve": [
    {
        "title": "CVE-2022-21123",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21123"
    },
    {
        "title": "CVE-2022-21125",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21125"
    },
    {
        "title": "CVE-2022-21127",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21127"
    },
    {
        "title": "CVE-2022-21166",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21166"
    }
]

The table should look something like this:

virtualMachineName,cvetitle,QID
computer1,CVE-2022-21123,48585
computer1,CVE-2022-21125,48585
computer1,CVE-2022-21127,48585
computer1,CVE-2022-21166,48585
computer2,CVE-2022-21125,48585
computer2,CVE-2010-38244,39459
computer3,CVE-2009-83492,39459

On line 8, it grabs the first CVE item but how do I grab the rest and then for each one create another row?

securityresources
| where type =~ "microsoft.security/assessments/subassessments"
| extend QID=tostring(properties.id)
| where properties.additionalData.source =~ "Built-in Qualys vulnerability assessment"
| extend vulnerabilityName=tostring(properties.displayName),
            vulnerabilityType = tostring(properties.additionalData.assessedResourceType),
            virtualMachineName=split(properties.resourceDetails.id, "/")[-1],
            allCves = properties.cve
| extend cvetitle = properties['additionalData']['cve'][0]['title']
| project QID, vulnerabilityName, vulnerabilityType, virtualMachineName, cvetitle, allCves

user_7861807 · Answer

you can use the mv-apply operator.

for example:

datatable(virtualMachineName:string, QID:long, payload:dynamic)[
    "computer1", 48585, dynamic({"cve": [
    {
        "title": "CVE-2022-21123",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21123"
    },
    {
        "title": "CVE-2022-21125",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21125"
    },
    {
        "title": "CVE-2022-21127",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21127"
    },
    {
        "title": "CVE-2022-21166",
        "link": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2022-21166"
    }
]})
]
| mv-apply cve = payload.cve on ( project cvetitle = tostring(cve['title']))
| project-away payload

virtualMachineName	QID	cvetitle
computer1	48585	CVE-2022-21123
computer1	48585	CVE-2022-21125
computer1	48585	CVE-2022-21127
computer1	48585	CVE-2022-21166

Looping through JSON array and generating a new row / ForEach loop

1 Answers