Bash Script – Log Concurrent Asterisk Calls to MySQL and Other Useful One-Liners

Here is a quick and dirty bash script I threw together today to log the concurrent calls for each of my long distance trunks in Asterisk to a MySQL database to be able to quickly analyze usage trends. Sure there is probably other open-source software out there that can do this and give pretty little graphs and what not (cdr-stats or maybe queue metrics come to mind), but where’s the fun in that? As I mentioned, the script is extremely primitive (just the bare minimum as I didn’t have much time to spend on it) and contains no error checking whatsoever but it could also be used as a pretty handy one-liner in bash.

Show all active SIP Calls on a single trunk

asterisk -x "core show channels verbose" | grep "^SIP/yourSIPTrunkName-"

Show concurrent number of SIP Calls on a single trunk

asterisk -x "core show channels verbose" | grep -c "^SIP/yourSIPTrunkName-"

Show all active DAHDI calls on channels 1-24

asterisk -x "core show channels verbose" | grep "^DAHDI/[1-9]-1\|^DAHDI/1[0-9]-1\|^DAHDI/2[0-4]-1"

Show concurrent number of DAHDI calls on channels 1-24

asterisk -x "core show channels verbose" | grep -c "^DAHDI/[1-9]-1\|^DAHDI/1[0-9]-1\|^DAHDI/2[0-4]-1"

For a helfpul realtime display of concurrent calls on a single trunk, incorporate the watch command

watch -n .5 'asterisk -x "core show channels verbose" | grep "^SIP/yourSIPTrunkName-"'

 
As a side note, feel free to use the script below if you like but you will have to write your own regular expressions to match the DAHDI channels used in your particular environment.
   
You will also need a MySQL database somewhere to put the data in. For this example, I named the table “trunkusage” and it has four columns.

Data types are as follows:
ID – int 11 unsigned auto-increment
time – datetime
trunk – varchar 50
activecalls – int 11 unsigned

To run as a daemon, download this and place in /etc/init.d and be sure to chmod +x it and issue update-rc.d to run at default run levels.

Download the script here, and place in /usr/local/bin.

#!/bin/bash
#
# Log Concurrent Calls to MySQL
# Author: Nathan Thomas
# 12/23/2014
#

DBUSER='yourMysqlUser';
DBPASS='yourMysqlPass';
DBHOST='yourMysqlServer';
DBNAME='yourMysqlDatabaseName';
TBLNAME='trunkusage';
TRUNKNAME=('mySIPProvider1' 'mySIPProvider2' 'dahdiCircuitProvider');
TRUNKTYPE=('SIP' 'SIP' 'DAHDI');
RUNTIME='5';  # how often to run in seconds

while true  # Infinite loop
do
        i=0
        for trunk in "${TRUNKNAME[@]}" ; do
                unset ACTIVECALLS;
                if [ "${TRUNKTYPE[$i]}" == "SIP" ] ; then
                        ACTIVECALLS=$( /usr/sbin/asterisk -x "core show channels verbose" | grep -c "^${TRUNKTYPE[$i]}/${trunk}-" ) > /dev/null 2>&1;
                        mysql -u ${DBUSER} -p${DBPASS} -h ${DBHOST} ${DBNAME} -e "INSERT INTO ${TBLNAME} (time,trunk,activecalls) VALUES (NOW(), '${trunk}', '${ACTIVECALLS}');" > /dev/null 2>&1;
                        ((i++));
                elif [ "${TRUNKTYPE[$i]}" == "DAHDI" ] ; then
                        # Regex pattern for channels 1-24
                        ACTIVECALLS=$( /usr/sbin/asterisk -x "core show channels verbose" | grep -c "^${TRUNKTYPE[$i]}/[1-9]-1\|^${TRUNKTYPE[$i]}/1[0-9]-1\|^${TRUNKTYPE[$i]}/2[0-4]-1" ) > /dev/null 2>&1;
                        mysql -u ${DBUSER} -p${DBPASS} -h ${DBHOST} ${DBNAME} -e "INSERT INTO ${TBLNAME} (time,trunk,activecalls) VALUES (NOW(), '${trunk}', '${ACTIVECALLS}');" > /dev/null 2>&1;
                        ((i++));
                fi
        done
        sleep ${RUNTIME};
done