CODEWITHSUNDEEP
azure-data-explorerkql
Aziz
Aziz

Reputation: 293

Escaping period character when enumerating JSON object

I'm trying to extract some information using Azure Data Explorer from the Qualys vulnerability scanner logs.

I have got the below KQL, and it all works fine except for line 9. I'm trying to extract information from the returned json object, but the key name has a period in it i.e. 2.0 and I can't work out how to escape it so that I can still use the JSON dot notation.

securityresources | where type =~ "microsoft.security/assessments/subassessments"
        | extend assessmentKey=extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id), QID=tostring(properties.id), parentResourceId= extract("(.+)/providers/Microsoft.Security", 1, id)
        | extend resourceId = tostring(properties.resourceDetails.id)
        | where properties.additionalData.assessedResourceType =~ "ServerVulnerability" or properties.additionalData.assessedResourceType =~ "GeneralVulnerability"
        | 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],
                cvss2Score = tostring(properties.additionalData.cvss.\(2.0).base)
        | project QID, vulnerabilityName, virtualMachineName, vulnerabilityType, cvss2Score

JSON structure:

{
    "2.0": {
        "base": 7.6
    },
    "3.0": {
        "base": 7.5
    }
}

Upvotes: 1

Views: 548

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

please see: Dynamic object accessors.

for example:

print properties = dynamic({
    "additionalData":{
        "cvss":{
            "2.0": {
                "base": 7.6
            },
            "3.0": {
                "base": 7.5
            }
        }
    }
})
| project base = todouble(properties.additionalData.cvss['2.0'].base)
base
7.6

Upvotes: 2

Related Questions