topic Re: Get multiple name/value from JSON file in Splunk Search

Get multiple name/value from JSON file

abilis — Thu, 14 Jan 2021 15:54:12 GMT

Hi everyone,

I've been trying several day to create a query that can give me the list of name/value inside the JSON file

the file has hundreds of event and each event has multiple name/value as the picture below

I'm not able to create a table out of the name/value pair from each event as below

can anyone help me or guide me in the right direction ?

GarageId	GarageClassId	GarageTypeId
4	1	5
3	4	5
6	5	4

thanks a lot for your time and support

Re: Get multiple name/value from JSON file

ITWhisperer — Thu, 14 Jan 2021 17:48:31 GMT

Here is a starting point

| makeresults | eval _raw="{ \"Locking\": \"blah\", \"accessDate\": \"blah, blah\", \"auditResultSets\": [ { \"action\": \"U\", \"commandType\": 0, \"records\": [ { \"newValues\": [ { \"name\": \"GarageId\", \"value\": 4 }, { \"name\": \"GarageClassId\", \"value\": 1 }, { \"name\": \"GarageTypeId\", \"value\": 5 } ], \"OriginalValues\": [ { \"name\": \"GarageId\", \"value\": 4 }, { \"name\": \"GarageClassId\", \"value\": 2 }, { \"name\": \"GarageTypeId\", \"value\": 4 } ] } ] } ] }" | spath input=_raw path=auditResultSets{}.records{}.newValues{} | fields - _raw | rename "auditResultSets{}.records{}.newValues{}" as newValues | mvexpand newValues | spath input=newValues | eval namevalue=mvzip(name, value, ":") | fields - _time name value | mvexpand namevalue | rex field=namevalue "(?<name>[^:]+):(?<value>.+)" | fields - namevalue | eval {name}=value | fields - name value newValues | stats values(*) as *

Obvious issues are that the stats should probably include a by clause so that each row represents the values from a defined newValues collection, and, it only works for newValues (not OriginalValues as well), but it does demonstrate the principal of creating dynamically named fields with their values.

Re: Get multiple name/value from JSON file

abilis — Thu, 14 Jan 2021 20:13:51 GMT

wow, thanks a lot... i worked on it several days and still i was far from getting something like this.

i added the this at the end

| stats values(GarageDesc) as Description by GarageId

GarageId Description

3
4

i am not getting the GarageDesc, it seems lke if the GarageID where no associated to the values of GarageDesc

i am looking to get the multiple GarageDesc values (one value per event) of all the events with specific GarageId

i had this before but i seem to have same issue

spath path=auditResultSets{}.records{}.newValues{}.name output=fieldId
| spath path=auditResultSets{}.records{}.newValues{}.value output=value
| eval fieldValue=mvzip(fieldId,value)
| mvexpand fieldValue
| eval fieldValue=split(fieldValue,",")
| eval fieldId=mvindex(fieldValue,0)
| eval value=mvindex(fieldValue,1)
| table fieldId value

thanks for your time

Re: Get multiple name/value from JSON file

ITWhisperer — Thu, 14 Jan 2021 20:36:02 GMT

Where does GarageDesc come from? It isn't shown in your JSON image.

Re: Get multiple name/value from JSON file

abilis — Thu, 14 Jan 2021 20:56:55 GMT

sorry, you are right.

it's just another key like GarageTypeId, but i have in another JSON file.

please ignore the GarageDesc, let's use GarageTypeId