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!

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

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...