Counting events start\end in same event

[ryankrieger]

I have data coming from an Avaya phone system that provides me the end time of the event and the duration, I am creating a start_time field based on those fields.

I get a table with the trunk number, start time, duration, and end time.   I am trying to get trunk capacity, so I need to know how many events are listed as occurring between the start time and end time.   

index="voice_switch"  
| eval Trunk = mvappend(Incoming_Trunk_Group, Outgoing_Trunk_Group)
| eval Dur = (DurH * 60 *60) + (DurM*60) + (DurTenths *6) | eval Start_Time = _time - Dur  
| eval Duration = tostring(Dur, "duration")
| convert ctime(Start_Time)
| convert ctime(_time) 
| rename _time as End_Time
| table Trunk,Start_Time, Duration,End_Time

Trunk Start_Time Duration End_Time

8940	10/13/2020 07:59:58	00:23:48	10/13/2020 08:23:46
8940	10/13/2020 08:02:41	00:26:06	10/13/2020 08:28:47
8905	10/13/2020 08:05:57	00:16:54	10/13/2020 08:22:51
8940	10/13/2020 08:08:14	00:14:00	10/13/2020 08:22:14
8940	10/13/2020 08:08:18	00:14:36	10/13/2020 08:22:54
8905	10/13/2020 08:08:53	00:13:00	10/13/2020 08:21:53
8940	10/13/2020 08:09:37	00:26:18	10/13/2020 08:35:55
8940	10/13/2020 08:11:01	00:12:30	10/13/2020 08:23:31
8901	10/13/2020 08:11:22	00:11:18	10/13/2020 08:22:40
8940	10/13/2020 08:11:37	00:11:36	10/13/2020 08:23:13

[to4kawa]

index="voice_switch"  
| eval Trunk = coalesce(Incoming_Trunk_Group, Outgoing_Trunk_Group)
| eval Dur = (DurH * 60 *60) + (DurM*60) + (DurTenths *6)
| stats min(eval(_time - Dur)) as start_time max(_time) as end_time count by Trunk
| eval duration=end_time-start_time
| eval duration=tostring(duration, "duration")
| convert ctime(start_time) ctime(end_time)

Something like this?

[ryankrieger]

That is closer than I have gotten,   

The end result should be the max number of concurrently used channels during that span. 

if on trunk 8900

call 1 was from 08:00:00-08:02:00

the event would say trunk 8900  endtime 080200   and duration 2 min

call 2 was from 08:01:00-08:02:00

call 3 was from 08:01:00-08:04:00

call 4 was from 08:00:00-08:01:00

I want to see that at 0800 there were 2 calls, 0801 there were 4 calls, and 0803 there was 1 call

Here is an example table with what the previous guy was doing . It shows that at from 1000-1015  the max number of concurrent calls was 22 on trunk 8901 and 5 on trunk 8905

Trunk	10:00	10:15	10:30	10:45
8901	22	15	17	14
8905	5	8	4	9
8904	44	22	28	30
8912	12	11	15	8

[to4kawa]

I see what you want to do, but I don't have too much information.

between 15minutes OR 1minute?

index="voice_switch"  
| eval Trunk = coalesce(Incoming_Trunk_Group, Outgoing_Trunk_Group)
| eval Dur = (DurH * 60 *60) + (DurM*60) + (DurTenths *6)
| eval Start_Time = relative_time(_time - Dur ,"@min") 
| eval End_Time = if(_time % 60 = 0,_time,relative_time(_time+60,"@min"))
| eval times=mvrange(Start_time,End_time,900)
| mvexpand times
| timechart span=15min count by Trunk

I haven't looked at the log, so I don't know if it will work.

[ryankrieger]

I think that will get me the total calls during that time but I need to know that max number of active calls during that time.   what I am trying to watch for is a trunk group that has 177 members, how close is it to getting full, or has its volume dropped enough to reduce members and save cost.  

[rnowitzki]

Hi @ryankrieger,

Your requirement for the output is not 100% clear to me.

Let's take the first line

8940

10/13/2020 07:59:58

00:23:48

10/13/2020 08:23:46

Do you need to know how many events where happening in that same timeframe with the same trunk number 8940? Or how many events across all trunk numbers?

Maybe it's a "bad" example table, but all of the events were crossing times of all others. I mean, there was a timeframe around ~8:11 to 8:22 where all of this events were occuring/active...

Did I get it wrong somehow?

Could you provide a table that shows how the final table should look like. Will it have a column "count of parallel events"?

BR
Ralph

[ryankrieger]

@rnowitzki    

The end result should be the max number of concurrently used channels during that span. 

if on trunk 8900

call 1 was from 08:00:00-08:02:00

the event would say trunk 8900  endtime 080200   and duration 2 min

call 2 was from 08:01:00-08:02:00

call 3 was from 08:01:00-08:04:00

call 4 was from 08:00:00-08:01:00

I want to see that at 0800 there were 2 calls, 0801 there were 4 calls, and 0803 there was 1 call

Here is an example table with what the previous guy was doing . It shows that at from 1000-1015  the max number of concurrent calls was 22 on trunk 8901 and 5 on trunk 8905

Trunk	10:00	10:15	10:30	10:45
8901	22	15	17	14
8905	5	8	4	9
8904	44	22	28	30
8912	12	11	15	8