login | register
Fri 29 of Aug, 2008 [00:41 UTC]

voip-info.org

Discuss [28] History

Asterisk cdr mysql

Created by: lmadsen,Last modification on Fri 18 of Apr, 2008 [08:51 UTC] by lvl
Asterisk can store CDR records in a MySQL database, as an alternative to CSV text files and other database formats.

How to download cdr_mysql

Due to Mysql client libraries licensing, the Mysql billing application is no longer an integrated part of the Asterisk standard distribution. It is now located in the asterisk-addons CVS directory.

Follow the instructions on http://www.asterisk.org/index.php?menu=download for Subversion download

 # cd /usr/src
 # svn checkout http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2

You must have mysql and mysql-devel packages installed.

  • In an RPM-based Linux, you can check presence of MySQL like this:
    • rpm -qa | grep mysql
  • For debian or other dpkg-based systems, check like this:
    • dpkg -l mysql-server libmysqlclient*dev
  • In FreeBSD, you'll find MySQL in the ports library, /usr/ports/databases/mysql4-server


Compile

# cd asterisk-addons-1.2
# make clean
# make
# make install

Check that in make stage that there are no mysql.h errors, which mean you are missing mysql-devel package


root@localhost asterisk-addons-1.2# make
./mkdep -fPIC -I../asterisk -D_GNU_SOURCE `ls *.c`
app_addon_sql_mysql.c:23:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:38:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:39:20: error: errmsg.h: No such file or directory
res_config_mysql.c:53:19: error: mysql.h: No such file or directory
res_config_mysql.c:54:27: error: mysql_version.h: No such file or directory
res_config_mysql.c:55:20: error: errmsg.h: No such file or directory

If make fails due to complaining about a missing "asterisk.h" file you can either copy this file from your asterisk directory, or create a soft link ("ln -s ...") for /usr/src/asterisk that points to your asterisk source directory.

A sample configuration file, can be found on the cdr_mysql.conf page.

Copy the sample configuration file to /etc/asterisk/cdr_mysql.conf and edit it according to your requirements. Then edit your modules.conf to load cdr_addon_mysql.so and finally restart asterisk; before the restart you should, however, check that your cdr table has been created correctly and is accessible to the username and password you specified.

Table definitions for Asterisk cdr_mysql

This is the database definition you use to install in Mysql to support billing.

Create the database


mysql --user=root --password=password [-h dbhost]



CREATE DATABASE asterisk;

GRANT INSERT
  ON asterisk.*
  TO asterisk@localhost
  IDENTIFIED BY 'yourpassword';

USE asterisk;

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 '',
`userfield` varchar(255) NOT NULL default ''
);

ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default '';
ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );


Please note that the rights granted in GRANT above is the _least_ the asterisk user will need. To allow the user to do more than just add new data to the table, see the MySQL manual on the topic

for trunk version since 29 Dec 2007 must be:
GRANT INSERT, SELECT ...
because cdr_addon_mysql now do DESC 'cdr'; for check tables fields.

Hint: Copy and paste this SQL command into a text file, save it under an appropriate name, then execute the following command:

  mysql --user=username --password=password databasename < nameoftextfile

Voila! The table is created for you. (:biggrin:)

An interesting note: on versions prior to v1.2, the name of the table, 'cdr', is hard-coded into the mysql interface, so if the table is created under a different name, the mysql CDR backend will not work. In v1.2 the table name is configurable via the "table=" option in cdr_mysql.conf

Yet another note: It probably isn't a good idea to use the InnoDB engine. Asterisk keeps autocommit turned on by default; therefore, each INSERT query is its own transaction. This will cause the query to take significantly longer to execute. On my personal computer, the "clock time" penalty is about 4-7 times that of MyISAM on a default Ubuntu install. Please also note that the CDR table does not require the features that the InnoDB engine provides.

Storing the Unique ID

Q: It would appear that the "uniqueid" field is not being populated in the MySQL CDR DB. Is this an obsolete field or is a bug?

A: You need to define MYSQL_LOGUNIQUEID at compile time for it to use that field.

You have two options in /usr/src/asterisk-addons:
1. Either add CFLAGS+=-DMYSQL_LOGUNIQUEID to the Makefile.
Note that recently (around Asterisk 1.4.18 or before) this has changed to ASTCFLAGS+=-DMYSQL_LOGUNIQUEID
2. Or instead add a #define MYSQL_LOGUNIQUEID to the top of cdr_addon_mysql.c.

Finally perform the usual make clean, make, make install. Be sure to check the Makefile for the presence of this flag after having done a CVS update! You will most probably also want to index the uniqueid field in your cdr table to improve performance.

You will also have to add a `uniqueid` column in your mysql database after the `accountcode` column:

ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ''

after `accountcode`;

What would I need all this for? For example you are running an AGI script and would like to be able to related AGI data with the CDR table. The problem is that the AGI script will lose connection to the call as soon as the caller hangs up, so you'll need a way to find the correct cdr entry (that'll also be created only after the call has been completed).

If you get the following error while compiling cdr_mysql:


  /usr/bin/ld: cannot find -lz

Make sure you have the zlib-devel package installed.

See also



Comments

Comments Filter
222

333Importing from CSV records

by pscrane, Monday 18 of August, 2008 [04:57:47 UTC]
This is a reposting of information in the CDR CSV page, but it might prove useful here. I've found that if you want to naively import the csv data in to MySQL using:

echo "LOAD DATA LOCAL INFILE 'Master.csv' INTO TABLE cdr FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" | mysql asterisk;

Then the fields won't line up if you use the cdr table provided above, so here's a slightly modified version:

CREATE TABLE `cdr_csv` (
`accountcode` varchar(20) NOT NULL default '',
`src` varchar(80) NOT NULL default '',
`dst` varchar(80) NOT NULL default '',
`dcontext` varchar(80) NOT NULL default '',
`clid` 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 '',
`start` datetime NOT NULL default '0000-00-00 00:00:00',
`answer` datetime NOT NULL default '0000-00-00 00:00:00',
`end` datetime NOT NULL default '0000-00-00 00:00:00',
`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'
);

echo "LOAD DATA LOCAL INFILE 'Master.csv' INTO TABLE cdr_csv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" | mysql asterisk;
222

333configuration of database cdr for phpmyadmin

by ismael, Thursday 08 of November, 2007 [09:12:47 UTC]
Hi, I need help with asterisk.
Specifically, I need to know the meaning of the column of the database phpmyadmin.
That is when I seek the calls that were made from four per day example, I get calls and one of the fields in the database 'cdr' is called 'diposition' and 'amaflags'. Well this is the question, when I place a call to my mobile for example from the extension 801 I cuelgo the call, that is, I have not responded yet in the table of my database this field as I get 'ANSWER '(and I have not off-hook).
The other issue is the field amaflags not what it means. And it happens it is always 3, and if they are not seconds or they are.

Thanks and a greeting Ismael Perez.
222

333configuración base datos cdr phpmyadmin

by ismael, Thursday 08 of November, 2007 [09:08:29 UTC]
Hola, necesito ayuda sobre asterisk.
Concretamente necesito saber el significado de la columna de la base de datos phpmyadmin.
Es decir cuando busco las llamadas que se han hecho a partir del dia cuatro por ejemplo, me aparecen las llamadas y uno de los campos de la base de datos 'cdr' se llama 'diposition' y 'amaflags'. Bueno pues esta es la cuestión, cuando realizo una llamada por ejemplo a mi movil desde la extensión 801 yo cuelgo la llamada, es decir, no la he respondido, sin embargo en la tabla de mi base de datos este campo me aparece como 'ANSWER'(y no he descolgado).
La otra cuestión es la del campo amaflags que no se lo que significa. y da la casualidad que siempre está en 3, y no se si son segundos o que son.

Gracias y un saludo de Ismael Pérez.
222

333RE : Error loading cdr_addon_mysql.so

by Kisecure, Monday 22 of October, 2007 [11:23:59 UTC]
Ok it's just a small error I think. In the package 1.4.4 of the asterisk-addons, and with asterisk 1.4.13.
The error was when loading cdr_addon_mysql : "Module 'cdr_addon_mysql.so' does not provide a description."
So near the end of cdr_addon_mysql.c in the function my_load_module() (line 457) there is :

res = ast_cdr_register(name, desc, mysql_log)

should be replaced by :

res = ast_cdr_register(name, ast_module_info->description, mysql_log)

Then the module could be loaded without the error.

222

333Error loading cdr_addon_mysql.so

by Kisecure, Monday 22 of October, 2007 [10:06:09 UTC]
Hi,

I've got a problem while loading the module cdr_addon_mysql. Every things was working fine, but i wanted to upgrade to the latest stable release of asterisk (1.4.13), so I updated the asterisk addons package too (version 1.4.4). Then asterisk try to load this module, in the console i can see this message :

Oct 22 11:57:45 WARNING28226: loader.c:602 inspect_module: Module 'cdr_addon_mysql.so' does not provide a description.

Oct 22 11:57:45 WARNING28226: loader.c:650 load_resource: Module 'cdr_addon_mysql.so' could not be loaded.


I tried also with the svn version of asterisk addons, same problem.
Can anybody help me ?

222

333Re: Transferring CDR data into mysql.. HELP!!!!!

by cizara, Wednesday 17 of October, 2007 [13:32:48 UTC]
It's all in this page. If you want to transfer the CDR into a mysql, you must install mysql and mysql-devel packages, download and compile the asterisk-addons, configure the /etc/asterisk/cdr_mysql.conf and add the module to the modules.conf file, create the db (below is the script) and nothing more. If you have any problems on any of this steps, give more details....
222

333Transferring CDR data into mysql.. HELP!!!!!

by satbir, Monday 15 of October, 2007 [07:02:43 UTC]
Hi,
i am new in asterisk and linux, i need to transfer the CDR into database... can anybody help me out by providing proper guidance....
222

333Transferring CDR data into mysql.. HELP!!!!!

by satbir, Monday 15 of October, 2007 [07:01:21 UTC]
Hi,
i am new in asterisk and linux, i need to transfer the CDR into database... can anybody help me out by providing proper guidance....
222

333Re: Failed to connect to mysql database, why?

by savaticus, Thursday 07 of December, 2006 [23:28:24 UTC]
You need to make sure that you can actually connect to that sql server from that box, my suggestion would be to have the mysql client installed on your box (should be anyway) and then attempt tto connect to that remote box using the credentials and setttings you plan to have * use.
222

333

by savaticus, Thursday 07 of December, 2006 [23:24:55 UTC]
.