Splunk Search

Get multiple name/value from JSON file

abilis
Explorer

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
415
345
654

 

  image.png

thanks a lot for your time and support

 

 

 

 

 

Labels (1)
0 Karma

ITWhisperer
Super Champion

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.

abilis
Explorer

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

 

Tags (1)
0 Karma

ITWhisperer
Super Champion

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

0 Karma

abilis
Explorer

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

0 Karma