topic Re: How to get specific value from key/value array to form table? in Splunk Search

How to get specific value from key/value array to form table?

btsr — Tue, 07 Feb 2023 02:10:25 GMT

Hi All,

I don't have much experience with Splunk. My JSON payload looks like as shown below. The msg.details array can have any number key/value pairs in any order.

{ "appName": "TestApp", "eventType": "Response", "msg": { "transId": "Trans1234", "status": "Success", "client": "clientXyz", "responseTime": 1650, "details": [ { "keyName": "rtt", "keyValue": 2778 }, { "keyName": "trace", "keyValue": 97007839130680 } ], "url": "/v1/test" } }

I am trying to write a query and form a table as shown below. I am interested in displaying only the keyValue of keyName:trace in the table. Any help is appreciated. Thanks.

index=* appName="TestApp" msg.url="/v1/test" | table msg.transId, msg.status, msg.details[keyName="trace"].keyValue

msg.transId	msg.status	msg.details[keyName="trace"].keyValue
Trans1234	Success	97007839130680
Trans7890	ERROR	29411645500355

Re: Get specific value from key/value array to form table

richgalloway — Tue, 07 Feb 2023 01:19:01 GMT

Splunk doesn't recognize square brackets as denoting a JSON array; they identify a subsearch.

We also can't reference a JSON array element by name. Perhaps some day...

Here's a run-anywhere query showing one way to accomplish the task.

| makeresults | eval _raw="{ \"appName\": \"TestApp\", \"eventType\": \"Response\", \"msg\": { \"transId\": \"Trans1234\", \"status\": \"Success\", \"client\": \"clientXyz\", \"responseTime\": 1650, \"details\": [ { \"keyName\": \"rtt\", \"keyValue\": 2778 }, { \"keyName\": \"trace\", \"keyValue\": 97007839130680 } ], \"url\": \"/v1/test\" } }" | spath ``` Everything above sets up test data. Delete IRL. ``` ``` Combine keyName and keyValue so we can work with them as a pair. ``` | eval foo=mvzip('msg.details{}.keyName','msg.details{}.keyValue') ``` Locate "trace" keys ``` | eval foo=mvindex(foo,mvfind(foo,"trace")) ``` Break up the keyName/keyValue pair for display ``` | eval foo=split(foo,",") | eval keyName=mvindex(foo,0), keyValue=mvindex(foo,1) | table msg.transId, msg.status, keyName, keyValue

Re: How to get specific value from key/value array to form table?

ITWhisperer — Tue, 07 Feb 2023 07:47:48 GMT

Another way to do it is to extract and expand the collection

| spath | spath msg.details{} output=details | mvexpand details | spath input=details | where keyName="trace" | table msg.transId msg.status keyValue

Re: How to get specific value from key/value array to form table?

btsr — Tue, 07 Feb 2023 19:49:38 GMT

This saved my day! Thank you @ITWhisperer!