Problem with Kusto Query with nested JSON parameters Sentinel Log Analytics

Question

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

There is no problem with deep nesting. Without a data sample it is quite challenging to assist you. — David דודו Markovitz
– David דודו Markovitz, Commented Mar 10, 2022 at 17:41

Yoni L. · Accepted Answer · 2022-03-10 17:58:51Z

1

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

See: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/parsejsonfunction

answered Mar 10, 2022 at 17:58

Yoni L.

26.4k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lance · Accepted Answer · 2022-03-11 10:59:00Z

0

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

answered Mar 11, 2022 at 10:59

Lance

131 silver badge4 bronze badges

1 Comment

David דודו Markovitz Over a year ago

responseBody value is clearly a string (notice the backslashes preceding the qualifiers). The first parse_json treat it as a string. The second parse_json convert it to json.

Collectives™ on Stack Overflow

Problem with Kusto Query with nested JSON parameters Sentinel Log Analytics

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related