Splunk Search

Counting events start\end in same event

ryankrieger
Loves-to-Learn

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

894010/13/2020 07:59:5800:23:4810/13/2020 08:23:46
894010/13/2020 08:02:4100:26:0610/13/2020 08:28:47
890510/13/2020 08:05:5700:16:5410/13/2020 08:22:51
894010/13/2020 08:08:1400:14:0010/13/2020 08:22:14
894010/13/2020 08:08:1800:14:3610/13/2020 08:22:54
890510/13/2020 08:08:5300:13:0010/13/2020 08:21:53
894010/13/2020 08:09:3700:26:1810/13/2020 08:35:55
894010/13/2020 08:11:0100:12:3010/13/2020 08:23:31
890110/13/2020 08:11:2200:11:1810/13/2020 08:22:40
894010/13/2020 08:11:3700:11:3610/13/2020 08:23:13

 

Labels (4)
0 Karma

to4kawa
Ultra Champion
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?

0 Karma

ryankrieger
Loves-to-Learn

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

Trunk10:0010:1510:3010:45
890122151714
89055849
890444222830
89121211158
0 Karma

to4kawa
Ultra Champion

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.

0 Karma

ryankrieger
Loves-to-Learn

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.  

0 Karma

rnowitzki
Builder

Hi @ryankrieger,

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

Let's take the first line

894010/13/2020 07:59:5800:23:4810/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

--
Karma and/or Solution tagging appreciated.
0 Karma

ryankrieger
Loves-to-Learn

@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

Trunk10:0010:1510:3010:45
890122151714
89055849
890444222830
89121211158
0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...