topic Re: How to fetch value from nested JSON in Dashboards & Visualizations

How to fetch value from nested JSON

rkishoreqa — Fri, 13 Aug 2021 12:03:38 GMT

Hi team,

How can I get the value of 'status' from below payload in Splunk search.

{"log":" \"status\" : \"END\",","payload":"stdout","time":"2021-08-13T11:54:17.255787345Z"}

Thanks in Advance.

Re: How to fetch value from nested JSON

manjunathmeti — Fri, 13 Aug 2021 14:04:14 GMT

hi @rkishoreqa,

Use rex command.

| makeresults | eval f="{\"log\":\" \"status\" : \"END\",\",\"payload\":\"stdout\",\"time\":\"2021-08-13T11:54:17.255787345Z\"}" | rex field=f "\"status\"\s:\s\"(?<status>\w+)\""

If this reply helps you, a like would be appreciated.

Re: How to fetch value from nested JSON

rkishoreqa — Fri, 13 Aug 2021 16:48:01 GMT

@manjunathmeti

I tried with below query, but it is not working.

index="dev" |rex field=f "\"status\"\s:\s\"(?<status>\w+)\"" |stats count by status.

Re: How to fetch value from nested JSON

kamlesh_vaghela — Fri, 13 Aug 2021 18:39:47 GMT

@rkishoreqa

Can you please try this?

YOUR_SEARCH | rex field=_raw "\\\\\"status\\\\\"\s\:\s\\\\\"(?<status>.*)\\\\\"," | stats count by status

My Sample Search :

| makeresults | eval _raw="{\"log\":\" \\\"status\\\" : \\\"END\\\",\",\"payload\":\"stdout\",\"time\":\"2021-08-13T11:54:17.255787345Z\"}" | rex field=_raw "\\\\\"status\\\\\"\s\:\s\\\\\"(?<status>.*)\\\\\"," | stats count by status

Re: How to fetch value from nested JSON

williamchenyp — Fri, 13 Aug 2021 19:10:52 GMT

Is this a one time use extraction, or do you need to do this extraction on a regular basis? Also, is the JSON formatting always identical where it always begins with:

{"log":" \"status\" : \"END\"

If it's only a time time use, what if you treat the double-quotes as a delimiter of a multivalue string? Then the SPL will be something like below to spit out \"status\"

| makeresults | eval aaa="{\"log\":\" \\\"status\\\" : \"END\",\",\"payload\":\"stdout\",\"time\":\"2021-08-13T11:54:17.255787345Z\"}" | eval aaa="\\\"".mvindex(split(aaa, "\""), 4)."\""

Re: How to fetch value from nested JSON

manjunathmeti — Mon, 16 Aug 2021 03:27:34 GMT

Remove field=f in your query. For field, you need to specify the actual field name that exists in your index dev. Or don't specify if you are extracting values from _raw event.

Try without specifying field:

index="dev" | rex "\"status\"\s:\s\"(?<status>\w+)\"" | stats count by status

If you have backslashes in your data then,

index="dev" | rex "\"status\\\\\"\s:\s\\\\\"(?<status>[^\\\]+)"