MySQL CDR for Asterisk w/ ODBC

Christopher Mendes
Jun 28, 2014 · 3 min read

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

# 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

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

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

Check isql for ODBC MySQL connection

# 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

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

cmendes0101

A place to write stuff

Christopher Mendes

Written by

cmendes0101

A place to write stuff