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.
unixodbc-dev – UnixODBC devel libraries
libmyodbc – ODBC MySQL Connector
libmysqlclient-dev – MySQL Client
# sudo apt-get install unixodbc-dev libmyodbc libmysqlclient-dev
[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
[MySQL] Description = MySQL driver Driver = libmyodbc.so Setup = libodbcmyS.so CPTimeout = CPReuse = UsageCount = 1
# 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
# make # make install
Setup any other configuration needed like sip.conf or extensions.conf.
[asterisk-cdr] enabled => yes dsn => asterisk-cdr username => MYSQL_USER password => MYSQL_PASS pooling => no limit => 1 pre-connect => yes
[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
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.