Splunk Search

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

khandelwaly
Explorer

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 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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.

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...