topic Re: extract JSON from a field in Splunk Search

extract JSON from a field

dbcase — Thu, 25 Aug 2016 22:34:20 GMT

Hi, I have data that looks like this

I'd like to extract the json out of the message field. I see the spath command and I think that is what I need but I don't quite get how I can use it to see the json fields in the message field.

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 22:54:02 GMT

my (failed) search attempt

index=pds2 sourcetype=ucontrol_logs|spath input=message|rename externalRefId AS ID externalRefId.message AS msg|table ID msg

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:08:27 GMT

Another failed search attempt

index=pds2 sourcetype=ucontrol_logs|mvexpand message | spath input=message|table externalRefId

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:22:21 GMT

Better look at the message/json field

{'externalRefId':'exr654321','message':'RMA service return 202','serverResponseTimeMs':'136'}

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:32:53 GMT

another search query. It looks like spath simply isnt doing anything

index=pds2 sourcetype=ucontrol_logs|spath input=message|stats list(*)

No fields within the message field have been parsed... Odd..... my head hurts and there is a bloody spot on the wall.......

Re: extract JSON from a field

somesoni2 — Thu, 25 Aug 2016 23:49:54 GMT

Can you try this

index=pds2 sourcetype=ucontrol_logs|mvexpand message | eval message=replace(message,"'","\"") | spath input=message

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:53:49 GMT

Well I'll be........ Works like a champ!!!! So what is that replace command doing that makes it work?

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:57:41 GMT

except one piece..... it doesn't extract the message from the json
the ones it does extract are
list(externalRefId)
list(serverResponseTimeMs)

but this one is missing
message":"RMA service return 202"

Re: extract JSON from a field

dbcase — Thu, 25 Aug 2016 23:58:49 GMT

something tells me that the message field name and the message field within the JSON are butting heads

Re: extract JSON from a field

somesoni2 — Fri, 26 Aug 2016 00:02:16 GMT

Could be. The replace in the first query was replacing single quotes with double quotes. Lets add another replace in that to replace message field inside the json data to inner_message.

index=pds2 sourcetype=ucontrol_logs|mvexpand message | eval message=replace(replace(message,"'message'","'inner_message'"),"'","\"") | spath input=message

Re: extract JSON from a field

dbcase — Fri, 26 Aug 2016 00:03:55 GMT

Hmmmm, one other thing the more I look at it

Here is another json message in the data {"externalRefId":"exr654321","message":"input: {"wifiNetwork":{"ssidName":"YOCTO_2.1S9","securityPassphrase":"xxxxxxx"}}"}

and ssidName, securityPassphrase fields aren't being extracted...... argh

Re: extract JSON from a field

dbcase — Fri, 26 Aug 2016 00:06:58 GMT

One more step, now it pulls the message out (man, I would have never gotten this far!)

The other fields are still MIA though

ssidName, securityPassphrase fields

Re: extract JSON from a field

somesoni2 — Fri, 26 Aug 2016 00:07:41 GMT

It's again json inside json which is causing propblems.

Re: extract JSON from a field

dbcase — Fri, 26 Aug 2016 00:10:50 GMT

so the best way to fix is to tell the developer, "HEY! Don't do that?" Because, I can do that as he sits upstairs 🙂 Plus it would be a bit of fun.... 🙂

Re: extract JSON from a field

somesoni2 — Fri, 26 Aug 2016 00:28:24 GMT

Actually yes.. if possible. Having developer do logging in standard format will avoid all these surprises

Re: extract JSON from a field

dbcase — Fri, 26 Aug 2016 00:38:22 GMT

I was hoping you would say that 🙂 If you want to change your comment to an Answer I'll go ahead and accept.

Re: extract JSON from a field

woodcock — Fri, 26 Aug 2016 02:21:10 GMT

This is not a complete answer but it DEFINITELY will help if you add this just before your spath:

| rex field=message mode=sed "s/'/\"/g"

You need to figure out what is/isn't valid JSON and then use rex to adjust message to conformant.