How to extract nested key value pairs from a specific JSON string field using spath and kvdelim?

[jkastning]

I have JSON that looks like this. With the "message" field, there can be one or more key value pairs. How can I extract the key value pairs that are within the "message" field?

{

"severity":"INFO",
"logger":"controllers.offers.OfferController",
"thread":"application-akka.actor.default-dispatcher-297",
"message":"2017-09-14 15:12:56,980 [I] c.o.OfferController h5FCZGLPj95A7DPq 67b33d676699b9cab76c7f86 \/offers\/private\/offer\/saveOffer\/25 POST Successfully saved offerId=69 for productId=3 ",
"properties":{

"path":"\/offers\/private\/offer\/saveOffer\/25",
"http_method":"POST",
"request_id":"xxxxxGLPj95xxxxx",
"client_id":"xxxxxd676699b9cab76xxxxx"
}
}

I've tried this, but it doesn't work:

index=xyz |  spath input=message | extract kvdelim="=" pairdelim=" " | table offerId, productId

I need to be able to do this at search time since it's not possible for me to modify props.conf

[mikaeloo]

I had the exact same problem as you, and I solved it by a slight modification of your attempt:

index=xyz | rename _raw AS _temp message AS _raw | extract kvdelim="=" pairdelim=" " | table offerId, productId

As extract only can read from _raw, you need to rename the field you want to extract key value pairs from to _raw.

See https://docs.splunk.com/Documentation/SplunkCloud/8.1.2008/SearchReference/Extract for more info

[jkat54]

Can you just do the extract without a pairdelim? Or just search in verbose mode?

[jkat54]

Maybe

 index=abc | fields message | extract | table *

[niketn]

@jkastning, try the following run anywhere search based on the data provided. The makeresults command is used to prepare the dummy data, you would need to place your base search instead. You can plugin the remaining part of the query as-is to your existing query (for testing and using with the actual data you have):

| makeresults
| eval message="{ 
\"severity\":\"INFO\",
\"logger\":\"controllers.offers.OfferController\",
\"thread\":\"application-akka.actor.default-dispatcher-297\",
\"message\":\"2017-09-14 15:12:56,980 [I] c.o.OfferController h5FCZGLPj95A7DPq 67b33d676699b9cab76c7f86 \\/offers\\/private\\/offer\\/saveOffer\\/25 POST Successfully saved offerId=69 for productId=3 \",
\"properties\":{ 
\"path\":\"\\/offers\\/private\\/offer\\/saveOffer\\/25\",
\"http_method\":\"POST\",
\"request_id\":\"xxxxxGLPj95xxxxx\",
\"client_id\":\"xxxxxd676699b9cab76xxxxx\"
}
}"
| spath input=message
| rex field=message "\s(?<key>\w+)=(?<value>[^\s]+)\s" max_match=0
| eval data=mvzip(key,value)
| table data
| mvexpand data
| eval data=split(data,",")
| eval key=mvindex(data,0)
| eval value=mvindex(data,1)
| fields - data

PS: I have used simple regex expression for key/value extraction. If the pattern is complicated you would need to adjust the regular expression. Please use regex101.com for testing/validation of your regular expression used by rex command.

[jkastning]

thanks @niketnilay, this does work if the "message" string only has 1 key value pair, but it doesn't pull out the second key value pair. When I run the above query (removing the "makeresults") I get this structure:

key value
offerId 69

Ideally I want to display the data in a table format, such that I can show:

offerId productId
69 3

...or any other name value pair that could possibly be in the "message" field. For example, another message value could be:

message: 2017-09-14 18:11:00,729 [E] c.o.OfferController Failed to find PromoCode promoCode=test error=168

I'm looking for a search query that's generic enough I can I choose whichever variable(s) are in the message string and display those in a table.

[niketn]

@jkastning, sorry for the delay in responding to this.

Your current example pattern for key value pair is different than the one stated in your question. Since this answer is depended on the actual data pattern using Regular Expression. You either need to provide various samples or else adjust the regular expression as per your needs. You can use regex101.com for testing out the Regular Expression with your sample data.

In your case you can make the Regular Expression a bit more lenient in this case by removing \s from the end of Regular Expression it should work i.e.: \s(?<key>\w+)=(?<value>[^\s]+)

As per the expected tabular output requested in comment you need to also throw in transpose command (following is a mock query with sample data provided in comment):

 | makeresults
 | eval message="message: 2017-09-14 18:11:00,729 [E] c.o.OfferController Failed to find PromoCode promoCode=test error=168"
 | spath input=message
 | rex field=message "\s(?<key>\w+)=(?<value>[^\s]+)" max_match=0
 | eval data=mvzip(key,value)
 | table data
 | mvexpand data
 | eval data=split(data,",")
 | eval key=mvindex(data,0)
 | eval value=mvindex(data,1)
 | fields - data
 | transpose header_field=key column_name=key
 | search key!="_mkv_child"