topic Extract values from nested JSON with same name fields in Getting Data In

Extract values from nested JSON with same name fields

bsdd04 — Thu, 08 Apr 2021 21:13:42 GMT

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

Re: Extract values from nested JSON with same name fields

ITWhisperer — Thu, 08 Apr 2021 21:31:21 GMT

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

Re: Extract values from nested JSON with same name fields

bsdd04 — Fri, 09 Apr 2021 18:12:18 GMT

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