I'm working on a query using my miserable skills with KQL. The problem I am having is extracting data from multiple nested JSONs.
As an example, When I check the output of this query:
securityresources | where type =~ "microsoft.security/assessments/subassessments"
| extend assessmentKey=extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id), subAssessmentId=tostring(properties.id), parentResourceId= extract("(.+)/providers/Microsoft.Security", 1, id)
| extend resourceId = tostring(properties.resourceDetails.id)
| extend subAssessmentName=tostring(properties.displayName),
subAssessmentDescription=tostring(properties.description),
subAssessmentRemediation=tostring(properties.remediation),
subAssessmentCategory=tostring(properties.category),
subAssessmentImpact=tostring(properties.impact),
severity=tostring(properties.status.severity),
status=tostring(properties.status.code),
cause=tostring(properties.status.cause),
statusDescription=tostring(properties.status.description),
additionalData=tostring(properties.additionalData)
| where assessmentKey == "xxx"
| where status == "Unhealthy"
| summarize numOfResources=dcount(resourceId), timeGenerated=arg_max(todatetime(properties.timeGenerated), additionalData) by assessmentKey, subAssessmentId, subAssessmentName, subAssessmentCategory, severity, status, cause, statusDescription, subAssessmentDescription, subAssessmentRemediation, subAssessmentImpact
| extend high = iff(severity == "High", 3,0), medium = iff(severity == "Medium", 2, 0), low = iff(severity == "Low", 1 ,0)
| extend all = high + medium + low
| extend additionalData = parse_json(additionalData)
| extend data = parse_json(additionalData.data)
| extend vendorReferences = parse_json(data.VendorReferences)
| extend imageDetails = parse_json(data.ImageDetails)
| extend containers = parse_json(data.Containers)
| extend imageId = parse_json(data.ImageId)
| extend cvss = parse_json(data.Cvss)
| extend cve = parse_json(data.Cve)
| project cvss
I will get the following result:
{
"2.0": {
"$type": "additionalData/cvss",
"cvssVectorString": "CVSS:2.0/AV:N/AC:L/Au:N/C:N/I:N/A:C/E:POC/RL:OF/RC:C",
"base": 7.8
},
"3.0": {
"$type": "additionalData/cvss",
"cvssVectorString": "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:H/E:P/RL:O/RC:C",
"base": 7.5
}
}
And from that I would like to project into two colums, which should look like something like this:
- CVSS 2.0 Base: 7.8
- CVSS 3.1 Base: 7.5
To make matters worse, an even more complex scenario would be to substract info from containers. If right now I would use
project containers
Then the end result would be something like this:
[
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-1",
"Namespace": "namespace-staging",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
},
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-2",
"Namespace": "namespace-staging",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
},
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-3",
"Namespace": "namespace-staging",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
},
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-4",
"Namespace": "namespace-prod",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
},
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-5",
"Namespace": "namespace-prod",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
},
{
"Name": "cont-name",
"Pod": {
"Name": "pod-name-6",
"Namespace": "namespace-prod",
"ControllerName": "controller-name",
"ControllerType": "ReplicaSet"
}
}
]
And from the above I need to extract the container name and, ideally, the namespace though I can image this might not be possible. So just the container nanme.
Unfortunately this is far beyond my skills and I have no clue where to even begin with. Any help would be greatly appreciated.
W.
I tried something like this:
| extend cvss20base = cvss["2.0"]["base"]
but the results are always null.
I could not come up with any other options
#########################################
Ok, I'll try to explain this in a bit more detail. Let's say my query looks (ends) like this, and let's make this the baseline to work on:
| extend additionalData = parse_json(additionalData)
| extend data = parse_json (additionalData.data)
| project data
If I run the query, this is the output:
If we get into more details, we get this:
73 rows are returned. And the Data contains all the info I need.
Based on that, I can project for:
- Type
- InventorySource
- PublishedTime etc. Basically, anywhere where I don't have yet another JSON nested.
But what I need to further extract are certain values from, let's say, Cvss, which is a JSON. I can further add something like this:
| extend cvss = parse_json (data.Cvss)
| project cvss
And as a result I get 73 results that look like this: cvss
Each of those rows, after prettifying, looks like that:
{
"2.0": {
"$type": "additionalData/cvss",
"cvssVectorString": "CVSS:2.0/AV:A/AC:M/Au:M/C:N/I:C/A:P/E:U/RL:OF/RC:C",
"base": 5.4
},
"3.0": {
"$type": "additionalData/cvss",
"cvssVectorString": "CVSS:3.1/AV:N/AC:L/PR:L/UI:R/S:U/C:L/I:N/A:N/E:U/RL:O/RC:C",
"base": 3.5
}
}
And finally from that I need to extract the cvss base value for 2.0 and 3.0.
I tried playing around with mv-expand, more JSON parsing like
| extend cvss20 = parse_json(cvss.[0])
| project cvss20
But whatever option I try I always get null results :(
I hope this is all clear now.
#########################################
Ok, finally getting somewhere!
| extend additionalData = parse_json(additionalData)
| extend data = parse_json (additionalData.data)
| extend cvss = parse_json (data.Cvss)
| extend cvss_ext = parse_json(tostring(parse_json(cvss)))
| extend x = cvss_ext.["2.0"]
| extend y = cvss_ext.["3.0"]
| extend cvss20 = x.base
| extend cvss30 = y.base
| project cvss20, cvss30
Wish me luck :D
You can use below
KQL queriesto get desired result:1st KQL query to get CVSS and Base:Output:Fiddle.
2nd KQL queryto get container name and namespace:Output:Fiddle.