FreePBX Configuring Asterisk for e-mail notifications of missed incoming calls in the queue

IP ATC Asterisk is a powerful IP telephony processor. And the FreePBX web-interface, created for Asterisk, greatly simplifies configuration and lowers the threshold for logging into the system.

If you can come up with some kind of task related to IP-telephony, then almost certainly it can be implemented in Asterisk. But be sure that you will need perseverance and endurance.



We faced the task of setting up e-mail notifications of missed calls. More precisely, to notify via e-mail of those cases when an incoming call has been queued, but no one (from the agents) has answered this incoming call.



Surprisingly, we did not find any regular tools to solve this problem in FreePBX. I’ll talk about how we solved this problem under the cut.



Foreword



Before solving the problem “head-on”, we certainly searched for information on the Internet, but did not find turnkey solutions (maybe they were looking poorly, but what can you do ...).



There are not as many work skills directly in Asterisk as we would like, so the solution offered here was not fully understood and was discarded.



I liked the solution proposed here , although it did not work. From here it was emphasized that work in Asterisk is necessary in the context of queues [ext-queues]. And since we work in Freepbx, we need to work in the configuration file “extensions_override_freepbx.conf”. We noticed that it is convenient to “catch missed calls” before the hangupcall event (end of a call).

After reading the discussion here , the idea came up that we need to filter the “Disposition” variable in the CDR for all agents in the queue. And after reading this information, quite specific steps have been taken to solve the problem.



What do we have:



There is FreePBX 13.0.197 that uses Asterisk 13.12.1. OS version SHMZ release 6.6 (Final). The distribution is based on CentOS.



Asterisk is configured with IVR (voice menu) scattering incoming calls into different Queues (queues). Agents (agents) are assigned to each queue, i.e., agents.



Theory



What is going on at Asterisk



When an incoming call arrives at Asterisk, that call goes to IVR. The caller makes a choice by pressing a specific number on the phone, and falls into a specific queue. After that, all free agents of the queue simultaneously receive a call.



In order to better understand what is happening at this moment and what happens next, turn to Report CDR (Fig. 1).





Fig. 1



When an incoming call fell into the queue, for all agents, the value of the variable "Disposition" became equal to "NO ANSWER" if the agents were not busy at that moment. The variable "Disposition" could take other values ​​(see https://asterisk-pbx.ru/wiki/asterisk/cf/cdr ), except for the value "ANSWERED". And at the moment when one of the agents answers an incoming call, the value of the “Disposition” variable of this agent becomes equal to “ANSWERED”.

From the Report CDR, you can notice that when the call is queued (in the App column, the value becomes “Queue”), then all events appear with the same “uniqueid” (System column).



CDR in brief



It is important to understand what a CDR is, and at what point in the CDR the data that we observe in the Report CDR is entered. The CDR, relative to the operating system, is the database into which Asterisk writes a detailed call report (see https://asterisk-pbx.ru/wiki/asterisk/cf/cdr ). In our case, this is a database called asteriskcdrdb, which is located in mysql. Empirically, we found that data about a call with a certain “uniqueid” is not entered into asteriskcdrdb immediately after the occurrence of any event, but after the hangupcall event (end of the call).



The principle of the created solution



Since we have more knowledge in bash than knowledge in Asterisk, the main idea is as follows. Before the hangupcall event, invoke the bash script. Pass 3 parameters to this script. The first parameter is “uniqueid”, to filter data received from the CDR. The second parameter is “CALLERID (num)” (the number of the caller) to know who to call back. The third parameter is “NODEST” (queue number), to which the call arrived, in order to know on what issue there was a call, and to whom to send an e-mail notification of a missed call.

The bash script should connect to the asteriskcdrdb database in mysql and take all the values ​​of the "Disposition" variable with a specific "uniqueid". From the obtained data, it is necessary to exclude the values: “NO ANSWER”, “BUSY”, “FAILED”, “UNKNOWN”. As a result, either “ANSWERED” will remain - they answered the incoming call, or nothing at all - the missed call.



Further, if the call was missed, the script should send an e-mail notification.

Looking ahead, I note an important point. Asterisk executes the commands sequentially, waiting for their execution (which is generally logical). And we will call the bash script before the hangupcall command is executed. Thus, at the moment the script is directly executed, the information about the “uniqueid” we are looking for will not be entered into the CDR yet. To solve this problem, we will call the bash script with the “&” parameter so that Asterisk immediately proceeds to the next step, that is, hangupcall. And inside the bash script, at the very beginning, we will set a small time delay to give time for Asterisk to enter the data with the “uniqueid” of interest to us in the CDR.



Practice



Before proceeding to configure Asterisk and create a bash script, you need to configure the sending of e-mail notifications. For this, we will use the postfix utility.



Postfix setup



We have a mail domain "lucky.ru" located in Yandex. We will configure postfix in smtp-client mode and will send letters from the asterisk@lucky.ru account.

The solution is taken from here: https://www.dmosk.ru/miniinstruktions.php?mini=postfix-over-yandex .



First install / update / check for packages:

yum install postfix yum install mailx yum install cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We will not overwrite the main postfix configuration file “/etc/postfix/main.cf”, but back it up:

 cp /etc/postfix/main.cf /etc/postfix/main.cf.sav
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We edit the file “/etc/postfix/main.cf” and bring it to the following form:

 nano /etc/postfix/main.cf ##################### relayhost = smtp_sasl_auth_enable = yes smtp_sasl_password_maps = hash:/etc/postfix/private/sasl_passwd smtp_sasl_security_options = noanonymous smtp_sasl_type = cyrus smtp_sasl_mechanism_filter = login smtp_sender_dependent_authentication = yes sender_dependent_relayhost_maps = hash:/etc/postfix/private/sender_relay smtp_generic_maps = hash:/etc/postfix/generic smtp_tls_CAfile = /etc/postfix/ca.pem smtp_use_tls = yes smtputf8_autodetect_classes = all #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Not every line in "/etc/postfix/main.cf" can be commented on. Comments in some lines are not determined by the parser and are passed to processing, and this leads to errors. It is better to refuse comments inside this file. You can experiment with this by running “tail -f / var / log / messages” in the next window.



I’ll mark the line “smtputf8_autodetect_classes = all”. This entry includes utf-8 by default, which allows you to use the Cyrillic alphabet both in the body of the message and in the subject line without additional manipulations (See http://www.postfix.org/SMTPUTF8_README.html ).



Create a directory for the configuration files:

 mkdir /etc/postfix/private
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We edit the file "/ etc / postfix / private / sender_relay". In it, you need to specify which smtp server you need to refer to when using our mail domain:

 nano /etc/postfix/private/sender_relay ##################### @lucky.ru smtp.yandex.ru #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We edit the file "/ etc / postfix / private / sasl_passwd". In it we will indicate the e-mail address that we will use to send letters, as well as the username and password for this account (we specify the username and password through a colon):

 nano /etc/postfix/private/sasl_passwd ##################### asterisk@lucky.ru asterisk@lucky.ru:password_asterisk #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Editing the file / etc / postfix / generic. In it we will write down the rules for replacing the outgoing address (see https://wiki.merionet.ru/ip-telephoniya/30/postfix-nastrojka-otpravki-pochty-v-asterisk/ ):

 nano /etc/postfix/generic ##################### root asterisk@lucky.ru root@localhost asterisk@lucky.ru root@localhost.localdomain asterisk@lucky.ru root@freepbx asterisk@lucky.ru root@freepbx.localdomain asterisk@lucky.ru root@asterisk asterisk@lucky.ru root@asterisk.localdomain asterisk@lucky.ru asterisk asterisk@lucky.ru asterisk@localhost asterisk@lucky.ru asterisk@localhost.localdomain asterisk@lucky.ru asterisk@freepbx asterisk@lucky.ru asterisk@freepbx.localdomain asterisk@lucky.ru asterisk@asterisk asterisk@lucky.ru asterisk@asterisk.localdomain asterisk@lucky.ru root@localdomain.localdomain asterisk@lucky.ru #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The initial outgoing address depends on the contents of “/ etc / hosts” and “/ etc / hostname”, as well as on the name of the user who will send the letter. That is, despite the fact that we use the smtp client and send letters from asterisk@lucky.ru, all the same, the postfix sender will initially substitute “something of his own” and this should be corrected with the rules from this configuration file.



Here is the contents of my / etc / hosts file:

 cat /etc/hosts ##################### 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 asterisk.localdomain 127.0.0.1 localhost.localdomain localhost ::1 asterisk localhost localhost6 #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

It is important that the server has any domain (the value after the period), because the mail utility “looks for” the domain name in “/ etc / hosts” and if it does not “find” it immediately, it will continue to do this for several more minutes and only then send a letter. That is, if the domain is not registered, then the letter will leave with a delay of several minutes.



I will list the contents of my “/ etc / hostname” file:

 cat /etc/hostname ##################### asterisk #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Next, you need to transfer the created configuration files to indexed databases, to do this, run the following command:

 postmap /etc/postfix/generic && postmap /etc/postfix/private/{sasl_passwd,sender_relay}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Next, we need to download and place the smtp.yandex.ru certificate on the server, for this we execute the following command:

 openssl s_client -starttls smtp -crlf -connect smtp.yandex.ru:25 > /etc/postfix/ca.pem
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

But after the technical information appears on the screen, the team will "continue to hang." Press Ctrl + C to abort it.



Now manually delete all the garbage from the resulting file and leave only the certificate. You should get something like this:

 nano /etc/postfix/ca.pem ##################### -----BEGIN CERTIFICATE----- MIIGazCCBVOgAwIBAgIQcUU9mJXW4OUs5Gf0JfLtsjANBgkqhkiG9w0BAQsFADBf ... nRG0DfdqYIuPGApFORYe -----END CERTIFICATE----- #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Finally, restart postfix:

 service postfix restart
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We send a test letter:

 echo "  " | mail -s " " admin@lucky.ru
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

admin@lucky.ru - destination address



This completes the posfix configuration.



Writing a bash script



Create a directory for storing a bash script (here someone likes it where):

 mkdir /home/asterisk/scripts
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Create a bash script file:

 touch /home/asterisk/scripts/noanswer.sh
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Give the script file the right to execute:

 chmod +x /home/asterisk/scripts/noanswer.sh
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

If there are doubts about the rights to the file, then during debugging you can give full access to the file. But it is "not safe."

 chmod 777 /home/asterisk/scripts/noanswer.sh
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The text of the bash script:

 nano /home/asterisk/scripts/noanswer.sh ##################### #!/bin/bash sleep 7 res_sql="SELECT disposition FROM cdr WHERE uniqueid = '$1'" answer=`mysql -u freepbxuser -pPassword_freepbxuser -D asteriskcdrdb -B -N -e "$res_sql" | grep -E -v "NO ANSWER|BUSY|FAILED|UNKNOWN" | head -n 1` error_kod=0 if [ "$answer" != "ANSWERED" ] then case $3 in 68800) address="big_boss@lucky.ru" subject="  " ;; 63100) address="debian@lucky.ru" subject="  linux debian" ;; 63200) address="windows@lucky.ru" subject="  windows" ;; 63300) address="freebsd@lucky.ru" subject="  freebsd" ;; 63400) address="ubuntu@lucky.ru" subject="  linux ubuntu" ;; 63500) address="centos@lucky.ru" subject="  linux centos" ;; *) address="admin@lucky.ru" error_kod=1 ;; esac case $error_kod in 0) echo "    $2,  $subject." | mail -s "   $2" $address echo "   $address   $2,  $subject. uid=$1" | mail -s "   $2" admin@lucky.ru ;; 1) echo "   $2.  . uid=$1" | mail -s "   $2" admin@lucky.ru ;; esac fi #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

A brief analysis of the script:

"Sleep 7":



This is the same time delay that I wrote about earlier. We have a delay of 7 seconds. Although, I think, one second is quite enough.

 «res_sql="SELECT disposition FROM cdr WHERE uniqueid = '$1'"»:
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The query in mysql we put into a separate variable for convenience.



Next, we make a request in mysql and filter the resulting output. We remove all options except "ANSWERED", if any. If there are several “ANSWERED” values, then only one should be left. At the end, in the variable “answer” we get either “ANSWERED” or “”.

If the value of the variable “answer” is not equal to “ANSWERED”, then this is a missed call. Depending on the queue number, using the case operator we will set the address to whom it is necessary to send an e-mail notification, and what to write in this message (variable part of the message).



The following is an option when the queue is set in Asterisk, but not described in the script. In this case, admin@lucky.ru will receive a letter stating that the queue is not known to the script.



If the queue is described, then a letter of destination and a duplicate letter will be sent to admin@lucky.ru indicating “uniqueid”, so that you can track events on this call, if necessary.



This ends the script.



I note that to connect to mysql we used the username and password, which we recognized in advance. In FreePBX, in order to find out the Asterisk user login in mysql, run the following command:

 cat /etc/amportal.conf | grep AMPDBUSER
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

And in order to find out the password of the Asterisk user in mysql, run the following command:

 cat /etc/amportal.conf | grep AMPDBPASS
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Configure Asterisk



We use FreePBX. FreePBX has different types of configuration files (see https://asterisk-pbx.ru/wiki/freepbx/files ), some of them are overwritten by FreePBX upon reboot, and some are not overwritten (they are called custom), as they are specially designed for the user.



We will work with the configuration file “extensions_override_freepbx.conf”, since it is of type custom.



First, make sure that the file “extensions_override_freepbx.conf” is connected in the file “/etc/asterisk/extensions.conf”. To do this, run the following command:

 cat /etc/asterisk/extensions.conf | grep extensions_override_freepbx.conf ##################### #include extensions_override_freepbx.conf #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We edit the file “/etc/asterisk/extensions_override_freepbx.conf” and bring it to the following form:

 nano /etc/asterisk/extensions_override_freepbx.conf ##################### [ext-queues] exten => h,1,System(/home/asterisk/scripts/noanswer.sh ${CDR(uniqueid)} ${CALLERID(num)} ${NODEST} &) exten => h,2,Macro(hangupcall,) #####################
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

As I wrote earlier, the “&” symbol at the end is required. Since we will work in a bash script with CDR data directly from the mysql database, and this data is entered into mysql only after executing “exten => h, 2, Macro (hangupcall,)”, we need not to wait for the completion of the bash script , and proceed to the next step in Asterisk. And the bash script itself must contain a time delay before executing its main part.



In order for the changes in the configuration file “/etc/asterisk/extensions_override_freepbx.conf” to take effect, you need to restart the Asterisk kernel with the following command:

 /usr/sbin/asterisk -rx "core restart now"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This needs to be done after the bash script has been created.



Conclusion



This is probably the 1001st way to “catch missed calls” in Asterisk. Share in the comments how you solve this problem. And what, in your opinion, can be improved / redone / optimized. We will be grateful for the constructive ideas.