Getting Data In

Extract data from Jason

ppanchal
Path Finder

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"}]

0 Karma

mayurr98
Super Champion

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!

0 Karma

anthonymelita
Contributor

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

0 Karma

ppanchal
Path Finder

can you give me a complete search query?

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

0 Karma

anthonymelita
Contributor

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

0 Karma

ppanchal
Path Finder

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

date site
2018-01-30 S01027

0 Karma
Get Updates on the Splunk Community!

Changes to Splunk Instructor-Led Training Completion Criteria

We’re excited to share an update to our instructor-led training program that enhances the learning experience ...

Stay Connected: Your Guide to January Tech Talks, Office Hours, and Webinars!

❄️ Welcome the new year with our January lineup of Community Office Hours, Tech Talks, and Webinars! &#x1f389; ...

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...