question

mtmaiman-7974 avatar image
0 Votes"
mtmaiman-7974 asked mtmaiman-7974 commented

Traverse hierarchical JSON object and perform iterative comparison

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.

azure-data-explorer
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @mtmaiman-7,

Welcome to Microsoft Q&A forum and thanks for posting your query.

To better assist on your ask, could you please help clarify what Azure Services are you using? I see that your ask is related to a Query language, could you please confirm what Query language you are referring to? A few additional details would help us to understand and assist better on this request.

We look forward to your response.

Thank you

0 Votes 0 ·
mtmaiman-7974 avatar image mtmaiman-7974 KranthiPakala-MSFT ·

Hi Kranthi,

My mistake, I had thought this forum was specific to the Azure Service I have been using. This is specifically surrounding Log Analytics and Data Explorer with the Kusto Query Language (KQL).

Thank you.

0 Votes 0 ·

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered mtmaiman-7974 commented

Hi @mtmaiman-7974,

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.







· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @mtmaiman-7974,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·
mtmaiman-7974 avatar image mtmaiman-7974 KranthiPakala-MSFT ·

Hi @KranthiPakala-MSFT,

Thanks for the reply. I have been away and unable to address this issue. However, now that I am back I have started working on implementing your suggestion. I am unfamiliar with this function/data type in KQL as I am fairly new to the language. I must be implementing your suggestion incorrectly, as here is what I have:

https://pastebin.com/fxJPD9M9 (code was too long)

This creates an error. See https://i.imgur.com/lYXXjHe.png (wouldn't let me attach file, so uploaded to Imgur)

0 Votes 0 ·

Hi @mtmaiman-7974 ,

Sorry for the delayed response. Thanks for attaching the code.
After looking it at multiple times, I was able to notice that there is an extra comma (,) at the last section of the code. Please see below image with highlighted in yellow.. Please remove that extra comma and you should see any syntax errors.

129051-image.png


Hope this helps.



  • Please accept an answer and up-vote if it helps. Original posters help the community find answers faster by identifying the correct answer. Here is [how][7].

  • Want a reminder to come back and check responses? Here is how to subscribe to a [notification][8].


0 Votes 0 ·
image.png (379.4 KiB)
Show more comments