MySQL CDR for Asterisk w/ ODBC

For a long time I never had a straight forward configuration for getting MySQL CDR setup on later versions of Asterisk(1.8+). This is to help anyone else that thinks this would be useful and also a reference for me next time I need to do another setup. This should be the same for 1.8+ but I’m just using 11 right now.

cdr_mysql has been deprecated in Asterisk 1.8. To get it working with MySQL you will need to use ODBC and cdr_adaptive_odbc module.

Steps are from an install using Ubuntu 12.01, Asterisk 11.9.0 and MySQL server located on a different machine.

Install Dependancies

unixodbc-dev — UnixODBC devel libraries
libmyodbc — ODBC MySQL Connector
libmysqlclient-dev — MySQL Client

# sudo apt-get install unixodbc-dev libmyodbc libmysqlclient-dev

Configure ODBC

Edit /etc/odbc.ini

[asterisk-cdr]
Description = MySQL Asterisk database
Trace = Off
TraceFile = stderr
Driver = MySQL
SERVER = MYSQL_HOSTNAME
USER = MYSQL_USER
PASSWORD = MYSQL_PASS
PORT = 3306
DATABASE = cdr

Edit /etc/odbcinst.ini

[MySQL]
Description = MySQL driver
Driver = libmyodbc.so
Setup = libodbcmyS.so
CPTimeout =
CPReuse =
UsageCount = 1

Install Asterisk

# cd /usr/src
 # wget http://downloads.asterisk.org/pub/telephony/asterisk/asterisk-11-current.tar.gz
 # tar xvfz asterisk-11.*.tar.gz
 # cd asterisk-11.*
 # ./configure

Verify in menuselect that func_odbc and res_odbc modules are going to be build

# make menuselect

Now install

# make
# make install

Configure Asterisk

Setup any other configuration needed like sip.conf or extensions.conf.

Edit /etc/asterisk/res_odbc.conf

[asterisk-cdr]
enabled => yes
dsn => asterisk-cdr
username => MYSQL_USER
password => MYSQL_PASS
pooling => no
limit => 1
pre-connect => yes

Edit /etc/asterisk/cdr_odbc.conf

[global]
dsn=asterisk-cdr
loguniqueid=yes
dispositionstring=yes
table=cdr ;"cdr" is default table name
usegmtime=no ; set to "yes" to log in GMT
hrtime=yes ;Enables microsecond accuracy with the billsec and duration fields

Setup MySQL

Make sure you have the user entered in the ODBC configs setup and with access from which ever location. Now create the cdr table, this exact create was suggest by a guy named Eugene on the Asterisk Wiki.

CREATE TABLE `cdr` (
 `id` INT(11) 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 '',
 `lastapp` VARCHAR(200) NOT NULL DEFAULT '',
 `lastdata` VARCHAR(200) NOT NULL DEFAULT '',
 `duration` FLOAT UNSIGNED NULL DEFAULT NULL,
 `billsec` FLOAT UNSIGNED NULL DEFAULT NULL,
 `disposition` ENUM('ANSWERED','BUSY','FAILED','NO ANSWER','CONGESTION') NULL DEFAULT NULL,
 `channel` VARCHAR(50) NULL DEFAULT NULL,
 `dstchannel` VARCHAR(50) NULL DEFAULT NULL,
 `amaflags` VARCHAR(50) NULL DEFAULT NULL,
 `accountcode` VARCHAR(20) NULL DEFAULT NULL,
 `uniqueid` VARCHAR(32) NOT NULL DEFAULT '',
 `userfield` FLOAT UNSIGNED NULL DEFAULT NULL,
 `answer` DATETIME NOT NULL,
 `end` DATETIME NOT NULL,
 PRIMARY KEY (`id`),
 INDEX `calldate` (`calldate`),
 INDEX `dst` (`dst`),
 INDEX `src` (`src`),
 INDEX `dcontext` (`dcontext`),
 INDEX `clid` (`clid`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB;

Testing it out

At this point if everything is setup correctly, starting asterisk and taking a call should generate a CDR. Start asterisk and give it a shot! Don’t want to be flying blind? Try out a couple steps to try.

Check isql for ODBC MySQL connection

With isql you can connect with the ODBC DSN you just created.

# isql asterisk-cdr -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT * FROM cdr;

Your output should look like the above with the Connected message, then the select should something like SQLRowCount returns 0. If you did not get that, go back and check the config and make sure you have the correct credentials and have permission from that machine. You can test this also by doing a normal # mysql -h HOST -u USER -p

Check Asterisk for ODBC modules and connection

Verify /usr/lib/asterisk/modules/res_odbc.so exists and should probably be 755 permissions.

In Asterisk, check module is accessible and shows a good connection:

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

Custom CDR parameters

With using cdr_adaptive_odbc, you can add additional parameters to your CDR logs without having to modify and configuration. Create a new column in the cdr table and within the call, perform Set(CDR(name)=value) with name being the column name. This is useful for keeping categories a call or being able to log call costs like per minute rate.