topic Re: How to parse a JSON array that follows my search string? in Splunk Search

How to parse a JSON array that follows my search string?

splunk_skr — Wed, 30 Nov 2016 15:58:44 GMT

My log file has multiple JSONs being printed in one line.

{JSON string 1}
My Search String : {"key1":"value1","key2":"value2"}
{JSON string 3}

I am trying to read the JSON that follows my search string, then get data for the key value pairs. So basically the JSON I need is between the {} following my search string. I can play with the JSON after I get it. Any idea how I can extract just that json data?

Re: How to parse a JSON array that follows my search string?

gokadroid — Wed, 30 Nov 2016 17:13:39 GMT

Please try this:

your query to return the event
| rex field=_raw "(?s).*(My Search String)\s*\:\s*\{(?<myJson>[^\}]+)\}"
| table myJson

Replace your search string with (My Search String) above taking care of escaping any special characters, like " . or * etc. in between that string if it is different.

See here for the output of regex

Re: How to parse a JSON array that follows my search string?

splunk_skr — Wed, 30 Nov 2016 19:38:09 GMT

Perfect. This gives the entire content within {}. It is a single string. I should be able to work with it.

Re: How to parse a JSON array that follows my search string?

splunk_skr — Wed, 30 Nov 2016 22:17:12 GMT

@gokadroid I used the rex you suggested and that returned the data between {}. table option put it under one column. Here is how it looks

"key1":"value1","key2":"value2"
"key1":"value1","key2":"value2"

I want to create a table with key1, key2 as headers and value1, value2 as the actual values inside it. Here is what i did

    query to return event | rex field=_raw "(?s).*(My Search String)\s*\:\s*\{(?<myJson>[^\}]+)\}" | mvexpand myJson
     | eval str=split(myJson,",") 
     | eval col1=mvindex(str,0) 
     | eval col2=mvindex(str,1)
| table  col1, col2

This is working but i get both key & value in column data. Is there a better way to get a cleaner table.

Re: How to parse a JSON array that follows my search string?

gokadroid — Thu, 01 Dec 2016 01:53:39 GMT

Indeed there always is a better way:

How about we try to make key and value out of all the kv which exist in this string by doing something like this:

query to return event 
| rex field=_raw "(?s).*(My Search String)\s*\:\s*\{(?<myJson>[^\}]+)\}" 
| rex field=myJson max_match=0 "\"(?<key>[^\"]+)\":\"(?<value>[^\"]+)\""
| eval myKV=mvzip( key, value, "~")
| mvexpand myKV
| rex field=myKV "(?<myKey>[^~]+)~(?<myValue>.*)"
| table myKey, myValue

Re: How to parse a JSON array that follows my search string?

splunk_skr — Fri, 09 Dec 2016 19:55:15 GMT

This works but not completely 🙂 . What i am looking for is a transpose. This query prints multiple rows for one result - key/values put in multiple rows. I need all values in one row. Table Header Row for all Keys and values in multiple rows/columns under each header.