Splunk Search
Highlighted

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

Motivator

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

Tags (3)
Highlighted

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

Motivator

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)

Highlighted

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

Motivator

Thanks, It worked.

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Motivator

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.

0 Karma
Highlighted

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

Motivator

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.

0 Karma
Highlighted

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

Motivator

what do you recommend?

0 Karma
Highlighted

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

Motivator

Then I would add a maxmatch= 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 "regexstring" |mvexpand fieldname | spath ...

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Motivator

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

0 Karma