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.

Another way to resolve the missing “asterisk.h” file is to run the ./configure with option “–with-asterisk=” like
./configure –with-asterisk=MyAsteriskSourceDir

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).

Attention! The uniqueid field is not guaranteed to be unique across the different CDR entries, even though the name suggests exactly that.

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.