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!
@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.