Count events for each time value in a given interval

[kiruwka]

Dear Community.
Given:

• events, each has start_time, end_time
• Time Range: [BEGIN, END]

output the following statistic:

for each time t, in [BEGIN, END] with interval 5 Min, count how many events satisfy: start_time < t < end_time

I am looking at concurrency and timechart, but can't wrap my head around. Any help would be very appreciated!

[tscroggins]

@kiruwka 

This is a good candidate for using a custom command, but you can do this in a resource intensive way using a join and e.g. gentimes to create a list of t values:

| join max=0 type=outer ```generate a field of t values```
    [| gentimes start=12/27/2021 end=12/28/2021 increment=300 
    | eval t=tonumber(starttime) 
    | fields + t 
    | mvcombine t ] 
| eval start_time=strptime(start_time, "%F %T") ```convert start_time to an epoch value```
| eval end_time=strptime(end_time, "%F %T") ```convert end_time to an epoch value```
| eval t=split(t, " ") ```convert t values to a multivalued field```
| eval t=mvmap(t, t."|".if(start_time<=t AND t<end_time, 1, 0)) ```compare start_time and end_time to each t value```
| mvexpand t ```expand all t value comparisons into separate events```
| eval t=split(t, "|") ```convert t value comparions to a multivalued field```
| eval _time=mvindex(t, 0) ```set _time to the t value```
| eval count=mvindex(t, 1) ```set count to the comparison value (0 or 1)```
| stats sum(count) by _time

I used start_time <= t < end_time to bin values where start_time == t. You could alternatively use start_time < t <= end_time, but without one or the other, some events may not be counted.

If all transaction durations are exactly the interval time, e.g. 5 minutes, you can use timechart:

| eval _time=end_time ```or strptime(end_time, "%F %T") or whatever's appropriate```
| timechart span=5m count