Problem with Kusto Query with nested JSON parameters Sentinel Log Analytics-CodePudding

I'm trying to extract some information from a nested JSON in log analytics. It is nested several levels deep though. And I come unstuck at the 3rd tier.

The scenario is to query on what user ID has had permissions removed in Azure. the ifnormation is all there is raw format, but I want to extract it to be more readable.

The data layout is :

AzureActivity
             Properties_d
                         responseBody
                                     properties
                                               principalId

It's the principalID I want (getting a UPN from AAD comes later ;)

My query works to a point. But the _propertieslevel3 comes up blank (no error). _resonsebody is fine. It is a dynamic JSON that contains the responsebody field from Properties_d.

AzureActivity
| where  (OperationNameValue contains "ROLEASSIGNMENTS/DELETE" and ActivityStatusValue contains "SUCCESS")
| extend _responsebody = parse_json(Properties_d.responseBody)
| extend _propertieslevel3 = parse_json(_responsebody.properties)
| extend ModifiedUser = parse_json(_propertieslevel3.principalId)

as _propertieslevel3 comes back blank, so does modified user. I can only guess that there is a problem trying to nest this deep.

Any ideas?

TIA.

data sample of Properties_d

{"eventCategory":"Administrative",
"eventDataId":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"eventSubmissionTimestamp":"2022-03-09T16:53:26.4493278Z",
"resource":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"resourceProviderValue":"MICROSOFT.AUTHORIZATION",
"subscriptionId":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"activityStatusValue":"Success",
"entity":"/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxx/providers/Microsoft.Authorization/roleAssignments/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"message":"Microsoft.Authorization/roleAssignments/delete",
"hierarchy":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"caller":"xxxxxx@xxxxxxx",
"httpRequest":"{\"clientIpAddress\":\"3.3.3.3\"}",
"statusCode":"OK",
"serviceRequestId":"",
"activitySubstatusValue":"OK",
"responseBody":"{\"properties\":{\"roleDefinitionId\":\"/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxx/providers/Microsoft.Authorization/roleDefinitions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\",
\"principalId\":\"xxxxxxxxxxxxxxxxxxxxxxxxxxxx\",
\"principalType\":\"User\",
\"scope\":\"/subscriptions/xxxxxxxxxxxxxxxxxxxxxx\",
\"condition\":null,
\"conditionVersion\":null,
\"createdOn\":\"2022-03-09T11:28:48.4781104Z\",
\"updatedOn\":\"2022-03-09T11:28:48.4781104Z\",
\"createdBy\":\"xxxxxxxxxxxxxxxxxxxxxxxxx\",
\"updatedBy\":\"xxxxxxxxxxxxxxxxxxxxxxx\",
\"delegatedManagedIdentityResourceId\":null,
\"description\":null},
\"id\":\"/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/providers/Microsoft.Authorization/roleAssignments/xxxxxxxxxxxxxxxxxxxxxx\",
\"type\":\"Microsoft.Authorization/roleAssignments\",
\"name\":\"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\"}"}

CodePudding user response：

Most likely, you need to apply parse_json() on the nested property bag too.

See:

CodePudding user response：

got it :)

Not sure why I needed to make _propertieslevel3 be just be the same as response body, rather than being able to extract .properties

but it works.

Thanks.

AzureActivity
| where (OperationNameValue contains "ROLEASSIGNMENTS/WRITE" and ActivityStatusValue contains "Start")
| extend _responsebody = parse_json(Properties_d.responseBody)
| extend _propertieslevel3 = parse_json(tostring(parse_json(_responsebody)))
| extend _level4 = parse_json(tostring(parse_json(_propertieslevel3.properties)))
| extend ModifiedUser = parse_json(tostring(parse_json(_level4.principalId)))