How to extract a particular field and value from JSON data in Splunk?

[khandelwaly]

We have the below data, out of which I wanted to extract a particular field and value from the json format.

PLATFORMINSTRUMENTS {“timestamp”:“1607797705",“instrumentList”:[{“name”:“dbcp.numIdle”,“value”:“100”},{“name”:“entity.versions.total”,“value”:“66137”}{“name”:“http.session.objects”,“value”:“2443”},{“name”:“dbcp.numActive”,“value”:“0”},
{“name”:“http.sessions”,“value”:“544”},
{“name”:“dbcp.maxActive”,“value”:“-1”}]}

expected output:

dbcp.numActive : 0 

[ITWhisperer · Accepted solution]

Assuming you have already got or can get your JSON into a field (I have used _raw in this example) and that it is properly formatted (your text doesn't use proper double-quotes and you are missing a comma between two parts of your list), you could do something like this.

| makeresults | eval _raw="{\"timestamp\":\"1607797705\",\"instrumentList\":[{\"name\":\"dbcp.numIdle\",\"value\":\"100\"},{\"name\":\"entity.versions.total\",\"value\":\"66137\"},{\"name\":\"http.session.objects\",\"value\":\"2443\"},{\"name\":\"dbcp.numActive\",\"value\":\"0\"},
{\"name\":\"http.sessions\",\"value\":\"544\"},
{\"name\":\"dbcp.maxActive\",\"value\":\"-1\"}]}"
| spath input=_raw
| rename "instrumentList{}.name" as name "instrumentList{}.value" as value
| eval namevalue=mvzip(name, value, ":")
| fields - _raw _time name value
| mvexpand namevalue
| rex field=namevalue "(?<name>[^:]+):(?<value>.+)"
| fields - namevalue
| where name="dbcp.numActive"
| eval {name}=value

I wasn't sure if you wanted the name and value in different fields or a field named as the name with the value in it so I have shown both.