Pulling new fields out of nested JSON data

[joemiller]

Looking at the example field below (part of a JSON event), I'm trying to figure out how at search time to pair up the corresponding values of properties.appliedConditionalAccessPolicies{}.displayName fields and properties.appliedConditionalAccessPolicies{}.result fields into new field/value pairs for each event (note that there can be multiple pairs per event - two in this example). So for example, in the event below, I would want to add two new field/value pairs to the event:

Require_Duo_MFA=success
Scammer_Blocked_IP_Addresses=notApplied

Any ideas how to approach that?

appliedConditionalAccessPolicies: [ [-]
       { [-]
         conditionsNotSatisfied: 0
         conditionsSatisfied: 3
         displayName: Require Duo MFA
         enforcedGrantControls: [ [+]
         ]
         enforcedSessionControls: [ [+]
         ]
         id: 11111111-1111-1111-1111-111111111111
         result: success
       }
       { [-]
         conditionsNotSatisfied: 8
         conditionsSatisfied: 3
         displayName: Scammer Blocked IP Addresses
         enforcedGrantControls: [ [+]
         ]
         enforcedSessionControls: [ [+]
         ]
         id: 22222222-2222-2222-2222-222222222222
         result: notApplied
       }
     ]

[ITWhisperer · Accepted solution]

First, extract (spath) the array elements as properties.appliedConditionalAccessPolicies objects, then mvexpand that to give you separate events for each object. Then extract (spath again) the two fields you want. Then replace the spaces in displayName with underscores. The create a new field based on the contents of displayName. Something along these lines.

| spath path=properties.appliedConditionalAccessPolicies output=appliedConditionalAccessPolicies 
| mvexpand appliedConditionalAccessPolicies 
| spath input=appliedConditionalAccessPolicies path=displayName output=displayName
| spath input=appliedConditionalAccessPolicies path=result output=result
| eval displayName=replace(displayName," ","_")
| eval {displayName}=result

[joemiller]

Oh actually, should there be a command towards the end to "undo" the mvexpand? It looks like I still have separate events for each object..

[ITWhisperer]

If you want to collect all the event together at the end, do something like this: before the mvexpand tag each event with a row number

| streamstats count as row

 Then at the end, gather them back

| stats values(*) as * by row

[joemiller]

Great, thanks again!

[joemiller]

Thank you very much! This is exactly what I was looking for. Worked perfectly. I did have to add a pair of curly brackets to the end of the path parameter in your first line, e.g.:

| spath path=properties.appliedConditionalAccessPolicies{} output=appliedConditionalAccessPolicies 

 Anyways, I really appreciate the quick answer and the detailed explanation! Marking this as the answer.