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

satkumvnr — Fri, 16 Feb 2018 05:03:28 GMT

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!

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

poete — Tue, 10 Jul 2018 14:22:56 GMT

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?

topic Re: How to extract fields from a JSON file from Google Analytics API? in Splunk Search

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

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