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!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! &#x1f44f; Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...