Traverse hierarchical JSON object and perform iterative comparison

Question

I have a data table of user sign in logs (SigninLogs) which returns a hierarchical JSON object containing Conditional Access data. This object is represented as an indexed list of child objects which contain details on each CA policy, applied status, result, etc. I would like to write a query which allows you to input the name of the CA policy you wish to see details for, then have it return the SigninLogs table with a new Column (ex. "RequestedPolicy") which contains the child object from the original JSON object that only shows details on the specified policy. For instance, if I call the SigninLogs table and project the CA data, here is an example of a returned JSON object:

0

{"id":"acb2a230-0e04-4f2f-a3c3-a706375e445d","displayName":"xxxx","enforcedGrantControls":[],"enforcedSessionControls":["CloudAppSecurity"],"result":"notApplied","conditionsSatisfied":0,"conditionsNotSatisfied":1}

1

{"id":"a7aa4063-8322-4d42-9c1c-9d2dd222a200","displayName":"xxxx","enforcedGrantControls":["Mfa"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":1,"conditionsNotSatisfied":2}

2

{"id":"d827d982-67a5-4960-8588-c78711bb35a2","displayName":"xxxx","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":0,"conditionsNotSatisfied":1}

3

{"id":"1d322609-f981-43ed-b5d9-91480dcad009","displayName":"xxxx","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":3,"conditionsNotSatisfied":16}

4

{"id":"f6df2620-e6e8-4af3-830e-6e4fa6f39c76","displayName":"xxxx","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":1,"conditionsNotSatisfied":2}

5

{"id":"986b289f-b15a-401c-81b7-f57b457b7504","displayName":"yyyy","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":3,"conditionsNotSatisfied":8}

6

{"id":"adb912d3-8f2f-40db-969e-887f4f6a733b","displayName":"xxxx","enforcedGrantControls":[],"enforcedSessionControls":[],"result":"notEnabled","conditionsSatisfied":0,"conditionsNotSatisfied":0}

7

{"id":"73183ee5-f0e0-4515-83eb-e44f78137b32","displayName":"xxxx","enforcedGrantControls":["Mfa"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":3,"conditionsNotSatisfied":8}

8

{"id":"6cda6558-5f83-4cc0-9e31-1f9f309667ee","displayName":"xxxx","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"reportOnlyNotApplied","conditionsSatisfied":0,"conditionsNotSatisfied":1}

9

{"id":"54493aad-eda2-4888-831d-5e040574ddcb","displayName":"xxxx","enforcedGrantControls":[],"enforcedSessionControls":["CloudAppSecurity"],"result":"reportOnlyNotApplied","conditionsSatisfied":0,"conditionsNotSatisfied":1}

10

{"id":"ac0ce9f8-1857-43f0-b071-2ab4200b1279","displayName":"xxxx","enforcedGrantControls":["Mfa"],"enforcedSessionControls":[],"result":"reportOnlyNotApplied","conditionsSatisfied":0,"conditionsNotSatisfied":1}

The true policy displayNames have been omitted for security purposes. But, for example say I wish to run my desired query with input "yyyy", then I wish to traverse through this hierarchical JSON object of CA data on each returned item from the SigninLogs table, compare the displayName of each child object to the inputted display name, and then return the details of the matched policy only into a unioned column "RequestedPolicy". For example, I would only see this data in the "RequestedPolicy" column for this entry:

{"id":"986b289f-b15a-401c-81b7-f57b457b7504","displayName":"yyyy","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":3,"conditionsNotSatisfied":8}

I have tried various methods to accomplish this, but have not had any success thus far. Any help would be appreciated, or to know if this is even possible.

Answer

Hi @mtmaiman ,

Thanks much for clarifying the ask. Please take a look at dynamic data type in ADX: The dynamic data type

Sample of dynamic datatype query:

   datatable(row:long,policy:dynamic)  
   [  
   0,  
   dynamic({"id":"f6df2620-e6e8-4af3-830e-6e4fa6f39c76","displayName":"xxxx","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":1,"conditionsNotSatisfied":2}),  
   5,  
   dynamic({"id":"986b289f-b15a-401c-81b7-f57b457b7504","displayName":"yyyy","enforcedGrantControls":["Block"],"enforcedSessionControls":[],"result":"notApplied","conditionsSatisfied":3,"conditionsNotSatisfied":8}),  
   6,  
   dynamic({"id":"adb912d3-8f2f-40db-969e-887f4f6a733b","displayName":"xxxx","enforcedGrantControls":[],"enforcedSessionControls":[],"result":"notEnabled","conditionsSatisfied":0,"conditionsNotSatisfied":0}),  
   ]  
   | where policy.displayName == "yyyy"  
   | project RequestedPolicy=policy

Result:

   RequestedPolicy  
   { "id": "986b289f-b15a-401c-81b7-f57b457b7504", "displayName": "yyyy", "enforcedGrantControls": [ "Block" ], "enforcedSessionControls": [], "result": "notApplied", "conditionsSatisfied": 3, "conditionsNotSatisfied": 8 }

Hope this helps. Do let us know how it goes.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Share via

Traverse hierarchical JSON object and perform iterative comparison

1 answer