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
}
}