New web-interface for statistics and listen to the calls for IP PBX Asterisk

the Idea of writing web-interface for statistics and listen to the calls for IP PBX Asterisk is not left me for several years now. The solutions found in Internet, are not satisfied on certain criteria — somewhere lacked functionality, some of them are not pleasing to the eye.

And so, armed with technology stack and riding warhorse provided by ServerClub I went.

The result of my journey has become a new interface, with charts, graphs and the ability to download and listen to the calls. I will not further bore you with words, here are a couple of screenshots:



And under the cut are waiting for you a video guide on the interface, necessary settings and detailed description of the available functionality.

the

Interface.

That post didn't look like a bed sheet from the screenshots, I made a little slideshow where you can see the interface.

Description. The already existing plans.

Currently implemented the following functionality:

Incoming calls:

the

the
• Report the Number of calls in the queue during the period (all/missed/answered/did not wait for answer)
the
• Chart — Received/missed calls
the
• Chart Is Adopted, the distribution operators
the
• Chart Is Unanswered, the distribution operators
the
• Report — Statistics operators. Who and how many are accepted/not accepted calls
the
• Report the Reason for separation (operator/customer)

the Report Calls. As on the date Received/Replied/Unreplied the

• Search entries in the database. Listen to and download recordings Razgovorov

Outgoing calls:

the

the
• Report — Total calls, missed/unanswered/busy(call failed), the total duration and distribution of calls by duration (see the trail. point).
the
• Chart — Distribution of calls by duration: up to 30C from 30C to 90C, 90C
the
• Chart — Number of calls, the distribution managers
the
• Report — distribution of calls by duration between managers/operators

the Report Calls. How many made calls to the date (we consider only answered*) the

• Search entries in the database. Listen to and download recordings Razgovorov

*reports are based only on outgoing calls in the world, i.e. internal calls between employees are not considered

Also available are several settings, which specifies whether Asterisk queue* and the path of the files call records in the server.
*implemented in settings, but there is no interface

In the nearest plans to add:

the

the
• the ability to group managers/operators and build reports by group
the
• the ability to create users and to distinguish between their rights to view reports on groups
the
• build reports if on your Asterisk using the queue
the
• searching, listening and downloading of records in the entire database, without filters "Incoming/Outgoing" (to see and internal calls)

the

Asterisk. Settings

To work with the described interface, you will need Asterisk version 1.8 and above. On the PBX must be configured to maintain tables cdr, cel and queue_log in MySQL database. If you have not already done so, then I will tell you how.

So I will give an example of the configuration of the Asterisk dialplan, for organizing saved recordings of conversations.

Custom Asterisk to work with MySQL

1. Install required packages (for example in Debian/Ubuntu)
the

aptitude  install  unixodbc-dev libmyodbc

2. Asterisk must be compiled with the following options

3. Forth edit a few config files

small hint, if the odbc connector does not clings

Caught on one of the systems the bug in which the connector somehow ceased to cling and Aster had fallen into a crust:

Core was generated by `asterisk-cvvvvvvvgd'.

Program terminated with signal 8, Arithmetic exception.

#0 0x00007ff4cc77a61b in sqlchar_as_sqlwchar () from /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so

decided that you have downloaded the latest version from the MySQL site — dev.mysql.com/downloads/file/?id=461779

unpacked libs in /usr/lib/x86_64-linux-gnu/odbc/

and slightly tweaked the config

/etc/odbcinst.ini

the

[MySQL] driver Descripti
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmy5S.so 
CPTimeout =
CPReuse =

/etc/asterisk/res_odbc.conf

the

[asterisk]
enabled => yes
dsn = > MySQL-asterisk
username => asterisk_user
password => 232d2edxse3e

cdr_adaptive_odbc.conf

the

[cdr_adaptive_connection]
connection=asterisk
table=cdr
alias start = > calldate
# raskomentiruyte, if you want to see the real room, onahich came calling, not the number of the operator queue
#alias dst => does_not_exist
#alias realdst => dst

/etc/odbc.ini

the

[MySQL-asterisk]
Description = MySQL Asterisk database
;Trace = Off
;TraceFile = stderr
Driver = MySQL
Server = localhost
User = asterisk_user
Password = 232d2edxse3e
;Port = 3306
Socket = /var/run/mysqld/mysqld.sock
Database = asterisk
Charset = utf8

/etc/odbcinst.ini

the

[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
CPTimeout =
CPReuse =

*for x64
the

[MySQL]
Description = MySQL driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
CPTimeout =
CPReuse =

in the end
cdr_mysql.conf

add
the

alias filename => filename

4. Create database and table in MYSQL cdr
the

mysql> create database asterisk;
mysql> use asterisk;
mysql> CREATE TABLE `cdr` ( `id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL DEFAULT ",
`src` varchar(80) NOT NULL DEFAULT ",
`dst` varchar(80) NOT NULL DEFAULT ",
`dcontext` varchar(80) NOT NULL DEFAULT ",
`channel` varchar(80) NOT NULL DEFAULT ",
`dstchannel` varchar(80) NOT NULL DEFAULT ",
`lastapp` varchar(80) NOT NULL DEFAULT ",
`lastdata` varchar(80) NOT NULL DEFAULT ",
`duration` int(11) NOT NULL DEFAULT '0',
`billsec` int(11) NOT NULL DEFAULT '0',
`disposition` varchar(45) NOT NULL DEFAULT ",
`amaflags` int(11) NOT NULL DEFAULT '0',
`accountcode` varchar(20) NOT NULL DEFAULT ",
`uniqueid` varchar(32) NOT NULL DEFAULT ",
`userfield` varchar(255) NOT NULL DEFAULT ",
`filename` varchar(255) NOT NULL DEFAULT ",
PRIMARY KEY (`id`),
KEY `calldate` (`calldate`),
KEY `accountcode` (`accountcode`),
KEY `uniqueid` (`uniqueid`),
KEY `dst` (`dst`),
KEY `src` (`src`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
mysql> grant all on asterisk.* to 'asterisk_user'@'localhost' identified by '232d2edxse3e';

5. Table cel
the

mysql>CREATE TABLE `cel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`eventtype` varchar(30) NOT NULL,
`eventtime` datetime NOT NULL,
`cid_name` varchar(80) NOT NULL,
`cid_num` varchar(80) NOT NULL,
`cid_ani` varchar(80) NOT NULL,
`cid_rdnis` varchar(80) NOT NULL,
`cid_dnid` varchar(80) NOT NULL,
`exten` varchar(80) NOT NULL,
`context` varchar(80) NOT NULL,
`channame` varchar(80) NOT NULL,
`src` varchar(80) DEFAULT NULL,
`dst` varchar(80) DEFAULT NULL,
`channel` varchar(80) DEFAULT NULL,
`dstchannel` varchar(80) DEFAULT NULL,
`appname` varchar(80) NOT NULL,
`appdata` varchar(80) NOT NULL,
`amaflags` int(11) NOT NULL,
`accountcode` varchar(20) NOT NULL,
`uniqueid` varchar(32) NOT NULL,
`linkedid` varchar(32) NOT NULL,
`peer` varchar(80) NOT NULL,
`userdeftype` varchar(255) NOT NULL,
`eventextra` varchar(255) DEFAULT NULL,
`userfield` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uniqueid_index` (`uniqueid`),
KEY `linkedid_index` (`linkedid`),
KEY `eventtime` (`eventtime`),
KEY `exten` (`exten`),
KEY `eventtype` (`eventtype`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

6. Now create a sign queue_log
the

mysql> CREATE TABLE IF NOT EXISTS `queue_log` (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
time timestamp NULL DEFAULT '0000-00-00 00:00:00',
callid varchar(32) NOT NULL default ",
queuename varchar(32) NOT NULL default ",
agent varchar(32) NOT NULL default ",
event varchar(32) NOT NULL default ",
data1 varchar(100) NOT NULL default ",
data2 varchar(100) NOT NULL default ",
data3 varchar(100) NOT NULL default ",
data4 varchar(100) NOT NULL default ",
data5 varchar(100) NOT NULL default ",
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> \q
Bye

6. Introduced in the /etc/asterisk/extconfig.conf

line
the

queue_log = > odbc,asterisk

7. Reload Asterisk and check the connection
the

*CLI > odbc show asterisk 
ODBC DSN Settings
-----------------
Name: asterisk
DSN: MySQL-asterisk
Last connection attempt: 1970-01-01 07:00:00
Pooled: No
Connected: Yes

8. It is also worth to call and check whether the data in the database

Dialplan Asterisk - excerpt from /etc/asterisk/extensions.ael

globals {
WAV=/var/calls; //Temporary directory WAV
MP3=/var/calls; //Where to upload mp3 files
RECORDING=1; // Write, 1 - enabled.
};

macro recording (calling,called) {
if ("${RECORDING}" = "1"){
Set(fname=${UNIQUEID}-${STRFTIME(${EPOCH},,%Y-%m-%d-%H_%M)}-${calling}-${called});
Set(datedir=${STRFTIME(${EPOCH},,%Y/%m/%d)});
System(mkdir -p ${MP3}/${datedir});
System(mkdir -p ${WAV}/${datedir});
Set(monopt=nice-n 19 /usr/bin/lame -b 32 --silent "${WAV}/${datedir}/${fname}.wav" "${MP3}/${datedir}/${fname}.mp3" &&rm-f "${WAV}/${fname}.wav" && chmod o+r "${MP3}/${datedir}/${fname}.mp3");
Set(CDR(filename)=${fname}.mp3);
Set(CDR(recordingfile)=${fname}.wav);
Set(CDR(realdst)=${called});
MixMonitor(${WAV}/${datedir}/${fname}.wav,b,${monopt});

};
};

You can find => {
&recording(${CALLERID(number)},${EXTEN});
Dial(SIP/83843${EXTEN}@multifon,180,tT);
HangUP();
} // end of you can find

Files of recorded conversations fall straight to
/var/calls

where have the following hierarchy
the

ls /var/calls/2016/ -l
total 24
drwxr-xr-x 19 asterisk asterisk 4096 May 31 10:10 05
drwxr-xr-x 30 asterisk asterisk 4096 Jun 30 10:02 06
drwxr-xr-x 31 asterisk asterisk 4096 Jul 31 10:18 07
drwxr-xr-x 31 asterisk asterisk 4096 Aug 31 09:00 08
drwxr-xr-x 26 asterisk asterisk 4096 Sep 26 09:51 09

the

Asterisk. Connection statistics interface

It is time to dispel some doubts, or confirm some guesses. Yes — at the moment the service is available on SAAS model i.e. on your PBX installs the client for synchronization of data bases and records.

(I don't think it is worth it to dwell — there, as usual), you need to log into your account at stat.vistep.ru, go to the settings and specify the path to the file record and click "Save". Then it will be available the link to download the script client.

To install the script you need to perform the following steps:

the 1. Install the server nodejs package Manager npm if they are not already installed (using yum or apt/aptitude/apt-get)

the 2. Set the pm2

the

npm install -g pm2

the 3. Create and navigate to the folder /opt/stat.vistep.EN

the

mkdir /opt/stat.vistep.EN
cd /opt/stat.vistep.EN

the 4. Put the file with the script in the folder that you created earlier step, and extract it

the

unzip skript_name.zip

the 5. to Edit the script, making changes to lines 393 — 397 (and 398 are optional, if you are familiar with regexp), namely

the

"dbhost":"localhost",
"dbuser":"asterisk_user",
"dbpassword":"232w2edxse3e",
"db":"asterisk", 
"timezone":"Asia/Novokuznetsk", // <--- time zone
"fileMask": /\.*/ // 

of 6. Start the script execution:

the

pm2 start stat.vistep.ru.js --name "ViStep.RU stat"

the 7. to Configure startup/shutdown script to sync with OS:

the

pm2 startup ubuntu #or centos, gentoo
pm2 save

With the settings at all. Have to wait for load data to the statistics server and start enjoying!

the

Terms of service

At the moment we offer 2 ways of cooperation on product Stat.ViStep.RU:

Local version for permanent use.
All updates will be available to You at no additional charge.

Cloud.
The updates included in the monthly fee.

Help installing/configuring the product free in both versions.

All the details you can learn contacting us at sales@vistep.ru

the

Conclusion

That ended my tale of the journey and its results. But only the tale, not the journey — it has only just begun. Believe me waiting for many more achievements, fun roads, and interesting quests.

For help in configuring Asterisk you are welcome to write to us at support@vistep.ru.
If the question on cooperation, terms of service, or any occasion, then wait for letters to sales@vistep.ru

Also all my contacts are in profile, and of course, I'll be happy to answer your questions in the comments.

Demo access:

support@vistep.ru
0jnoiLJNFDr4-3r2f4

For symmetry, let's finish. Thank you!

Article based on information from habrahabr.ru