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

it’s an integral part of Asterisk 1.8 and higher

You must have mysql and mysql-devel packages installed.

• In an RPM-based Linux, you can check the 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

it’s like standard Asterisk compilation

1. ./configure
2. make menuselect
3. make
4. make install

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

[[email protected] asterisk-addons-1.6.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 [email protected] IDENTIFIED BY ‘yourpassword’; USE asterisk; CREATE TABLE `bit_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 ”, `uniqueid` VARCHAR(32) NOT NULL default ”, `linkedid` VARCHAR(32) NOT NULL default ”, `sequence` VARCHAR(32) NOT NULL default ”, `peeraccount` VARCHAR(32) NOT NULL default ” ); ALTER TABLE `bit_cdr` ADD INDEX ( `calldate` ); ALTER TABLE `bit_cdr` ADD INDEX ( `dst` ); ALTER TABLE `bit_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.

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.

Extending CDR

MySQL to supports adaptive columns natively in Asterisk 1.6

Dec. 2007: While reading the SVN commit logs I noticed that tilghman has committed a change to allow MySQL to write to arbitrary column names. This has been present in the ODBC driver for some time but not in the MySQL driver. It is currently only available in trunk (what will become 1.6) but hopefully, someone will write a backport. (Might want to start here: Bug 11642).

cdr_addon_mysql

• Module has been completely revamped, with some options in the config file no longer used and an additional new section called [aliases]. Please read the sample config to familiarize yourself with these changes.
• Module now permits arbitrary columns to be created and populated, just like cdr_adaptive_odbc, simply by adding the column to the table and defining the corresponding CDR() variable. Aliases may be used to define relationships between differently-named variables and columns (such as the calldate column and the start CDR variable).
• The calldate column, as defined in the sample configuration, uses the start time of the call, as opposed to the old behavior of using the end time. However, you may get the old behavior for calldate, simply by changing the alias to “end => calldate”.
• Standard columns may also be excluded now, simply by dropping the column name from the table, renaming the column, or aliasing the cdrname from a nonexistent variable (e.g. foo => amaflags).

[some_context] ;dial trunk exten => _X.,1,Dial(SIP/trunk/${EXTEN}) ;exten h must be in same context! exten => h,1,noop(“extended CDR”) exten => h,n,set(CDR(hangupcause)=${HANGUPCAUSE}) ; hangupcause exten => h,n,set(CDR(peerip)=${CHANNEL(peerip)}) ; like 10.0.0.5 if behind nat exten => h,n,set(CDR(recvip)=${CHANNEL(recvip)}) ; like 194.79.52.192 – public ip exten => h,n,set(CDR(from)=${CHANNEL(from)}) ; like sip:[email protected] exten => h,n,set(CDR(uri)=${CHANNEL(uri)}) ; like sip:[email protected] exten => h,n,set(CDR(useragent)=${CHANNEL(useragent)}) ; useragent like Aastra_57i exten => h,n,set(CDR(codec1)=${CHANNEL(audioreadformat)}) ; codec * exten => h,n,set(CDR(codec2)=${CHANNEL(audiowriteformat)}) ; exten => h,n,set(CDR(llp)=${CHANNEL(rtpqos,audio,local_lostpackets)}) ; lost packets by local end ** exten => h,n,set(CDR(rlp)=${CHANNEL(rtpqos,audio,remote_lostpackets)}) ; lost packets by remote end exten => h,n,set(CDR(ljitt)=${CHANNEL(rtpqos,audio,local_jitter)}) ; the same for jitter exten => h,n,set(CDR(rjitt)=${CHANNEL(rtpqos,audio,remote_jitter)})

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 `bit_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 relate 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. In fact Uniqueid has never been guaranteed to be unique across all CDRs: It is only unique across a channel, and a channel may have multiple CDRs.

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

Source