Hello,
I am trying to determine the number of entries that have a field date that is before or equal to _time
. My data set is:
_time, Opened, Closed
2016-06-01 00:20:21,2016-06-01 00:20:21,2016-06-02 02:18:23
2016-06-01 00:37:16,2016-06-01 00:37:16,2016-06-02 02:25:14
2016-06-01 01:12:07,2016-06-01 01:12:07,2016-06-03 02:44:41
2016-06-01 01:17:27,2016-06-01 01:17:27,2016-06-03 03:00:08
2016-06-02 14:01:28,2016-06-02 14:01:28,2016-06-03 02:02:50
2016-06-02 14:04:06,2016-06-02 14:04:06,2016-06-03 02:23:52
2016-06-02 14:08:53,2016-06-02 14:08:53,2016-06-04 02:05:21
2016-06-03 18:46:25,2016-06-03 18:46:25,2016-06-04 02:47:58
2016-06-03 18:47:18,2016-06-03 18:47:18,2016-06-04 02:39:18
(during import _time was created based on Opened)
My goal is to get a table that will tell me for a given day, how many entries have an Opened
value equal to or before that day along with the number of entries that have a Closed
value equal to or before that day (but only for the entries that have already been counted due to their Opened
value):
_time,openedCount,closedCount
2016-06-01,4,0
2016-06-02,7,2
2016-06-03,9,4
I have tried as much as I can to use the join
command, but I'm not having much luck. Right now I am experimenting with the append
command to see whether I can achieve my goal. I'm opening this question in the meantime to see whether I can get any pointers...
Thank you!
Andrew
How about this. I am assuming given_day=today()
... | eval e_closed=strptime(Closed, "%Y-%m-%d %H:%M:%S") | eval given_day=relative_time(now(), "@d") | eval o_count=if(_time<=given_day, 1, 0) | eval c_count=if(e_closed<=given_day, 1, 0) | timechart span=1d sum(o_count) as opened sum(eval(if(c_count=1 AND o_count=1, 1, 0))) as closed
Thanks for the answer. It's not quite what I'm looking for but it's given me something to work with. In reality the given_day
should be the the _time
value for a particular entry.
What I'm looking for is:
Given the _time of an entry, how many objects were opened on or before that time, and how many of those same objects have been closed on or before that time?
Some pseudo-code:
FOR EVERY ENTRY BEFORE _time
INCLUDE IF [Opened] < [_time]
INCLUDE IF [Closed] < [_time]
What makes it difficult is that I have to be able to search through all previous data to make my calculation, and I am struggling to figure out how to do that.
Regards,
Andrew
Sounds like this is what you need
https://answers.splunk.com/answers/392089/ticket-analytics-how-to-chart-open-tickets-over-ti.html