Is there a way to obtain peak SIP trunk usage?

General Help
1

Hi,

In my current setup, SIP trunk can handle 12 concurrent call sessions.
I am thinking to optimize the value and wish to obtain peak usage of the day. (Perhaps from CDR record? I have 2 years+ CDR records…)

It may also be nice if I can receive email notification from FreePBX host when SIP trunk concurrent usage hits certain number of threshold (for example, send email when the concurrent call number exceeded 6, 8 or 10…) this will give me rough statistic how it is used.

Any advice would be appreciated!

Many thanks in advance,

2

So far I configured cronjob to get some data. (I may not capture the spike but it is okay for my environment for now…)

[root@pbx ~]# crontab -l
* * * * * echo $(date +"%D %T"),$(eval asterisk -rx "sip show channels" | grep -c "SIP_Trunk_Name") >> active_SIP_trunk_count.csv
1 Like
3

Querying your asteriskcdrdb by time group is a challenge for all but the cognoscenti but

Might get you started

1 Like
4

Reporting from your SIP provider?

5

I used to do this with CAMA trunks.

Umm… let me see if I remember the jist of it…
Here is the query that I ended up using… of course, this was for a slightly different system (although still Asterisk)… but it gives you the idea.

  • Note: This was written in MSSQL, certainly don’t expect to drop it into mysql and have ANYTHING but errors. This just shows the logic.

Essentially, what it does is every time a call STARTS, it adds one, and when a call ENDS, it subtracts one (change), then groups them together over a timeframe so you can see how many seconds 1 was in use, 2 were in use, 3 were in use, etc. so that you have a representation of “How long were we at 100% capacity? 75% capacity? 50% capacity?” etc.

With occupancychanges (ts, change) as 
     (Select StartTimeStamp as ts, 1 as change from 
    @yourtable Union All 
    Select DisconnectTimeStamp as ts, -1 as change from @yourtable) 
Select occupiedlines_timespans.occupiedcount, 
    Cast(Sum(datediff(second, Case When 
    StartTimeStamp > @t1 Then StartTimeStamp 
    else @t1 
    end, Case When 
EndTimeStamp < @t2 Then 
   EndTimeStamp 
   else @t2 end))/60.0 as real) as TotalMinutes 
From (SELECT SUM(change) 
     OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) 
     as occupiedcount, ts as StartTimeStamp, LEAD(ts) 
     OVER (ORDER BY ts) as EndTimeStamp 
     FROM occupancychanges) occupiedlines_timespans Where 
    @t1 < EndTimeStamp AND StartTimeStamp < @t2 Group by 
    occupiedlines_timespans.occupiedcount
1 Like
6

Here is a good article on running totals in SQL (which should be adaptable to mysql).

This is the preferred method over a loop that looks at start and end times, which would be very costly (in the SQL world)

https://sqlperformance.com/2012/07/t-sql-queries/running-totals

7

Hi Dicko and Greg,

Thank you very much for sharing detailed info! I downloaded CDR in .csv file, manipulated through pivot table to grasp some idea, but knowing exact concurrent use was a bit difficult from Excel environment. Though, I had a rough idea about the trunk usage, roughly 6 to 8 session during peak. (and this is happening rarely…)

Going forward, I thought it may be nice if I capture the number of trunk usage whenever it is used, I was looking at /etc/asterisk/extensions_additional.conf and I saw [ext-trunk] context as below,

... <snipped> ...

exten => tdial,1,Set(OUTBOUND_GROUP=OUT_${DIAL_TRUNK})
exten => tdial,n,GotoIf($["${OUTMAXCHANS_${DIAL_TRUNK}}" = ""]?nomax)
exten => tdial,n,GotoIf($[${GROUP_COUNT(OUT_${DIAL_TRUNK})} >= ${OUTMAXCHANS_${DIAL_TRUNK}}]?hangit)
exten => tdial,n(nomax),ExecIF($["${CALLINGNAMEPRES_SV}" != ""]?Set(CALLERPRES(name-pres)=${CALLINGNAMEPRES_SV}))
exten => tdial,n,ExecIf($["${CALLINGNUMPRES_SV}" != ""]?Set(CALLERPRES(num-pres)=${CALLINGNUMPRES_SV}))
exten => tdial,n,Set(DIAL_NUMBER=${FROM_DID})
exten => tdial,n,GosubIf($["${PREFIX_TRUNK_${DIAL_TRUNK}}" != ""]?sub-flp-${DIAL_TRUNK},s,1())
exten => tdial,n,Set(OUTNUM=${OUTPREFIX_${DIAL_TRUNK}}${DIAL_NUMBER})
exten => tdial,n,Set(DIAL_TRUNK_OPTIONS=${IF($["${DB_EXISTS(TRUNK/${DIAL_TRUNK}/dialopts)}" = "1"]?${DB_RESULT}:${TRUNK_OPTIONS})})
exten => tdial,n,Dial(${TDIAL_STRING}/${OUTNUM}${TDIAL_SUFFIX},${TRUNK_RING_TIMER},${DIAL_TRUNK_OPTIONS})
exten => tdial,n,Set(CALLERID(number)=${CALLERID(number):0:40})
exten => tdial,n,Set(CALLERID(name)=${CALLERID(name):0:40})
exten => tdial,n(hangit),Hangup

;--== end of [ext-trunk] ==--;

I thought I may be able to capture concurrent SIP trunk usage by using asterisk Function_FILE, so I copied [ext-trunk] section in /etc/asterisk/extensions_override_freepbx.conf, I added one line, and restarted with fwconsole restart, and made outside call (or call in from outside), but no change in file yet… :sweat_smile:

... <snipped> ...

exten => tdial,1,Set(OUTBOUND_GROUP=OUT_${DIAL_TRUNK})
;2018-09-14: The number of concurrent SIP Trunk usage is noted in /var/log/asterisk/active_sip_trunk.csv
exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,,,al,u)=$[${GROUP_COUNT(OUTBOUND_GROUP)}])
exten => tdial,n,GotoIf($["${OUTMAXCHANS_${DIAL_TRUNK}}" = ""]?nomax)
exten => tdial,n,GotoIf($[${GROUP_COUNT(OUT_${DIAL_TRUNK})} >= ${OUTMAXCHANS_${DIAL_TRUNK}}]?hangit)
exten => tdial,n(nomax),ExecIF($["${CALLINGNAMEPRES_SV}" != ""]?Set(CALLERPRES(name-pres)=${CALLINGNAMEPRES_SV}))
exten => tdial,n,ExecIf($["${CALLINGNUMPRES_SV}" != ""]?Set(CALLERPRES(num-pres)=${CALLINGNUMPRES_SV}))
exten => tdial,n,Set(DIAL_NUMBER=${FROM_DID})
exten => tdial,n,GosubIf($["${PREFIX_TRUNK_${DIAL_TRUNK}}" != ""]?sub-flp-${DIAL_TRUNK},s,1())
exten => tdial,n,Set(OUTNUM=${OUTPREFIX_${DIAL_TRUNK}}${DIAL_NUMBER})
exten => tdial,n,Set(DIAL_TRUNK_OPTIONS=${IF($["${DB_EXISTS(TRUNK/${DIAL_TRUNK}/dialopts)}" = "1"]?${DB_RESULT}:${TRUNK_OPTIONS})})
exten => tdial,n,Dial(${TDIAL_STRING}/${OUTNUM}${TDIAL_SUFFIX},${TRUNK_RING_TIMER},${DIAL_TRUNK_OPTIONS})
exten => tdial,n,Set(CALLERID(number)=${CALLERID(number):0:40})
exten => tdial,n,Set(CALLERID(name)=${CALLERID(name):0:40})
exten => tdial,n(hangit),Hangup

;--== end of [ext-trunk] ==--;

when I performed asterisk -rx "dialplan show ext-trunk", I see my update so it is loaded properly, but it is not logging the number of trunk.

I will play around a bit more. (I wonder how people debug dialplan in general, perhaps with Log function, but this will be stored in default file (/var/log/asterisk/full?) I prefer to store info in separate file possibly with timestamp. ;-))

8

That is two lines below your added custom line. It basically is checking if the group count is greater than the allowed max channels. Did you try using what is there instead of GROUP_COUNT(OUTBOUND_GROUP)? Did you try ${GROUP_COUNT(OUT_${DIAL_TRUNK})}

Also don’t forget that 1 call has at least TWO channels in it. One for the user and one for the trunk. So when it says there are 6 active channels that doesn’t mean there are 6 active calls.

1 Like
9

Hi Tom,

Thank you, other than two lines below, it is basically a dead copy of [ext-trunk] from /etc/asterisk/extensions_additional.conf file.

;2018-09-14: The number of concurrent SIP Trunk usage is noted in /var/log/asterisk/active_sip_trunk.csv
exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,,,al,u)=$[${GROUP_COUNT(OUTBOUND_GROUP)}])

I believe $[${GROUP_COUNT(OUT_${DIAL_TRUNK})} &gt;= ${OUTMAXCHANS_${DIAL_TRUNK}}] would be evaluated if Maximum Channels field is set with some number, I am currently not setting it in my SIP trunk configuration.

image

For the test, I set SET(FILE(/var/log/asterisk/active_sip_trunk.csv,,,al,u)=foo) but nothing was stored in the file while the trunk is in use. I changed that line with Log or NoOp function but no effect.

Maybe I am not using correct context, perhaps, I should modify [from-pstn] for checking how many concurrent session is in the trunk?

10

You’re missing my point. You are trying to get a group count of ${OUTBOUND_GROUP} which is being set here:

exten => tdial,1,Set(OUTBOUND_GROUP=OUT_${DIAL_TRUNK})

You’re trying to use it here:

exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,al,u)=$[${GROUP_COUNT(OUTBOUND_GROUP)}])

I’m asking if you’ve tried:

exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,al,u)=$[${GROUP_COUNT(OUT_${DIAL_TRUNK})}])

As it is being called upon and working in the FreePBX stock dialplan code. Have you tried checking against a known working variable that is being used for other checks?

Also, since you have limit of 12 channels, have you set up the inbound side of things to track incoming calls on this trunk so they will be added to the group count for this trunk?

11

Hi Tom,
Yes, I’ve tried exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,al,u)=$[${GROUP_COUNT(OUT_${DIAL_TRUNK})}]) and it wasn’t working.

I tried exten => tdial,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,al,u)=foo) but nothing was written on the file.

12

The active_sip_trunk.csv has both the correct owner/group (asterisk:asterisk) and the proper permissions to be writable?

13

this is what I have so far…

[root@pbx ~]# ls -al /var/log/asterisk/active_sip_trunk.csv
-rw-rw-r-- 1 asterisk asterisk 1 Sep 14 15:53 /var/log/asterisk/active_sip_trunk.csv

Obviously, nothing is inside. :sweat_smile:

14

Ah, it started to log something when I throw the line in [app-blacklist-check] context.

Instead of using OUT_${DIAL_TRUNK}, I embedded the name used for Trunk name used for the setting, then I started to see the value zero.

exten => s,n,SET(FILE(/var/log/asterisk/active_sip_trunk.csv,,,al,u)=${EPOCH}, $[${GROUP_COUNT(SIP/Name_of_Trunk_Junction)}])

I think I have to evaluate when the call session is established, so perhaps after [app-blacklist-check] context, I have to follow the flow by looking at /var/log/asterisk/full log.

15

Hi Tom,

After try and error, I started to see numbers.
I needed to have SET(GROUP()=OUT_${DIAL_TRUNK}) before GROUP_COUNT() function.

And thank you so much for your advise, I noticed GROUP_COUNT($OUT_$(DIAL_TRUNK}) is counting “opened” channels and number varied at the time of the function was evaluated.

Instead of using FILE() function, I decided to use CDR() function in [macro-usr-callerid] context and stored counted number in userfield of CDR.

  • Inserted below three lines right above of exten =&gt; h,1,Macro(hangupcall,).

    ;2018-09-17: The number of concurrent SIP Trunk usage will be posted in Userfield of CDR
exten =&gt; s,n,SET(GROUP()=OUT_${DIAL_TRUNK}) 
exten =&gt; s,n,Set(CDR(userfield)=Active Trunk: $[${GROUP_COUNT(OUT_${DIAL_TRUNK})}])

[macro-user-callerid]
include => macro-user-callerid-custom
exten => lang-playback,1,GosubIf($[${DIALPLAN_EXISTS(macro-user-callerid,${CHANNEL(language)})}]?macro-user-callerid,${CHANNEL(language)},${ARG1}():macro-user-callerid,en,${ARG1}())
exten => lang-playback,n,Return()

exten => s,1,Set(TOUCH_MONITOR=${UNIQUEID})
... <snipped> ...

exten => s,n(cnum),Set(CDR(cnum)=${CALLERID(num)})
exten => s,n,Set(CHANNEL(language)=${MASTER_CHANNEL(CHANNEL(language))})
;2018-09-17: The number of concurrent SIP Trunk usage will be posted in Userfield of CDR
exten => s,n,SET(GROUP()=OUT_${DIAL_TRUNK})
exten => s,n,Set(CDR(userfield)=Active Trunk: $[${GROUP_COUNT(OUT_${DIAL_TRUNK})}])

exten => h,1,Macro(hangupcall,)

exten => en,1(hook_0),Playback(im-sorry&an-error-has-occurred&with&call-forwarding)
... <snipped> ...

It is not a perfect solution, but I can at least see something in relation to CDR data… (Maybe I should say “Active Channels” rather than “Active Trunk” …)

image

16

Finally, I ended up using SHELL function to actually count the trunk name appears in Peer column. :smile:

exten => s,n,Set(CDR(userfield)=Active Trunk: ${SHELL(asterisk -rx "sip show channels"| grep "<Trunk_name_appeared_in_Peer_column>" | wc -l)})

Reference:

17

The userfield is an actively used field by Asterisk for reporting in the CDR. You could end up having Asterisk overwrite what you want in that field during a call. You should use a custom field to avoid any confusion in the reporting down the road.

And always remember, CDRs are not real-time. They get written when calls are completed so you could look at the report and see one thing but not have an idea of how many active calls/channels are happening without looking at some real time data.

1 Like
18

Hi Tom,

Thank for the reply. I have been writing values in userfield for more than 2 years and so far never had issue, but I will look into how to place the value into custom field.

And I am aware CDR is not real-time, and I noticed the place I am embedding the CDR function is not capturing outgoing trunk number correctly, I need to look farther to place my code in. For my environment, we have a very few calls a day, it is more than enough to obtain the trunk usage when a new call session is started or ended so that I can have a rough idea about concurrent trunk usage. (initially I use crontab to pole the info, but it was a bit heavy lifting for PBX host…)

19

Perhaps you are looking for something like this:

channels

1 Like
20

Hi Hawkeye,

Ah, that is very fancy! Assuming it is RRDtool, is it easy to configure? (I have been interested, but I haven’t touched that yet… I should take a look. :wink:)

So far I placed a Set(CDR(userfield)~ command in [macro-user-callerid] and [macro-outbound-callerid] so that I have some “snapshot” of trunk usage in CDR.

image

This is good enough for my setup since my PBX is pretty quiet most of the day.