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?
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.
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.
@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.
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
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.
Perfect. This gives the entire content within {}. It is a single string. I should be able to work with it.