Splunk Search

Pulling new fields out of nested JSON data

joemiller
Explorer

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
       }
     ]

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend

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

 

View solution in original post

ITWhisperer
Legend

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

 

View solution in original post

joemiller
Explorer

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

0 Karma

ITWhisperer
Legend

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
Explorer

Great, thanks again!

0 Karma

joemiller
Explorer

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.

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!