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.
Can you try this
index=pds2 sourcetype=ucontrol_logs|mvexpand message | eval message=replace(message,"'","\"") | spath input=message
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.
Can you try this
index=pds2 sourcetype=ucontrol_logs|mvexpand message | eval message=replace(message,"'","\"") | spath input=message
Well I'll be........ Works like a champ!!!! So what is that replace command doing that makes it work?
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"
something tells me that the message field name and the message field within the JSON are butting heads
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
It's again json inside json which is causing propblems.
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.... 🙂
Actually yes.. if possible. Having developer do logging in standard format will avoid all these surprises
I was hoping you would say that 🙂 If you want to change your comment to an Answer I'll go ahead and accept.
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
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
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.......
Better look at the message/json field
{'externalRefId':'exr654321','message':'RMA service return 202','serverResponseTimeMs':'136'}
my (failed) search attempt
index=pds2 sourcetype=ucontrol_logs|spath input=message|rename externalRefId AS ID externalRefId.message AS msg|table ID msg
Another failed search attempt
index=pds2 sourcetype=ucontrol_logs|mvexpand message | spath input=message|table externalRefId