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

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

lpolo — Wed, 20 Mar 2013 15:43:29 GMT

I have the following log event but I have not been able to use spath to extract the json key=value pairs if the json portion contains arrays. Event example:

2013-03-12 10:37:10,205 <tvsquery id=58b6bf4d-948b-416b-8d17-cedcbc1059ec>{
"start" : 1,
"returned" : 1,
"count" : 1,
"entities" : [ {
"houses" : {
"callers" : "IM",
"placeid" : 5041447014850446107,
"number" : 14,
"sourceid" : 5625
},
"entitytype" : "house/street",
"title" : [ {
"default" : "No Place"
} ]
} ]
}</tvsquery>

The following answer solved the problem if the json protion does not contain any array:

http://splunk-base.splunk.com/answers/79029/part-1-how-to-extract-a-json-portion-of-an-event-then-use-spath-to-extract-keyvalue-pairs

I having a hard time to make it work.

Any help please!

Thanks,
Lp

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

jonuwz — Wed, 20 Mar 2013 21:33:41 GMT

Look at my answer in the original question you linked. It extracts everything, including values in arrays

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

lpolo — Thu, 21 Mar 2013 12:03:42 GMT

I tried but It does not work. The regex does not return any value. what do you suggest?

Thanks,
Lp

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

lpolo — Thu, 28 Mar 2013 12:38:07 GMT

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

rex "[^>]+)>(?.+?)"

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 "[^>]+)>(?.+?)"

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

Regards,
Lp