Splunk Search

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

splunk_skr
Explorer

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?

0 Karma
1 Solution

gokadroid
Motivator

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

View solution in original post

0 Karma

gokadroid
Motivator

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

0 Karma

splunk_skr
Explorer

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

0 Karma

gokadroid
Motivator

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
0 Karma

splunk_skr
Explorer

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.

0 Karma

splunk_skr
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...