topic Re: How to extract fields from a JSON file from Google Analytics API using spath or other options? in Splunk Search

How to extract fields from a JSON file from Google Analytics API using spath or other options?

guimilare — Thu, 10 Sep 2015 21:10:07 GMT

Hi dear Splunkers

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

{"kind":"analytics#gaData","id":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:XXXXXXXX&dimensions=ga:operatingSystem&metrics=ga:users&sort=-ga:users&start-date=30daysAgo&end-date=yesterday","query":{"start-date":"30daysAgo","end-date":"yesterday","ids":"ga:XXXXXXXX","dimensions":"ga:operatingSystem","metrics":["ga:users"],"sort":["-ga:users"],"start-index":1,"max-results":1000},"itemsPerPage":1000,"totalResults":9,"selfLink":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:XXXXXXXX&dimensions=ga:operatingSystem&metrics=ga:users&sort=-ga:users&start-date=30daysAgo&end-date=yesterday","profileInfo":{"profileId":"XXXXXXXX","accountId":"XXXXXXXX","webPropertyId":"XXXXXXXX-X","internalWebPropertyId":"XXXXXXXX","profileName":"Todos os dados do website","tableId":"ga:XXXXXXXX"},"containsSampledData":false,"columnHeaders":[{"name":"ga:operatingSystem","columnType":"DIMENSION","dataType":"STRING"},{"name":"ga:users","columnType":"METRIC","dataType":"INTEGER"}],"totalsForAllResults":{"ga:users":"17700"},"rows":[["Windows","13440"],["Android","2131"],["iOS","1439"],["Linux","281"],["Macintosh","280"],["Windows Phone","78"],["(not set)","29"],["Chrome OS","19"],["BlackBerry","3"]]}

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

OS                  Number
Windows             13440
Android              2131
iOS                  1439
Linux                 281
Macintosh             280
Windows Phone          78
(not set)              29
Chrome OS              19
BlackBerry              3

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

Re: How to extract fields from a JSON file from Google Analytics API using spath or other options?

somesoni2 — Thu, 10 Sep 2015 21:43:12 GMT

Try this

Your base search | table _raw | rex mode=sed "s/\[(\"[^\"]+\"),(\"[^\"]+\")\]/{\"OS\":\1,\"Number\":\2}/g" | spath | table rows* | rename rows{}.* as * | table OS Number

This will give you multivalued field for OS and Number. If you further split each OS as separate event, try this

 Your base search | table _raw | rex mode=sed "s/\[(\"[^\"]+\"),(\"[^\"]+\")\]/{\"OS\":\1,\"Number\":\2}/g" | spath | table rows* | rename rows{}.* as * | eval temp=mvzip(OS,Number,"##") | table temp | mvexpand temp | rex field=temp "(?<OS>.*)##(?<Number>.*)"| table OS Number

Re: How to extract fields from a JSON file from Google Analytics API using spath or other options?

guimilare — Fri, 11 Sep 2015 13:27:29 GMT

Thank you somesoni2 !
It worked perfectly!!
Best regards!