All Apps and Add-ons

How to parse JSON mixed in with text data or a timestamp?

vernonfuller
Explorer

This question should have been titled "How to parse JSON mixed in with text data or a timestamp?"
Does splunk support multi character delimiters?
I am trying to parse the fields: duration, status and url from the following log entry (all on one line):

2014-08-14 22:43:28,966 {"metricEvent":{"systemName":"production","metricId":"httpClientDao","userAgent":"null","acceptLanguage":"null","serverIP":"127.0.0.1","parameters":{"duration":"156","status":"404","api":"platform","Request":"2967231","url":"htttp://mysite/mypath"}}}

All of my fields are quoted and they are key value pairs separated by ',' with the ':' as the equal sign. Using a multi character Delimiter like \",\" does not work. Is there an obvious answer I am missing other than using regex and splitting it up myself?

1 Solution

vernonfuller
Explorer

Adding

| rex "^.{23} (?.*)" | spath input=data

to my search queries makes it so splunk can parse the JSON. The spath command expects JSON, but the preceding timestamp throws it off, so the above rex command ignores the first 23 characters (the size of my timestamp) and then matches everything else as a variable named 'data'. This way spath sees valid JSON from the first character and does a great job of pulling out the key-value fields automatically.

View solution in original post

vernonfuller
Explorer

Adding

| rex "^.{23} (?.*)" | spath input=data

to my search queries makes it so splunk can parse the JSON. The spath command expects JSON, but the preceding timestamp throws it off, so the above rex command ignores the first 23 characters (the size of my timestamp) and then matches everything else as a variable named 'data'. This way spath sees valid JSON from the first character and does a great job of pulling out the key-value fields automatically.

somesoni2
Revered Legend

Try this. (On indexer)


props
.conf

[your_sourcetype] 
REPORT-extractkv = key_json

transforms.conf

[key_json] 
REGEX = \"(?<_KEY_1>[^\"]+)\":\"(?<_VAL_1>[^\"]+)\"

wsnyder2
Path Finder

Is it possible to automate the spath functionality into a props.conf or transforms.conf ?

the previously given REPORT and transforms.conf ... work but not to the extent of the spath command.

0 Karma

wsnyder2
Path Finder

will this work on a search head? I would really like to apply this field extraction only at search time.

0 Karma

somesoni2
Revered Legend

You can create a new field without the timestamp and then use spath on that.

your base search | rex "\d+-\d+-\d+ \d+:\d+:\d+,\d+ (?.*)$" | spath input=Temp_raw...

0 Karma

vernonfuller
Explorer

Unfortunately I don't have access to my indexers, so I need to use something in the search path, like the spath command. I keep wondering if my timestamp before the JSON object is messing spath up.

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...