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

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

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
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...