Hi All,
I am trying to create a dashboard for notable events that has been opened on the month and how many events closed during the month. The cases that are still being worked on will be carried over to the next month. Your help will be much appreciated.
@iwanwibisonoadha you would need to add more details in terms of data/fields and current query that you have for the community to assist you better. You can anonymize/mask any sensitive data/fields before sharing.
In general you would need to identify state/s that signify notable events opened in a month and the time field for the latest state of specific event. Similarly state/s that signify closed notable events within the same month and time field for the latest state of specific event.
You can use dedup to get the latest state each event through some unique event id field and get the latest event and time. Or else you can use stats
command to get min(_time) as earliest_time
and max(_time) as latest_time
for each event use values(state) as states
to get all the states the event has been into.
Then you can use search filter on multi-valued states field to check various conditions like
1) states="OPEN" AND states="CLOSED"
, implies events that were opened and closed in selected Time Range.
2) states="OPEN" AND states!="CLOSED"
, implies event that were opened in selected Time Range but not closed.
3) states!="OPEN" AND states="CLOSED"
, implies event that were closed in selected Time Range but opened before selected time range (backlogs closed).
4) states!="OPEN" AND states!="CLOSED"
, implies event that were closed in selected Time Range but opened before selected time range (backlogs closed). (backlog open events).
There would be several examples on Splunk answers for using stats for creating these kind of transactions. However, as stated earlier, for us to assist you better you would need to provide more details and the SPL query that you have tried.
I see. Please see below. I managed to whip something up however it is not showing up.
Thank you for letting me know.
notable
| search NOT supression
| ctime(review_time)
| eval _time=review_time | timechart span=month count by status_label
So the idea here is to replace the_time field with the review_time field as I would like to create a timechart based on when we update the notable event.
I hope that makes sense.
Iwan
Can you add some samples of review_time
field with String Time format?
Sure.
The format of review_time I believe is in seconds - 1519860410.185617
Converted using ctime(review_time)
= month/day/year hours/minutes/seconds.
review_time is epoch which is a good thing in two ways...
1) You can assign filters in your base search with Epoch time selected in Time Picker. Refer to my older answer as to how you will pick earliest and latest epoch time from time picker using independent search: https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html
2) Also, you can directly assign review_time to _time
(I dont think ctime()
is required) i.e. try the following run anywhere search.
| makeresults
| eval review_time=1519860410.185617
| eval _time=review_time