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

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

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

bsdd04
Engager

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

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...