Getting Data In
Highlighted

How to parse and extract JSON log files in Splunk?

Engager

I need to parse Tableau 8.2 JSON log files. Sample two rows of the log files is as below:

{"ts":"2014-07-30T07:14:06.840","pid":2104,"tid":"1e88","sev":"info","req":"U9j9rgqgYVgAAA@kHr8AAAHs","sess":"B84F19DEC88D4362B9164D87A687CFBC-0:1","site":"Default","user":"nsawant","k":"begin-query","v":{"protocol":"c4561a0","query":"(restrict (aggregate (select (project (table [Extract].[Extract]) ((yr:Date:ok) ([none:Region:nk] [Region]) ([none:Country / Region:nk] [Country / Region]))) (= [none:Region:nk] \"Asia\")) (([none:Country / Region:nk] [none:Country / Region:nk])) ((avg:P: Population (count):ok))) ([none:Country / Region:nk] [avg:P: Population (count):ok]))"}}
{"ts":"2014-07-30T07:14:06.856","pid":2104,"tid":"1e88","sev":"info","req":"U9j9rgqgYVgAAA@kHr8AAAHs","sess":"B84F19DEC88D4362B9164D87A687CFBC-0:1","site":"Default","user":"nsawant","k":"end-query","v":{"protocol":"c4561a0","cols":2,"query":"(restrict (aggregate (select (project (table [Extract].[Extract]) ((yr:Date:ok) ([none:Region:nk] [Region]) ([none:Country / Region:nk] [Country / Region]))) (= [none:Region:nk] \"Asia\")) (([none:Country / Region:nk] [none:Country / Region:nk])) ((avg:P: Population (count):ok))) ([none:Country / Region:nk] [avg:P: Population (count):ok]))","rows":34,"elapsed":0.011}}

Is there a way to use Splunk to parse this and extract one value? If so, how?

Thank you in advance.

Best Regards,
Namrata Sawant

Tags (3)
Highlighted

Re: How to parse and extract JSON log files in Splunk?

SplunkTrust
SplunkTrust
0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Engager

Well I am a complete newbie and still trying to figure out where and how I need to use the "spath" command? Do you have any video tutorial that demonstrates this?

Thank you!

0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Splunk Employee
Splunk Employee

I don't know of a video, but this search should work as a quick demo.

The json you posted does not validate because of the broken quotes around ' Asia '. I fixed that, then escaped the quotes (so that I could inject it via the search bar).

index=_internal | head 1 | eval f = "{\"ts\": \"2014-07-30T07:14:06.840\",\"pid\": 2104,\"tid\": \"1e88\",\"sev\": \"info\",\"req\": \"U9j9rgqgYVgAAA@kHr8AAAHs\",\"sess\": \"B84F19DEC88D4362B9164D87A687CFBC-0:1\",\"site\": \"Default\",\"user\": \"nsawant\",\"k\": \"begin-query\",\"v\": {\"protocol\": \"c4561a0\",\"query\": \"(restrict (aggregate (select (project (table [Extract].[Extract]) (([yr:Date:ok] (year [Date])) ([none:Region:nk] [Region]) ([none:Country / Region:nk] [Country / Region]))) (= [none:Region:nk] Asia))(([none: Country / Region: nk][none: Country / Region: nk]))(([avg: P: Population(count): ok](average[P: Population(count)]))))([none: Country / Region: nk][avg: P: Population(count): ok]))\"}}" | fields f | spath input=f | fields - f
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Splunk Employee
Splunk Employee

Namrata,
You can also have Splunk extract all these fields automatically during index time using KV_MODE = JSON setting in the props.conf. Give it a shot it is a feature I think of Splunk 6+. For example:

[Tableau_log]
KV_MODE = JSON

It is actually really efficient as Splunk has a built in parser for it.

Highlighted

Re: How to parse and extract JSON log files in Splunk?

Contributor

Bumping this topic. Is there a specific use case for these two modes of extracting JSON stuff?

And what about the overhead if any?

Documentation is fairly sketchy on this topic.

0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Splunk Employee
Splunk Employee

KV_MODE = json tells splunk to automatically perform search time extractions on json data
INDEXED_EXTRACTIONS = json tells splunk to create index time extractions for the data

In the first scenario you are saving disk at the expense of processing power (and potentially search time)
In the second scenario you are saving processing power (and potentially search time) at the expense of disk

0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Explorer

I downvoted this post because kv_mode is used for search-time field extractions only

0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Explorer

Not exactly sure why that warranted a down vote, as search-time extractions are pretty normal in Splunk and the question didn't call specifically for indexed extractions...furthermore indexed extractions are generally not recommended. Both answers are technically valid.

0 Karma
Highlighted

Re: How to parse and extract JSON log files in Splunk?

Explorer

I down voted because he said to use KVMODE = json during index time.
Can you really use KV
MODE = json during index time?????

0 Karma