Splunk Search

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

satkumvnr
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

poete
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
Get Updates on the Splunk Community!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...