Multi field combination for JSON file question

darkelfaxe — Sun, 15 Mar 2020 17:39:09 GMT

I'm totally new to splunk, I have this JSON file already indexed:

{"EventType":2,"EventData":{"Values":[{"Status":1,"Name":"BOT1"},{"Status":0,"Name":"BOT2"},{"Status":0,"Name":"BOT3"},{"Status":1,"Name":"BOT4"}],"Subject":"Resource Online Status","Source":"Dashboard"}}

I need to create a table which contains the Values in separate columns like this:

ID STATUS RESOURCE
1 1 BOT1
2 0 BOT2
3 0 BOT3
4 1 BOT4

I'm trying the following:
index="main" resource online Status | table "EventData.Values{}.Name" "EventData.Values{}.Status" | sort -_time asc | head 1
But it gives me this:

ID EventData.Values{}.Name EventData.Values{}.Status
1 BOT1 BOT2 BOT3 BOT4 1 0 0 1

How can I combine the two columns to generate the desired format?

Thank you!

Re: Multi field combination for JSON file question

niketn — Sun, 15 Mar 2020 19:50:36 GMT

@darkelfaxe, if your issue is that you are getting a multi-value results from your JSON, try adding the following to your existing query

index="main" resource online Status
| head 1
| fields "EventData.Values{}.Name" "EventData.Values{}.Status"
| rename "EventData.Values{}.*" as "*"
| eval EventData=mvzip(Name,Status)
| fields EventData
| mvexpand EventData
| makemv EventData delim=","
| eval RESOURCE=mvindex(EventData,0),STATUS=mvindex(EventData,1)
| streamstats count as ID
| fields ID STATUS RESOURCE

Please try out and confirm.
Following is a run anywhere example based on the sample data provided:

| makeresults
| eval _raw="{\"EventType\":2,\"EventData\":{\"Values\":[{\"Status\":1,\"Name\":\"BOT1\"},{\"Status\":0,\"Name\":\"BOT2\"},{\"Status\":0,\"Name\":\"BOT3\"},{\"Status\":1,\"Name\":\"BOT4\"}],\"Subject\":\"Resource Online Status\",\"Source\":\"Dashboard\"}}"
| spath
| head 1
| table "EventData.Values{}.Name" "EventData.Values{}.Status"
| rename "EventData.Values{}.*" as "*"
| eval EventData=mvzip(Name,Status)
| fields EventData
| mvexpand EventData
| makemv EventData delim=","
| eval RESOURCE=mvindex(EventData,0),STATUS=mvindex(EventData,1)
| streamstats count as ID
| fields ID STATUS RESOURCE

PS:
1. table is a transforming command, you should avoid use fields instead.
2. Splunk events are sorted in reverse chronological order by default i.e. sort - _time just consumes performance if you are only interested in latest event.
3. head 1 should be first pipe after index search as it pulls only one event from indexer. This should improve performance of your search.

Refer to Writing Better Searches Splunk documentation to better understand above points.

topic Re: Multi field combination for JSON file question in Getting Data In

Multi field combination for JSON file question

Re: Multi field combination for JSON file question