Get multiple name/value from JSON file

[abilis]

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

[ITWhisperer]

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]

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

[ITWhisperer]

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

[abilis]

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