topic Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs in Splunk Search

Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 14:15:11 GMT

I have the following log event but I have not been able to use spath to extract the json key=value pairs.

2013-03-12 10:37:10,205 <tvsquery id=58b6bf4d-948b-416b-8d17-cedcbc1059ec>{
"start" : 1,
"returned" : 0,
"count" : 0
}</tvsquery>

Therefore, I tried to extract the json portion with this regex and then use spath:

|rex field=_raw "[^>]+)>(?.+?)"|spath input=response

But I having a hard time to make it work.

How can I extract the json portion of the event and then use spath to extract the key=value pairs?

Thanks,
Lp

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Tue, 12 Mar 2013 14:53:48 GMT

I believe you need to keep the {} in the field as well, which your rex omits. Try this for your rex:

rex "(?< json_field>{[^}]+})"

(remove the space used to keep text from disappearing)

Or if you want to keep the more complex regex, simply move the closing } into the capture group.

You should then be able to use spath on the rex'ed field.

EDIT TO ADD:
If you've got events with multiple JSON objects, then you'll have to do a bit more work with it. I would recommend adding the max_match param to the rex, which will find multiple matches and collect them into a multi-valued field. Then you can mvexpand that field to multiple events, and parse that with spath.

rex max_match=10 "(?< json_field>{[^}]+})" | mvexpand json_field | spath input=json_field ...

(replace rex as needed)

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

jonuwz — Tue, 12 Mar 2013 14:55:20 GMT

is that json in xml ? yowsers

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 15:03:41 GMT

Thanks, It worked.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 15:16:58 GMT

I tried using the regex in a more complex json field but it fails. It worked for the simple json presented in the example.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

jonuwz — Tue, 12 Mar 2013 15:19:38 GMT

With difficulty.

Here's a starter :

...
| rex "<tvsquery id=(?<id>[^>]+)>(?<response>.*)</tvsquery>" 
| table id response _time
| spath input=response
| eval key=_time.";".id
| fields - response _time id
| untable key field value

Now you can either have the values in seperate events :

| rex field=key "(?<_time>.*);(?<id>.*)"
| fields - key

Or for easy reading :

| stats list(field) as fields list(value) as values by key 
| rex field=key "(?<_time>.*);(?<id>.*)"
| fields - key

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Tue, 12 Mar 2013 15:53:42 GMT

I think the most important thing is that in your original rex, the closing } wasn't part of the capture group, so the field being extracted was
{ "start" : 1, "returned" : 0, "count" : 0
which spath will fail on.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 17:13:06 GMT

Thanks for the observation. I corrected this problem as you recommended. And I was able to extract the json portion of the event and use spath. However, I am facing the same issue I had at the beginning: if the extracted json field contains multiple arrays and objects both regex fail to extract json portion of the event.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 17:17:59 GMT

what do you recommend?

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Mon, 28 Sep 2020 13:30:12 GMT

Then I would add a max_match= condition to the rex, so it could capture more than one JSON array into a multi-valued field. Then pipe that to mvexpand so that they get split to multiple events.

rex max_match=10 "regex_string" |mvexpand field_name | spath ...

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 17:44:46 GMT

It did not work. It continues to fail if the extracted json field contains multiple arrays and objects. I made sure that max_match=value was not greater that the number of objects.
Could you kindly see the log example I posted in case 114699?
thanks,LP

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Tue, 12 Mar 2013 17:54:28 GMT

I'm just another user, so I can't see your cases. Sorry!

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Tue, 12 Mar 2013 18:10:18 GMT

I reviewed my props.conf and I removed
KV_MODE = json from the related sourcetype. Then, reload props.conf and I ran the query with your recommendation. It seems to be working. I need to validate the result set.

Thanks,
Lp

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Thu, 14 Mar 2013 17:50:04 GMT

If this is working, would you mind accepting the answer? That way other people searching will see this is something with a resolution. Thanks!

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Wed, 20 Mar 2013 15:17:06 GMT

The regular expression behaves if an only if there is not any json array like the presented example.
It partially solves the problem.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Thu, 21 Mar 2013 14:27:47 GMT

This approach should work. However, I am still unable to make the regex work in my environment.

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

jonuwz — Thu, 21 Mar 2013 16:03:46 GMT

Why not ? what does response get populated with ? (just run the 1st 2 lines)

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

lpolo — Thu, 28 Mar 2013 12:24:56 GMT

The following regex will work, if and only if, there is not any new line in the event:

rex "<tvsquery id=(?<id>[^>]+)>(?<response>.+?)</tvsquery>"

Therefore, I was able to make it work by trimming the event before the regular expression as follow:

| rex field=_raw mode=sed "s/[\r\n]//g" 
| rex "<tvsquery id=(?<id>[^>]+)>(?<response>.+?)</tvsquery>"

Then, the extracted field "response" can be processed by spath search command.

Regards,
Lp

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

marciogh — Tue, 13 Sep 2016 07:35:41 GMT

By an unknown reason, I have to replace single quotes for double quotes in order to make a duplicate spath call.
"level" is a JSON field inside a JSON in message field.

 * | spath message | eval message=replace(message,"'","\"") |spath input=message | search level=INFO

Got the tip from here https://answers.splunk.com/answers/444133/extract-json-from-a-field.html

Re: Part 1: How to extract a json portion of an event then use spath to extract key=value pairs

emiller42 — Wed, 14 Sep 2016 14:31:52 GMT

This is because using single-quotes isn't valid JSON, so it can't parse it as JSON.

{"aaa": 1, "bbb": "some value"}
vs.
{'aaa': 1, 'bbb': 'some value'}

The first is JSON. The second is not.

Since it's a field extracted from a larger JSON, I'm going to assume it's just incorrectly constructed. Something like this would work fine, and not require multiple spath calls:

{"id": 12345, "message": {"level": "INFO", "content": "foo bar baz"}}

But I'm going to guess what you have to work with is:

{"id": 12345, "message": "{'level': 'INFO', 'content': 'foo bar baz'}"}

Which isn't valid JSON at all. (or more specifically, message is just a string, and not a JSON object)