Getting Data In

Extract values from nested JSON with same name fields

bsdd04
Engager

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": "mail@acme.com", "profileId": "1234567890"}, "events": [{"type": "user_action", "name": "message_posted", "parameters": [{"name": "room_id", "value": "ABCDEFGH"}, {"name": "timestamp_ms", "value": "1617909687595199"}, {"name": "actor", "value": "mail@acme.com"}, {"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

Labels (3)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

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\": \"mail@acme.com\", \"profileId\": \"1234567890\"}, \"events\": [{\"type\": \"user_action\", \"name\": \"message_posted\", \"parameters\": [{\"name\": \"room_id\", \"value\": \"ABCDEFGH\"}, {\"name\": \"timestamp_ms\", \"value\": \"1617909687595199\"}, {\"name\": \"actor\", \"value\": \"mail@acme.com\"}, {\"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

View solution in original post

ITWhisperer
Ultra Champion

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\": \"mail@acme.com\", \"profileId\": \"1234567890\"}, \"events\": [{\"type\": \"user_action\", \"name\": \"message_posted\", \"parameters\": [{\"name\": \"room_id\", \"value\": \"ABCDEFGH\"}, {\"name\": \"timestamp_ms\", \"value\": \"1617909687595199\"}, {\"name\": \"actor\", \"value\": \"mail@acme.com\"}, {\"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

View solution in original post

bsdd04
Engager

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

0 Karma