topic Re: json data extraction further into fields in Getting Data In

json data extraction further into fields

dhirendra761 — Tue, 29 Sep 2020 23:32:31 GMT

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.

Re: json data extraction further into fields

tiagofbmm — Sat, 02 Mar 2019 10:24:02 GMT

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

Re: json data extraction further into fields

dhirendra761 — Tue, 05 Mar 2019 14:10:31 GMT

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.

Re: json data extraction further into fields

tsaikumar009 — Tue, 05 Mar 2019 15:45:28 GMT

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

Re: json data extraction further into fields

sdchakraborty — Tue, 05 Mar 2019 18:14:36 GMT

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

Re: json data extraction further into fields

dhirendra761 — Thu, 07 Mar 2019 10:39:46 GMT

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

Thanks,
Dhirendra

Re: json data extraction further into fields

sdchakraborty — Fri, 08 Mar 2019 05:50:04 GMT

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