Getting Data In

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

jkastning
Engager

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

0 Karma

mikaeloo
New Member

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

Maybe

 index=abc | fields message | extract | table *
0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

jkastning
Engager

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.

0 Karma

niketn
Legend

@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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...