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

[vernonfuller]

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?

[vernonfuller · Accepted solution]

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]

Try this. (On indexer)


props.conf

[your_sourcetype] 
REPORT-extractkv = key_json

transforms.conf

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

[wsnyder2]

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.

[wsnyder2]

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

[somesoni2]

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...

[vernonfuller]

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.