Getting Data In
Highlighted

json data extraction further into fields

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="tmsapp" sourcetype="tmstransport_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
Highlighted

Re: json data extraction further into fields

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>[^\"]*)"

Highlighted

Re: json data extraction further into fields

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
Highlighted

Re: json data extraction further into fields

Explorer

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

Highlighted

Re: json data extraction further into fields

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
Highlighted

Re: json data extraction further into fields

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
Highlighted

Re: json data extraction further into fields

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