Extract values from nested JSON with same name fields

[bsdd04]

Hi people,

First of all I'm still newbie with Splunk, but I'm trying to extract fields from a JSON sent by the Admin Report API from Google and I'm having trouble. 

Here is a sample JSON:

{"kind": "admin#reports#activity", "id": {"time": "2021-04-08T19:21:27.595Z", "uniqueQualifier": "-0987654321", "applicationName": "chat", "customerId": "C0123456A"}, "etag": "\"giant/string\"", "actor": {"callerType": "USER", "email": "[email protected]", "profileId": "1234567890"}, "events": [{"type": "user_action", "name": "message_posted", "parameters": [{"name": "room_id", "value": "ABCDEFGH"}, {"name": "timestamp_ms", "value": "1617909687595199"}, {"name": "actor", "value": "[email protected]"}, {"name": "message_id", "value": "ZYXWVUTS"}, {"name": "retention_state", "value": "PERMANENT"}, {"name": "room_name", "value": ""}]}]}

What I want is to create some counts/dashboards with fields from "parameters" along with others, but they all have the same name in this nest.

This is what I came with, but all the "name" and "value" strings are grouped:

| spath path=events{}. output=events
| mvexpand events
| rename events as _raw
| extract
| rename parameters{}.name as pname, parameters{}.value as pvalue
| table _time pname pvalue

Also I don't know if this can be done with the search parameters only (sorry If I'm talking nonsense).

Thanks

[ITWhisperer · Accepted solution]

Perhaps this will help as far as extracting fields and values

| makeresults 
| eval _raw="{\"kind\": \"admin#reports#activity\", \"id\": {\"time\": \"2021-04-08T19:21:27.595Z\", \"uniqueQualifier\": \"-0987654321\", \"applicationName\": \"chat\", \"customerId\": \"C0123456A\"}, \"etag\": \"\\\"giant/string\\\"\", \"actor\": {\"callerType\": \"USER\", \"email\": \"[email protected]\", \"profileId\": \"1234567890\"}, \"events\": [{\"type\": \"user_action\", \"name\": \"message_posted\", \"parameters\": [{\"name\": \"room_id\", \"value\": \"ABCDEFGH\"}, {\"name\": \"timestamp_ms\", \"value\": \"1617909687595199\"}, {\"name\": \"actor\", \"value\": \"[email protected]\"}, {\"name\": \"message_id\", \"value\": \"ZYXWVUTS\"}, {\"name\": \"retention_state\", \"value\": \"PERMANENT\"}, {\"name\": \"room_name\", \"value\": \"\"}]}]}"
| spath path=events{}. output=events
| spath input=events path=parameters{} output=parameters
| mvexpand parameters
| spath input=parameters
| eval {name}=value
| stats values(*) as * by events
| fields - parameters name value events

[bsdd04]

That's perfect! I still need to grasp my understanting of mv and spath commands though... 🤔