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 |
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?
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 |
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.
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.
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
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 |