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
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.
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
Where does GarageDesc come from? It isn't shown in your JSON image.
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