Splunk Search

extract JSON from a field

dbcase
Motivator

Hi, I have data that looks like this

alt text

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Can you try this

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

View solution in original post

woodcock
Esteemed Legend

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you try this

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

dbcase
Motivator

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

0 Karma

dbcase
Motivator

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"

0 Karma

dbcase
Motivator

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

0 Karma

dbcase
Motivator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

dbcase
Motivator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

dbcase
Motivator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

dbcase
Motivator

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

0 Karma

dbcase
Motivator

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

0 Karma

dbcase
Motivator

Better look at the message/json field

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

dbcase
Motivator

my (failed) search attempt

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

dbcase
Motivator

Another failed search attempt

index=pds2 sourcetype=ucontrol_logs|mvexpand message | spath input=message|table externalRefId
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...