Getting Data In

json data extraction further into fields

dhirendra761
Contributor

Hi All,

My base search has a field "msg" , that contain below json data.

{"level":50,"time":1550758285865,"msg":"validate fail {\"isPP\":true,\"name\":\"ValidationError\",\"details\":[{\"message\":\"\\\" ghdsg to kjsd \\\" udnd nsbd at sndj 4 \\",\"path\":[],\"type\":\"hdyn.min\",\"context\":{\"jdhn\":4,\"value\":[{\"id\":0,\"name\":\"Thdaj JHD KJJ \",\"email\":\"abc@xyz.com\",\"phone\":\"1\",\"title\":\"GSTb SK Issue - KJDK JHDJ hours\",\"isSy\":true}],\"label\":\"ghdsg to kjsd\"}}],\"_object\":[{\"id\":0,\"name\":\"Thdaj JHD KJJ \",\"email\":\"abc@xyz.com\",\"phone\":\"1\",\"title\":\"ajsdj  Insdkssue - YTSV working hours\",\"isActive\":SG}],\"isFF\":true,\"isGtsver\":false,\"data\":null,\"output\":{\"statusCode\":400,\"jdjkdjk\":{\"statusCode\":400,\"error\":\"Bad Request\",\"message\":\"\\\"jdjnis jds kdskd \\\" udnd nsbd at sndj 4 items\",\"validation\":{\"source\":\"yejdhi\",\"keys\":[\"\"]}},\"headers\":{}}}","pid":87487,"hostname":"ip-55423-762-232-636.dsdf.jds.ksndkd.ksmd","v":1}

Our requirement is that extracting above json data into field which are same as Key in json.
eg.

msg--> validate fail
isPP--> true
name--> ValidationError
statusCode--> 400
hostname --> ip-55423-762-232-636.dsdf.jds.ksndkd.ksmd

my search query is like below, but it didn't work:
host="ITEM-s73365" index="tms_app" sourcetype="tms_transport_app" | spath| rename msg.name as messageName ,msg.details as msgDetails| eval msgDetails = trim(msgDetails,"\")| table _time messageName msgDetails

Could you please suggest?
Thanks in advance.

0 Karma
1 Solution

sdchakraborty
Contributor

Hi Dhirendra,

you can use rex to extract your fields. I am giving you one example hopefully you will be able to extract as per your need.

base_search
| | rex field=msg "\"message\":\"(?<msg>[^\{]*)\",.*\s*\"path\":(?<path>.*),\s*\"type\":\"(?<type>.*)\",\s*\"context\":{\s*\"tshdi\":(?<context_tshdi>.*),"

Sid

View solution in original post

0 Karma

sdchakraborty
Contributor

Hi Dhirendra,

you can use rex to extract your fields. I am giving you one example hopefully you will be able to extract as per your need.

base_search
| | rex field=msg "\"message\":\"(?<msg>[^\{]*)\",.*\s*\"path\":(?<path>.*),\s*\"type\":\"(?<type>.*)\",\s*\"context\":{\s*\"tshdi\":(?<context_tshdi>.*),"

Sid

0 Karma

dhirendra761
Contributor

Hi @sdchakraborty Thanks for your answer. can you please put your comment in Answer section so that I can accept your answer.

Thanks,
Dhirendra

0 Karma

sdchakraborty
Contributor

Done. Also the suggestion from @tsaikumar009 is also correct. If there is any possibility for you to change the sourcetype to _json that would be better solution.

Sid

0 Karma

tsaikumar009
Explorer

please try to onboard this data with _json as the sourcetype , then you will be able to see all the fields as expected.

tiagofbmm
Influencer

Your json event is not well formatted, so it's likely that it is the reason for the spath not to work as expected.

Check a json formatter to see what is wrong there. If for some reason your message really is badly formatted, you may use rex to extract the fields or find a way to format it correctly. If I the message is correct then

| rex field=test "\"msg\":\"(?<msg>[^\{]*).*\"isPP\":(?<isPP>[^\,]*).*\"name\":\"(?<name>[^\"]*).*\"statuscode\":\"(?<statuscode>[^\,]*).*\"hostname\":\"(?<hostname>[^\"]*)"

dhirendra761
Contributor

Hi @tiagofbmm ,

Sorry for late reply and thanks for your answer. I have formatted my data as below:

msg:server notice notification failed 
    {"isJoi":true,
    "name":"NotificationError",
    "details":
        [{
            "message":"\"there is an error\" might be 6 letters",
            "path":[],
            "type":"yetrn.hsnk",
            "context":{
                "tshdi":4,
                "value":
                    [{
                        "id":0,
                        "name":"BHE Camel Lakne",
                        "email":"xyz.abc@abc.com",
                        "phone":"1",
                        "title":"This is text message",
                        "isActive":true
                    }],
                "label":"contacts to save"
                }
        }],
    "_object":
        [{
            "id":0,
            "name":"BHE Camel Lakne",
            "email":"xyz.abc@abc.com",
            "phone":"1",
            "title":"This is text message",
            "isActive":true
        }],
    "isBosom":true,
    "isSrsgver":false,
    "data":null,
    "output":
        {
            "statusCode":400,
            "gstaflsoad":
                {
                "statusCode":400,
                "error":"Bad Request",
                "message":"\"there is an error\" might be 6 letters or 5 words",
                "validation":
                    {
                    "source":"thsgmn",
                    "keys":[""]
                    }
                },
            "headers":{}
        }
    }

Could you please suggest How to extract in their respective fields.

Thanks.

0 Karma
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...