topic Re: Extract data from Jason in Getting Data In

Extract data from Jason

ppanchal — Wed, 31 Jan 2018 20:19:26 GMT

Hi,
I want to extract fields like date, site, etc from the below log (jason), how can I do this?

[{"date":"2018-01-30","site":"S01027","routePublishCount":"17","routeCount":"97","customerCount":"931"},{"date":"2018-01-30","site":"S02923","routePublishCount":"16","routeCount":"119","customerCount":"1248"},{"date":"2018-01-30","site":"S03175","routePublishCount":"14","routeCount":"79","customerCount":"701"},{"date":"2018-01-30","site":"S03422","routePublishCount":"24","routeCount":"146","customerCount":"1486"}]

Re: Extract data from Jason

anthonymelita — Wed, 31 Jan 2018 20:30:58 GMT

Splunk can do some automatic handling of Json. After your initial search command, try piping either
| spath
or
| extract pairdelim="{,}" kvdelim=":"

Re: Extract data from Jason

ppanchal — Wed, 31 Jan 2018 20:46:10 GMT

can you give me a complete search query?

I am doing,
index=* | table date, site

Re: Extract data from Jason

anthonymelita — Wed, 31 Jan 2018 22:17:23 GMT

I didn't pay close attention to your example being a single event multivalue json, so not entirely sure this will work:

index=*
| extract pairdelim="{,}" kvdelim=":"
| table date, site

there are other commands for handling multivalue like mvexpand

Re: Extract data from Jason

ppanchal — Thu, 01 Feb 2018 00:20:18 GMT

Not sure why but the above query is returning only single value from the jason. Please help.

date site
2018-01-30 S01027

Re: Extract data from Jason

mayurr98 — Thu, 01 Feb 2018 06:45:47 GMT

hey try this run anywhere search

| makeresults 
| eval _raw="[{\"date\":\"2018-01-30\",\"site\":\"S01027\",\"routePublishCount\":\"17\",\"routeCount\":\"97\",\"customerCount\":\"931\"},{\"date\":\"2018-01-30\",\"site\":\"S02923\",\"routePublishCount\":\"16\",\"routeCount\":\"119\",\"customerCount\":\"1248\"},{\"date\":\"2018-01-30\",\"site\":\"S03175\",\"routePublishCount\":\"14\",\"routeCount\":\"79\",\"customerCount\":\"701\"},{\"date\":\"2018-01-30\",\"site\":\"S03422\",\"routePublishCount\":\"24\",\"routeCount\":\"146\",\"customerCount\":\"1486\"}]" 
| spath 
| rename {}.* as *

In your environment, you should try

index=<your_index> 
| spath 
| rename {}.* as * 
| table date site

let me know if this helps!