topic Re: Pulling new fields out of nested JSON data in Splunk Search

Pulling new fields out of nested JSON data

joemiller — Thu, 15 Apr 2021 19:35:21 GMT

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

Re: Pulling new fields out of nested JSON data

ITWhisperer — Thu, 15 Apr 2021 20:28:55 GMT

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

Re: Pulling new fields out of nested JSON data

joemiller — Thu, 15 Apr 2021 21:51:24 GMT

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.

Re: Pulling new fields out of nested JSON data

joemiller — Thu, 15 Apr 2021 22:32:18 GMT

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

Re: Pulling new fields out of nested JSON data

ITWhisperer — Thu, 15 Apr 2021 23:24:07 GMT

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

Re: Pulling new fields out of nested JSON data

joemiller — Thu, 15 Apr 2021 23:28:58 GMT

Great, thanks again!