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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...