How can I put several extracted values from a Json in an array in Kusto?

user_17914737 0
Viewed 133

I'm trying to write a query that returns the vulnerabilities found by "Built-in Qualys vulnerability assessment" in log analytics.

It was all going smoothly I was getting the values from the properties Json and turning then into separated strings but I found out that some of the terms posses more than one value, and I need to get all of them in a single cell.

My query is like this right now

securityresources | where type =~ "microsoft.security/assessments/subassessments"
    | extend assessmentKey=extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id), IdAzure=tostring(properties.id)
    | extend IdRecurso = tostring(properties.resourceDetails.id)
    | extend NomeVulnerabilidade=tostring(properties.displayName),
        Correcao=tostring(properties.remediation),
        Categoria=tostring(properties.category),
        Impacto=tostring(properties.impact),
        Ameaca=tostring(properties.additionalData.threat),
        severidade=tostring(properties.status.severity),
        status=tostring(properties.status.code),
        Referencia=tostring(properties.additionalData.vendorReferences[0].link),
        CVE=tostring(properties.additionalData.cve[0].link)
    | where assessmentKey == "1195afff-c881-495e-9bc5-1486211ae03f"
    | where status == "Unhealthy"
    | project IdRecurso, IdAzure, NomeVulnerabilidade, severidade, Categoria, CVE, Referencia, status, Impacto, Ameaca, Correcao

Ignore the awkward names of the columns, for they are in Portuguese.

As you can see in the "Referencia" and "CVE" columns, I'm able to extract the values from a specific index of the array, but I want all links of the whole array

kql
1 Answers

Without sample input and expected output it's hard to understand what you need, so trying to guess here...

I think that summarize make_list(...) by ... will help you (see this to learn how to use make_list)

If this is not what you're looking for, please delete the question, and post a new one with minimal sample input (using datatable operator), and expected output, and we'll gladly help.

user_21450970
Related