MySQL y Asterisk CDR

Realizaremos una pequeña descripción de como conectar mediante MySQL diferentes servidores Asterisk para que guarden los registros de las llamadas realizadas. En el ejemplo que se mencionara a continuación se tienen siete equipos Asterisk funcionando cada uno de ellos con tarjetas analógicas y/o digitales. En el diagrama que se muestra a continuación, se puede observar que estos Asterisk están conectados mediante una topología de estrella por así decirlo, cada servidor esta conectado al Asterisk principal mediante un túnel UDP (VTUN), de esta manera puede haber comunicación entre todos ellos pasando por el servidor central mediante trunks SIP e IAX2.

mysqlasterisk

Cada servidor almacenara en una base de datos alojada en el servidor principal todos los registros de llamadas que se realizan, en este caso solo es de mi interés primordial las llamadas que se realizan a la PSTN por cada equipo, ya que las llamadas realizadas entre servidores (de extensión a extensión) son fácilmente registradas en el CDR del servidor principal, por lo cual tendremos una base de datos con ocho tablas.

Asumimos que ya contamos nuestro Asterisk funcionando en cada conmutador, y que en cada uno de ellos hemos instalado el modulo cdr_addon_mysql, presente en los addons de Asterisk, es preciso indicar que se utilizara el campo uniqueid, este de manera predeterminada no viene activo cuando trabajamos con MySQL al guardar el CDR, así que debemos agregar la siguiente linea en el archivo Makefile que se encuentra de la carpeta de asterisk-addons-1.4.7 y despues de esto compilar e instalar.

ASTCFLAGS+=-DMYSQL_LOGUNIQUEID

Mas información al respecto la podemos encontrar en el siguiente enlace: http://www.voip-info.org/wiki/view/Asterisk+cdr+mysql

Instalamos MySQL mediante aptitude y establecemos una contraseña para el usuario root de nuestro sistema de base de datos.

asteriskpbx:~# aptitude install mysql-server-5.0 libmysqlclient-dev

asteriskpbx:~# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> update mysql.user set Password = PASSWORD('password') where User = 'root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Verificamos el correcto acceso mediante la autenticación de root.

asteriskpbx:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> quit
Bye

En el archivo de configuración de MySQL comentamos la linea de bind-address para aceptar conexiones provenientes de otros hosts y aplicamos la configuración al reiniciar el servicio de MySQL.

asteriskpbx:~# vim /etc/mysql/my.cnf
 Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

asteriskpbx:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.

asteriskpbx:~# nmap localhost -p 3306,5432

Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2008-11-04 14:14 CST
Interesting ports on localhost (127.0.0.1):
PORT     STATE SERVICE
3306/tcp open  mysql
5432/tcp open  postgres

Nmap finished: 1 IP address (1 host up) scanned in 0.039 seconds

Vemos los puertos abiertos de MySQl y la de otra muy popular base de datos.

Creamos la base de datos “asteriskcdr” la cual almacenera todos los registros de los servidores Asterisk.
Servidor 0

asteriskpbx:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> CREATE DATABASE asteriskcdr;
Query OK, 1 row affected (0.00 sec)

Creamos el usuario asterisk para la base de datos asteriskcdr y establecemos una contraseña.

mysql> GRANT INSERT ON asteriskcdr.* TO asterisk@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> USE asteriskcdr;
Database changed
mysql> CREATE TABLE cdr (
    ->   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 ''
    -> );
Query OK, 0 rows affected (0.03 sec)

Daremos privilegios al usuario asterisk de acceder a nuestra base de datos desde la red 10.3.1.0/24 (la red privada clase A de las VPN) y del host 192.9.200.161/24 (mi laptop), cualquier conexión fuera de estas dos excepciones seran denegadas.

mysql> GRANT ALL PRIVILEGES ON asteriskcdr.* TO asterisk@'10.3.1.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON asteriskcdr.* TO asterisk@'192.9.200.161/255.255.255.255';
Query OK, 0 rows affected (0.00 sec)

Crearemos las tablas para nuestros servidores, sus usuario, contraseñas y los hosts desde donde se conectaran e ingresaran registros a sus tablas correspondientes.

Servidor 1

mysql> CREATE TABLE cdrponiente (
    ->   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 ''
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrponiente TO poniente@'10.3.1.65/255.255.255.255' IDENTIFIED BY 'poniente';
Query OK, 0 rows affected (0.00 sec)

Servidor 2

mysql> CREATE TABLE cdrcentro (
    ->   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 ''
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrcentro TO centro@'10.3.1.85/255.255.255.255' IDENTIFIED BY 'centro';
Query OK, 0 rows affected (0.00 sec)

Servidor 3

mysql> CREATE TABLE cdrqueretaro (
    ->   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 ''
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrqueretaro TO queretaro@'10.3.1.15/255.255.255.255' IDENTIFIED BY 'queretaro';
Query OK, 0 rows affected (0.00 sec)

Servidor 4

mysql> CREATE TABLE cdrleon (
    ->   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 ''
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrleon TO leon@'10.3.1.61/255.255.255.255' IDENTIFIED BY 'leon';
Query OK, 0 rows affected (0.00 sec)

Servidor 5

mysql> CREATE TABLE cdrtijuana (
    -> 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 ''
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrtijuana TO tijuana@'10.3.1.12/255.255.255.255' IDENTIFIED BY 'tijuana';
Query OK, 0 rows affected (0.03 sec)

Servidor 6

mysql> CREATE TABLE cdrmetepec (
    -> 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 ''
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrmetepec TO metepec@'10.3.1.37/255.255.255.255' IDENTIFIED BY 'metepec';
Query OK, 0 rows affected (0.03 sec)

Servidor 7

mysql> CREATE TABLE cdrpuebla (
    -> 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 ''
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT INSERT ON asteriskcdr.cdrpuebla TO puebla@'10.3.1.26/255.255.255.255' IDENTIFIED BY 'puebla';
Query OK, 0 rows affected (0.03 sec)

Vamos a entrar a uno de los servidores y nos conectaremos mediante el cliente de MySQL a la base de datos con el usuario correspondiente para verificar los parametros antes mencionados.

Servidor 5

[root@asterisktijuana ~]# mysql -u tijuana -h 10.3.1.1 asteriskcdr -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1891
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show tables;
+-----------------------+
| Tables_in_asteriskcdr |
+-----------------------+
| cdrtijuana            |
+-----------------------+
1 row in set (0.07 sec)

mysql> select current_user();
+-----------------------------------+
| current_user()                    |
+-----------------------------------+
| tijuana@10.3.1.12/255.255.255.255 |
+-----------------------------------+
1 row in set (0.07 sec)

Especificamos los parametros de conexión.

[root@asterisktijuana ~]# cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrtijuana
password=tijuana
user=tijuana
port=3306
sock=/tmp/mysql.sock
userfield=1

Reiniciamos el modulo para aplicar la configuración y verificamos que la conexión se haya realizado.

asterisktijuana*CLI> module show like cdr_addon_mysql.so
Module                         Description                              Use Count
cdr_addon_mysql.so             MySQL CDR Backend                        0
1 modules loadedCLI>

asterisktijuana*CLI> module unload cdr_addon_mysql.so
  == Unregistered 'mysql' CDR backend
asterisktijuana*CLI> module load cdr_addon_mysql.so
  == Parsing '/etc/asterisk/cdr_mysql.conf': Found
 Loaded cdr_addon_mysql.so => (MySQL CDR Backend)
asterisktijuana*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrtijuana for 8 seconds.
  Wrote 0 records since last restart.
asterisktijuana*CLI>

A partir de aqui la información se repite para cada servidor, cambiando la tabla, el usuario y la contraseña correspondiente, hago mención que al ejecutar cdr mysql status en la consola de Asterisk decidi mostrar el estado actual de la conexión mostrando los registros y el tiempo de conexión de cada servidor, ya que al iniciar por primera vez el modulo obviamente tendremos 0 registros y un tiempo corto de conexión, esto es con el fin de mostrar el estado real de un servidor en producción.

Servidor 4

[root@leonpbx ~]# cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrleon
password=leon
user=leon
port=3306
sock=/tmp/mysql.sock
userfield=1

leonpbx*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrleon for 14 days, 5 hours, 52 minutes, 42 seconds.
  Wrote 6359 records since last restart and 877 records since last reconnect.
leonpbx*CLI>

Servidor 3

[root@PBX ~]# cat /etc/asterisk/cdr_mysql.conf
[global]
hostname=10.3.1.1
dbname=asteriskcdr
user=queretaro
password=queretaro
table=cdrqueretaro
port=3306
sock=/var/lib/mysql/mysql.sock
userfield=1

PBX*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrqueretaro for 5 hours, 10 minutes, 8 seconds.
  Wrote 914 records since last restart and 93 records since last reconnect.
PBX*CLI>

Servidor 2

[root@asteriskcentro ~]# cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrcentro
password=centro
user=centro
port=3306
sock=/tmp/mysql.sock
userfield=1

asteriskcentro*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrcentro for 6 days, 2 hours, 11 minutes, 24 seconds.
  Wrote 4613 records since last restart and 1992 records since last reconnect.
asteriskcentro*CLI>

Servidor 1

[root@SanJeronimo ~]# vim /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrponiente
password=poniente
user=poniente
port=3306
sock=/tmp/mysql.sock
userfield=1

SanJeronimo*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrponiente for 1 days, 5 hours, 55 minutes, 2 seconds.
  Wrote 631 records since last restart and 40 records since last reconnect.
SanJeronimo*CLI>

Servidor 6

[root@tolucados ~]# cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrmetepec
password=metepec
user=metepec
port=3306
sock=/tmp/mysql.sock
userfield=1

tolucados*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrmetepec for 14 days, 5 hours, 38 minutes, 16 seconds.
  Wrote 714 records since last restart and 440 records since last reconnect.
tolucados*CLI>

Servidor 7

[root@asteriskpuebla ~]# cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=10.3.1.1
dbname=asteriskcdr
table=cdrpuebla
password=puebla
user=puebla
port=3306
sock=/tmp/mysql.sock
userfield=1

asteriskpuebla*CLI> cdr mysql status
Connected to asteriskcdr@10.3.1.1, port 3306 using table cdrpuebla for 14 days, 6 hours, 45 minutes, 39 seconds.
  Wrote 32413 records since last restart and 2219 records since last reconnect.
asteriskpuebla*CLI>

Servidor 0

gabriel@asteriskpbx:~$ cat /etc/asterisk/cdr_mysql.conf

[global]
hostname=localhost
dbname=asteriskcdr
table=cdr
password=password
user=asterisk
;port=3306
;sock=/tmp/mysql.sock
userfield=1

asteriskpbx*CLI> cdr mysql status
Connected to asteriskcdr@localhost using table cdr for 14 days, 2 hours, 28 minutes, 47 seconds.
  Wrote 2601 records since last restart.
asteriskpbx*CLI>

Este ultimo servidor, que es el principal almacena en su tabla todas las llamadas entre sus extensiones y las extensiones de los demas equipos, asi tambien el registrara las llamadas desde un servidor a otro, ya que el esta en medio de la llamada.

Vamos a verificar de manera sencilla todas las conexiones que tengamos a nuestra base de datos, usando la sentencia SHOW FULL PROCESSLIST

gabriel@asteriskpbx:~$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1897
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW FULL PROCESSLIST;
+------+-----------+-----------------+-------------+---------+-------+-------+-----------------------+
| Id   | User      | Host            | db          | Command | Time  | State | Info                  |
+------+-----------+-----------------+-------------+---------+-------+-------+-----------------------+
| 1871 | asterisk  | localhost       | asteriskcdr | Sleep   |  2029 |       | NULL                  |
| 1883 | centro    | 10.3.1.85:56963 | asteriskcdr | Sleep   |  8973 |       | NULL                  |
| 1884 | metepec   | 10.3.1.37:42568 | asteriskcdr | Sleep   | 27113 |       | NULL                  |
| 1885 | queretaro | 10.3.1.15:43297 | asteriskcdr | Sleep   |  1970 |       | NULL                  |
| 1886 | puebla    | 10.3.1.26:41327 | asteriskcdr | Sleep   |  6132 |       | NULL                  |
| 1892 | tijuana   | 10.3.1.12:33041 | asteriskcdr | Sleep   | 10447 |       | NULL                  |
| 1893 | leon      | 10.3.1.61:53451 | asteriskcdr | Sleep   |  5837 |       | NULL                  |
| 1899 | poniente  | 10.3.1.65:41878 | asteriskcdr | Sleep   |    22 |       | NULL                  |
| 1900 | root      | localhost       | NULL        | Query   |     0 | NULL  | SHOW FULL PROCESSLIST |
+------+-----------+-----------------+-------------+---------+-------+-------+-----------------------+
9 rows in set (0.00 sec)

La consulta nos arroja una pequeña tabla donde nos muestra el usuario, el host de donde se realiza la conexión la base de datos a la que se conecta, el tiempo de conexión y el proceso que esta realizando (de momento todos estan flojeando).

Vamos a ver unas cuantas consultas de ejemplo.

En esta consulta pedimos seleccionar 20 registros de los campos calldate, clid (callerid), dst, duration (convertidos los segundos a formato HH:MM:SS), channel (canal de origen), dstchannel (canal de destino) y uniqueid (identificador unico de la llamada) de la tabla cdr que cumplan con las condiciones de que se encuentren en el intervalo de fechas del 1 al 20 de Noviembre de 2009, que el campo billsec (duración real de la llamada) sea mayor a cero segundos y que la llamada haya sido contestada, ordenados por la fecha en orden descendente.

gabriel@asteriskpbx:~$ mysql -u root -h localhost asteriskcdr -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1909
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> select calldate, clid, dst, sec_to_time(duration), channel, dstchannel, uniqueid from cdr where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and billsec!=0 and disposition='ANSWERED' order by calldate desc limit 20;
+---------------------+------------------------------+------+-----------------------+--------------------------------+-------------------------------+-----------------+
| calldate            | clid                         | dst  | sec_to_time(duration) | channel                        | dstchannel                    | uniqueid        |
+---------------------+------------------------------+------+-----------------------+--------------------------------+-------------------------------+-----------------+
| 2009-11-20 20:53:17 | "Cabina 2" <103>             | 8282 | 00:01:14              | IAX2/192.9.200.246:4569-14099  | SIP/10.9.201.252-08246560     | 1258771997.5339 |
| 2009-11-20 18:54:52 | "D.Lugo" <8012>              | 102  | 00:03:34              | SIP/10.9.201.252-b683c3d0      | IAX2/192.9.200.246:4569-12593 | 1258764892.5337 |
| 2009-11-20 18:00:07 | "3406" <3406>                | 102  | 00:06:23              | SIP/10.9.201.252-b6831c50      | IAX2/192.9.200.246:4569-14177 | 1258761607.5333 |
| 2009-11-20 17:51:43 | "J.Ortiz" <8016>             | 9100 | 00:01:15              | SIP/10.9.201.252-b683ae40      | IAX2/10.3.1.22:4569-12341     | 1258761103.5331 |
| 2009-11-20 17:16:15 | "J.Ortiz" <8016>             | 2107 | 00:00:29              | SIP/10.9.201.252-b681b118      | SIP/ofcentro-08246560         | 1258758975.5329 |
| 2009-11-20 17:02:52 | "J.Ortiz" <8016>             | 2107 | 00:01:01              | SIP/10.9.201.252-b6834730      | SIP/ofcentro-08246560         | 1258758172.5327 |
| 2009-11-20 16:08:03 | "3406" <3406>                | 102  | 00:04:15              | SIP/10.9.201.252-b6805f20      | IAX2/192.9.200.246:4569-5114  | 1258754883.5325 |
| 2009-11-20 16:01:39 | "Satelite" <9010>            | 8222 | 00:01:10              | SIP/9010-b682f130              | SIP/10.9.201.252-08246560     | 1258754499.5323 |
| 2009-11-20 15:30:42 | "A.Zu&iga" <8216>            | 2105 | 00:00:26              | SIP/10.9.201.252-b6805f20      | SIP/ofcentro-08246560         | 1258752642.5321 |
| 2009-11-20 15:06:02 | "Toluca" <9110>              | 9021 | 00:04:14              | SIP/9110-b6812138              | SIP/9021-08246560             | 1258751162.5319 |
| 2009-11-20 14:57:00 | "O.Tavares" <8150>           | 9150 | 00:02:27              | SIP/10.9.201.252-b682f130      | IAX2/10.3.1.80:4569-3216      | 1258750620.5311 |
| 2009-11-20 14:55:07 | "David Alvarez Ochoa" <6002> | 8278 | 00:04:17              | SIP/insurgentestj-b681b118     | SIP/10.9.201.252-0822c258     | 1258750507.5307 |
| 2009-11-20 14:52:15 | "Chihuahua" <9100>           | 8315 | 00:00:43              | SIP/insurgenteschihu-b681c8f8  | SIP/10.9.201.252-08246560     | 1258750335.5299 |
| 2009-11-20 14:45:17 | "San Nicolas" <9154>         | 8155 | 00:01:24              | SIP/9154-b6805f20              | SIP/10.9.201.252-0822c258     | 1258749917.5295 |
| 2009-11-20 14:44:50 | "San Nicolas" <9154>         | 8216 | 00:00:17              | SIP/9154-b683c3d0              | SIP/10.9.201.252-0822c258     | 1258749889.5293 |
| 2009-11-20 14:43:32 | "Agentes" <2124>             | 8222 | 00:04:29              | IAX2/10.3.1.85:4569-15679      | SIP/10.9.201.252-08246560     | 1258749812.5289 |
| 2009-11-20 14:42:19 | "J.HERNANDEZ" <8209>         | 2602 | 00:00:14              | SIP/10.9.201.252-b681b118      | SIP/ofpuebla-08246560         | 1258749739.5283 |
| 2009-11-20 14:42:10 | "Cyntia Cano" <9116>         | 8274 | 00:00:37              | SIP/insurgentesmet-b68031d8    | SIP/10.9.201.252-0822c258     | 1258749730.5281 |
| 2009-11-20 14:38:15 | "Mauro Lopez" <2105>         | 8299 | 00:00:33              | SIP/insurgentescentro-b683d960 | SIP/10.9.201.252-0829cdf8     | 1258749495.5275 |
| 2009-11-20 14:38:13 | "D.Macias" <8247>            | 2604 | 00:01:34              | SIP/10.9.201.252-b6805f20      | SIP/ofpuebla-08246560         | 1258749493.5273 |
+---------------------+------------------------------+------+-----------------------+--------------------------------+-------------------------------+-----------------+
20 rows in set (0.00 sec)

En esta consulta pedimos seleccionar 10 registros de los campos calldate, src, dst, duration, channel, dstchannel y lastdata de la tabla cdrleon que cumplan con las condiciones de que se encuentren en el intervalo de fechas del 1 al 20 de Noviembre de 2009, que el campo billsec (duración real de la llamada) sea mayor a cero segundos, que la llamada haya sido contestada, donde los campos dstchannel y lastdata contengan registros que empiezen con los caracteres “Zap” ordenados por la fecha en orden ascendente. Esta consulta es para obtener las llamadas que se hacen a la red telefonica mundial.

mysql> select calldate, src, dst, sec_to_time(duration), channel, dstchannel, lastdata from cdrleon where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and dstchannel regexp "^Zap" and billsec!=0 and disposition='ANSWERED' and lastdata regexp "^Zap" order by calldate limit 10;
+---------------------+------+---------------+-----------------------+-------------------+------------+----------------------+
| calldate            | src  | dst           | sec_to_time(duration) | channel           | dstchannel | lastdata             |
+---------------------+------+---------------+-----------------------+-------------------+------------+----------------------+
| 2009-11-03 07:52:45 | 5203 | 018008009531  | 00:00:43              | SIP/5203-082f3508 | Zap/3-1    | ZAP/g3/018008009531  |
| 2009-11-03 07:59:07 | 5203 | 014927950124  | 00:00:09              | SIP/5203-08322380 | Zap/3-1    | ZAP/g3/014927950124  |
| 2009-11-03 07:59:56 | 5203 | 0454927950124 | 00:00:09              | SIP/5203-08322380 | Zap/3-1    | ZAP/g3/0454927950124 |
| 2009-11-03 08:03:56 | 5203 | 018008009531  | 00:00:40              | SIP/5203-08322380 | Zap/3-1    | ZAP/g3/018008009531  |
| 2009-11-03 08:04:56 | 5203 | 018008009531  | 00:00:10              | SIP/5203-08322380 | Zap/3-1    | ZAP/g3/018008009531  |
| 2009-11-03 08:05:27 | 5203 | 018008009531  | 00:00:53              | SIP/5203-082f3508 | Zap/3-1    | ZAP/g3/018008009531  |
| 2009-11-03 08:08:09 | 5203 | 018000912912  | 00:00:23              | SIP/5203-08322380 | Zap/3-1    | ZAP/g3/018000912912  |
| 2009-11-03 08:09:04 | 5203 | 018000912912  | 00:04:28              | SIP/5203-082f3508 | Zap/3-1    | ZAP/g3/018000912912  |
| 2009-11-03 09:09:09 | 5200 | 7180000       | 00:00:38              | SIP/5200-08305fe0 | Zap/1-1    | ZAP/g1/7180000|90|r  |
| 2009-11-03 09:20:40 | 5281 | 7139008       | 00:00:33              | SIP/5281-082fd568 | Zap/1-1    | ZAP/g1/7139008|90|r  |
+---------------------+------+---------------+-----------------------+-------------------+------------+----------------------+
10 rows in set (0.00 sec)

Aqui pedimos seleccionar otros 10 registros de los campos calldate, src, dst, duration, channel, dstchannel y lastdata de la tabla cdrleon que cumplan con las condiciones de que se encuentren en el intervalo de fechas del 1 al 20 de Noviembre de 2009, que el campo billsec (duración real de la llamada) sea mayor a cero segundos, que la llamada haya sido contestada, que el campo dstchannel contengan registros que empiezen con los caracteres “SIP” y el campo channel tenga registros que empiezen con los caracteres “Zap” ordenados por la fecha en orden descendente. Esta consulta es para obtener las llamadas que recibimos de la red telefonica mundial.

mysql> select calldate, src, dst, sec_to_time(duration), channel, dstchannel, lastdata from cdrleon where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and dstchannel regexp "^SIP" and billsec!=0 and disposition='ANSWERED' and channel regexp "^Zap" order by calldate desc limit 10;
+---------------------+------------+------+-----------------------+---------+-------------------+---------------+
| calldate            | src        | dst  | sec_to_time(duration) | channel | dstchannel        | lastdata      |
+---------------------+------------+------+-----------------------+---------+-------------------+---------------+
| 2009-11-20 12:34:58 | 4777187618 | 5212 | 00:00:49              | Zap/2-1 | SIP/5212-08326ff8 | SIP/5212|40|r |
| 2009-11-20 12:06:18 | 4777177975 | 5212 | 00:02:21              | Zap/2-1 | SIP/5212-082f05f8 | SIP/5212|40|r |
| 2009-11-20 11:45:47 | 4777127350 | 5204 | 00:17:25              | Zap/2-1 | SIP/5204-0832bbe0 | SIP/5204|40|r |
| 2009-11-20 11:40:33 | 4777118213 | 5204 | 00:03:36              | Zap/1-1 | SIP/5204-08317e30 | SIP/5204|40|r |
| 2009-11-20 11:16:45 |            | 5204 | 00:04:34              | Zap/2-1 | SIP/5204-0832a650 | SIP/5204|40|r |
| 2009-11-20 10:43:40 | 4777133468 | 5204 | 00:04:19              | Zap/2-1 | SIP/5204-0832a650 | SIP/5204|40|r |
| 2009-11-20 10:13:14 | 4747424743 | 5204 | 00:08:08              | Zap/1-1 | SIP/5204-083009e8 | SIP/5204|40|r |
| 2009-11-20 09:42:29 | 4772113309 | 5204 | 00:03:11              | Zap/2-1 | SIP/5204-0830ff88 | SIP/5204|40|r |
| 2009-11-20 09:39:18 | 4777185452 | s    | 00:01:15              | Zap/4-1 | SIP/5205-08313f00 | SIP/5205|90|r |
| 2009-11-20 09:18:05 | 5556362900 | s    | 00:03:24              | Zap/2-1 | SIP/5200-0832d170 | SIP/5200      |
+---------------------+------------+------+-----------------------+---------+-------------------+---------------+
10 rows in set (0.00 sec)

Para el caso anterior, una manera mas sencilla es ayudarnos de un contexto de llamadas entrantes por lo que en la consulta especificamos que solo queremos registros del contexto incoming almacenado en el campo dcontext. “Estupido por que la linea no tiene identificador de llamadas, pero es la idea =)”.

mysql> select calldate, clid, dst, channel, dstchannel, dcontext, lastdata from cdrponiente where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and billsec!=0 and disposition='ANSWERED' and dcontext='incoming' order by calldate desc limit 10;
+---------------------+------+-----+---------+-------------------+----------+----------+
| calldate            | clid | dst | channel | dstchannel        | dcontext | lastdata |
+---------------------+------+-----+---------+-------------------+----------+----------+
| 2009-11-20 12:39:59 |      | s   | Zap/3-1 | SIP/9201-b7b48ac8 | incoming | SIP/9200 |
| 2009-11-20 12:11:44 |      | s   | Zap/1-1 | SIP/9202-b7b48ac8 | incoming | SIP/9200 |
| 2009-11-20 11:25:48 |      | s   | Zap/2-1 | SIP/9200-08a289e8 | incoming | SIP/9200 |
| 2009-11-20 09:44:18 |      | s   | Zap/3-1 | SIP/9201-b7b48ac8 | incoming | SIP/9200 |
| 2009-11-19 18:07:59 |      | s   | Zap/1-1 | SIP/9201-b7b48ac8 | incoming | SIP/9200 |
| 2009-11-19 17:12:56 |      | s   | Zap/2-1 | SIP/9200-08a289e8 | incoming | SIP/9200 |
| 2009-11-19 17:01:14 |      | s   | Zap/1-1 | SIP/9200-08a289e8 | incoming | SIP/9200 |
| 2009-11-19 16:59:25 |      | s   | Zap/2-1 | SIP/9207-b7b48ac8 | incoming | SIP/9200 |
| 2009-11-19 15:55:40 |      | s   | Zap/2-1 | SIP/9201-b7b4b6e8 | incoming | SIP/9200 |
| 2009-11-19 15:40:24 |      | s   | Zap/3-1 | SIP/9202-b7b48ac8 | incoming | SIP/9200 |
+---------------------+------+-----+---------+-------------------+----------+----------+
10 rows in set (0.03 sec)

Para el caso anterior, una manera mas sencilla es ayudarnos de un contexto de llamadas entrantes por lo que en la consulta especificamos que solo queremos registros del contexto especifico almacenado en el campo dcontext.

mysql> select calldate, clid, dst, channel, dstchannel, dcontext, lastdata, uniqueid from cdrleon where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and billsec!=0 and disposition='ANSWERED' and dcontext='siniestros' and channel regexp "^Zap" order by uniqueid desc limit 10;
+---------------------+------------+------+---------+-------------------+------------+---------------+-----------------+
| calldate            | clid       | dst  | channel | dstchannel        | dcontext   | lastdata      | uniqueid        |
+---------------------+------------+------+---------+-------------------+------------+---------------+-----------------+
| 2009-11-20 09:39:18 | 4777185452 | s    | Zap/4-1 | SIP/5205-08313f00 | siniestros | SIP/5205|90|r | 1258731558.9112 |
| 2009-11-20 08:48:06 |            | 5212 | Zap/3-1 | SIP/5212-0832a650 | siniestros | SIP/5212|40|r | 1258728486.9095 |
| 2009-11-19 12:08:35 | 4813814411 | 5205 | Zap/4-1 | SIP/5205-0832e580 | siniestros | SIP/5205|40|r | 1258654115.9006 |
| 2009-11-18 20:31:44 | 5553289921 | s    | Zap/4-1 | SIP/5203-08326ff8 | siniestros | SIP/5203|90|r | 1258597904.8947 |
| 2009-11-18 08:24:54 |            | s    | Zap/4-1 | SIP/5203-08333be8 | siniestros | SIP/5203|90|r | 1258554294.8771 |
| 2009-11-17 16:14:40 |            | s    | Zap/4-1 | SIP/5200-083009e8 | siniestros | SIP/5203|90|r | 1258496080.8738 |
| 2009-11-17 10:44:40 |            | s    | Zap/4-1 | SIP/5200-0831ac58 | siniestros | SIP/5203|90|r | 1258476280.8698 |
| 2009-11-17 08:33:51 | 3336690670 | s    | Zap/4-1 | SIP/5203-082ff5f0 | siniestros | SIP/5203|90|r | 1258468431.8649 |
| 2009-11-13 12:19:45 |            | s    | Zap/4-1 | SIP/5200-083150a0 | siniestros | SIP/5203|90|r | 1258136385.8594 |
| 2009-11-13 11:21:49 | 4777180000 | s    | Zap/4-1 | SIP/5200-08313b10 | siniestros | SIP/5203|90|r | 1258132909.8580 |
+---------------------+------------+------+---------+-------------------+------------+---------------+-----------------+
10 rows in set (0.07 sec)

Una consulta sencilla para saber los ultimos cinco registros en los que alguien hizo llamadas a celular LD.

mysql> select calldate, src, dst from cdrqueretaro where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and billsec!=0 and disposition='ANSWERED' and dst regexp "^045" order by uniqueid desc limit 5;
+---------------------+------+---------------+
| calldate            | src  | dst           |
+---------------------+------+---------------+
| 2009-11-19 12:09:15 | 9078 | 0455527659873 |
| 2009-11-19 11:59:08 | 9078 | 0455554730698 |
| 2009-11-19 11:11:35 | 9078 | 0455554730698 |
| 2009-11-19 11:06:56 | 9078 | 0455554730698 |
| 2009-11-18 14:36:22 | 9077 | 0455527659873 |
+---------------------+------+---------------+
5 rows in set (0.00 sec)

Aqui una consulta donde se puede apreciar la utilidad del campo uniqueid, las llamadas a celular normalmente son restringidas, por lo que para realizarlas se amerita una clave, si utilizamos disa e introducimos la clave correcta damos la posibilidad de entrar a otro contexto que nos permita realizar estas llamadas y la forma en que esto se aprecia es que los registros que tengan el mismo uniqueid son una sola llamada en realidad. En este caso la persona de la extensión 9078 marca 11 para poder realizar una llamada que su contexto natural no le permite, se le pedira una clave y si esta es correcta se le permitira marcar el numero deseado, es por eso que vemos en la tabla el uniqueid asignado a cada etapa de la llamada.

mysql> select calldate, src, dst, dcontext, uniqueid from cdrqueretaro where calldate between '2009-11-01 00:00:00' and '2009-11-20 23:59:59' and billsec!=0 and disposition='ANSWERED' and src='9078' order by uniqueid desc limit 10;
+---------------------+------+---------------+------------+-----------------+
| calldate            | src  | dst           | dcontext   | uniqueid        |
+---------------------+------+---------------+------------+-----------------+
| 2009-11-20 15:40:16 | 9078 | 001929001     | agentes    | 1258753216.1398 |
| 2009-11-20 15:39:03 | 9078 | 001929018     | agentes    | 1258753143.1385 |
| 2009-11-20 14:37:24 | 9078 | 0444421715004 | ventasdisa | 1258749426.1294 |
| 2009-11-20 14:37:06 | 9078 | 11            | agentes    | 1258749426.1294 |
| 2009-11-20 13:35:12 | 9078 | 11            | agentes    | 1258745712.1274 |
| 2009-11-20 13:35:34 | 9078 | 0444421715004 | ventasdisa | 1258745712.1274 |
| 2009-11-20 13:34:09 | 9078 | 11            | agentes    | 1258745649.1272 |
| 2009-11-20 13:34:30 | 9078 | 0444421441096 | ventasdisa | 1258745649.1272 |
| 2009-11-20 13:33:41 | 9078 | 11            | agentes    | 1258745621.1271 |
| 2009-11-20 13:33:11 | 9078 | 11            | agentes    | 1258745591.1270 |
+---------------------+------+---------------+------------+-----------------+
10 rows in set (0.00 sec)

Por ultimo, si quisieramos exportar a un archivo separado por comas, los registros de todo el mes de Agosto lo hariamos de la siguiente forma.

mysql> select calldate, src, dst, sec_to_time(duration), channel, dstchannel, dcontext, duration, uniqueid into outfile '/tmp/cdragosto80.csv' fields terminated by ',' from cdr where calldate between '2009-08-01 00:00:00' and '2009-08-31 23:59:59' and billsec!=0 and disposition='ANSWERED'  order by calldate;

Por ultimo hay herramientas graficas para realizar todo tipo de consultas como por ejemplo: Asterisk-Stat

5 thoughts on “MySQL y Asterisk CDR

    1. Al servidor con Asterisk se le tiene que especificar en que host esta la base de datos, el otro servidor con MySQL tiene que aceptar las conexiones remotas. Si te fijas en el articulo esto se hace, ya que los servidores mas pequeños con Asterisk guardan sus registros en la base de datos del otro servidor.

      Saludos.

      Like

  1. Gracias Gabriel, por contestar, creo que no he sido claro, o me enrede solo, quiero tener el servidor asterisk, que grabe local y que a su vez grabe en otro servidor de mysql, el cual no tendra asterisk. He leido y se podria por mysql por cluster, pero tengo entendido que desde asterisk podriamos.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: