How do I perform count logic on all entries for a specific line?

[andrewtrobec]

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

[sundareshr]

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

[andrewtrobec]

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

[sundareshr]

Sounds like this is what you need

https://answers.splunk.com/answers/392089/ticket-analytics-how-to-chart-open-tickets-over-ti.html