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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...