Splunk Search
Highlighted

How to extract fields from a JSON file from Google Analytics API?

New Member

Hi dear Splunkers

I have the following JSON given by a REST calling at Google Analytics:

{"kind":"analytics#realtimeData","id":"https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:XXXXXXX&dimensions=rt:eventCategory,rt:eventAction,rt:eventLabel&metrics=rt:totalEvents&filters=rt:eventCategory%3D%3Ddata","query":{"ids":"ga:XXXXXXX","dimensions":"rt:eventCategory,rt:eventAction,rt:eventLabel","metrics":["rt:totalEvents"],"filters":"rt:eventCategory==Results”,”max-results":1000},"totalResults":7,"selfLink":"https://www.googleapis.com/analytics/v3/data/realtime?ids=ga:XXXXXXX&dimensions=rt:eventCategory,rt:eventAction,rt:eventLabel&metrics=rt:totalEvents&filters=rt:eventCategory%3D%3Ddata","profileInfo":{"profileId":"XXXXXXX","accountId":"XXXXXXX","webPropertyId":"XXXXXXX","internalWebPropertyId":"XXXXXXX","profileName":"All Web Site Data","tableId":"realtime:XXXXXXX"},"columnHeaders":[{"name":"rt:eventCategory","columnType":"DIMENSION","dataType":"STRING"},{"name":"rt:eventAction","columnType":"DIMENSION","dataType":"STRING"},{"name":"rt:eventLabel","columnType":"DIMENSION","dataType":"STRING"},{"name":"rt:totalEvents","columnType":"METRIC","dataType":"INTEGER"}],"totalsForAllResults":{"rt:totalEvents":"377"},"rows”:[[“Success”,”search","0JIwnotL","67”],[“Failure”,”search","AgxsqvYs","8"],["Failure","search","DQhOXfQs","41"],["Failure","search","I6HTlk5z","68"],["Results","search","TL7dI0UN","60"],["Success","search","XcC3EU45","69"],["Success","search","oBTyH99G","64"]]}

I'm having a hard time trying to use spath so I can break the data I need as follows:

Results    Process   Info               Count
Failure     search     0JIwnotL       67
Success  search      XcC3EU45    69

Can you guys help me?
How can I use spath (or any other tool) to do this to break as a separate event?
Thanks in advance!

0 Karma
Highlighted

Re: How to extract fields from a JSON file from Google Analytics API?

Builder

Hello @satkumvnr,

according to JSONLint, the json is not valid.

I understand you had to modify it in order to remove sensitive information, but the result is not valid and I can not work with it.

Can you please check with jsonLint the result of the pruned json and post it back as an answer to this comment?

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.