topic Re: Extract multiple JSON fields in Splunk Search

Extract multiple JSON fields

srcno — Fri, 16 Jun 2023 17:42:57 GMT

I am trying to create a table whereby two of the values are within a JSON array. The data in each array entry is based on the "type" field. I can't seem to figure out how to extract the proper json using json_extract or spath, so I assume I'm going in the wrong direction. Any help would be appreciated.

I can't figure out how to say 'Extract the value from displayName for the array entry where a specific key/value pair match my criteria'

Any help is appreciated.

Example Data

{ "actor": { "type": "User", "alternateId": "john.smith@example.com" }, "target": [ { "type": "User", "alternateId": "jane.doe@example.com", "displayName": "Doe, Jane", "detailEntry": null }, { "type": "UserGroup", "alternateId": "unknown", "displayName": "Good Employees", "detailEntry": null } ], "uuid":"58dd3885-0c4a-11ee-9843-938af4d00f2c" }

Preferred Output

Actor	Group	User
john.smith@example.com	Good Employees	jane.doe@example.com

Re: Extract multiple JSON fields

ITWhisperer — Fri, 16 Jun 2023 16:35:25 GMT

| spath actor.alternateId output=Actor | spath target{} output=target | eval User=mvindex(target,0) | spath input=User path=alternateId output=User | eval Group=mvindex(target,1) | spath input=Group path=displayName output=Group | table Actor Group User

Re: Extract multiple JSON fields

yuanliu — Fri, 16 Jun 2023 16:49:40 GMT

First, how accurate is the representation in that data illustration? The sample contains an extraneous comma after alternateId, rendering the blob invalid as JSON. If that is a problem, you need to bring that to your developers.

If I assume that the original data is valid, notice that target node is an array. So, you will need to handle multiple entries in that entity. In SPL, an array is flattened with an suffix "{}". Usually you do not need JSON_EXTRACT in props.conf if the raw event is valide JSON; Splunk will automatically extract for you. As a result, you should see flattened fields such as actor.*, target{}.*, and uuid. To get individual objects in target{}, meanwhile, you should extract target{}, then split multivalues first.

| spath path=target{} | mvexpand target{} | spath input=target{}

This should give you something like

alternateId	detailEntry	displayName	type
jane.doe@example.com	null	Doe, Jane	User
unknown	null	Good Employees	UserGroup

Here is an emulation of your illustrated data with that syntax correction. You can play with it and compare with your actual data.

| makeresults | eval _raw = "{ \"actor\": { \"type\": \"User\", \"alternateId\": \"john.smith@example.com\" }, \"target\": [ { \"type\": \"User\", \"alternateId\": \"jane.doe@example.com\", \"displayName\": \"Doe, Jane\", \"detailEntry\": null }, { \"type\": \"UserGroup\", \"alternateId\": \"unknown\", \"displayName\": \"Good Employees\", \"detailEntry\": null } ], \"uuid\":\"58dd3885-0c4a-11ee-9843-938af4d00f2c\" }" ``` data emulation above ```

Re: Extract multiple JSON fields

srcno — Fri, 16 Jun 2023 17:44:49 GMT

Thank you. Your data is representative of mine. I failed to lint the output after I masked/removed private and irrelevant fields.

Re: Extract multiple JSON fields

srcno — Fri, 16 Jun 2023 18:21:27 GMT

Thank you. This gave me the output I wanted for most records and was a great starting point for me to clean up - there was no guarantee of the order these options showed up in the array so I am now looking that up with the mvfind.

index=okta eventType="group.user_membership.*"
| spath actor.alternateId output=Actor
| spath target{} output=target
| eval UserPosition = mvfind('target{}.type', "User")
| eval GroupPosition = mvfind('target{}.type', "UserGroup")
| eval User=mvindex(target,UserPosition)
| spath input=User path=alternateId output=User
| eval Group=mvindex(target,GroupPosition)
| spath input=Group path=displayName output=Group
| table Actor Group User

Re: Extract multiple JSON fields

yuanliu — Sat, 17 Jun 2023 05:48:22 GMT

You missed mvexpand target. You shouldn't have to use mvfind if you filter by type=User after mvfind. See my answer above.

| mvexpand target{} | spath input=target{} | fields - target{}.* target{} _raw | eval User = if(type == "User", alternateId, null()) | eval Group = if(type == "UserGroup", displayName, null()) | rename actor.alternateId AS Actor | stats values(User) as User values(Group) as Group by Actor