Ubuntu 14.04/Asterisk 13: CDR Reporting to MySQL

On Linux, I am familiar with MySQL, and for me it’s the easiest to get going. Asterisk used to include support for MySQL directly (and the config is still there, but not compiled into Asterisk by default anymore), but has since moved to a ODBC structure which offloads the database handling, making it database server agnostic. This is good in the way that it makes writing reports a lot more flexible, but also bad because you have to learn how to configure ODBC also. It’s not as simple as configuring the single ini anymore.

For a few reasons, it is suggested to install/use a MySQL server on another machine. It is safer and more space could be available if there isn’t enough on your Asterisk box. I don’t have that luxury, so I will have the SQL server sit on the server itself for now.

First, install MySQL:

sudo apt-get install mysql-server libmyodbc

During the install it will ask you for a root user password. Please enter a strong password, but one you will remember, as you will need it later.

Now we will need to use MySQL’s CLI client to set up our databases and tables. We will be calling the database “asterisk”, and the standard for CDR reports is a table called “cdr”. Of course, you can create the table in a separate database if you want.

Make a new file to copy all of the lines we need to make the table that CDR will use.

Now, copy and paste the following into a new file, I called it cdr.sql:

cd /tmp
vi cdr.sql

Putting it in /tmp will make the file disappear automatically on reboot. It can really be put anywhere, but this guide assumes that location, so change the path to suit your needs.

Copy and paste the following into the new file:

        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 '', 
        uniqueid varchar(32) NOT NULL default '', 
        userfield varchar(255) NOT NULL default '' 

ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

Now, save and exit. Don’t forget the semicolon on the last line.

Log in to MySQL:

mysql -u root -p

Create a new database:


If you are new to MySQL, every command that completes correctly responds with something similar to:

Query OK, 1 row affected (0.00 sec)

If not, it will tell you the error. 99% of the time it’s a syntax error, so check for spelling, etc. Also, every command must end with a semicolon.

Now, let’s go into the database and create the table:

USE asterisk;

It should now say “Database changed.” We can now create the CDR table. This can be done a few ways. You might be able to copy everything below in directly, or you can copy and paste it into a file on your asterisk box in your home directory (or wherever else that’s convenient, like your home directory).

Next, import the table structure we saved to a file earlier:

SOURCE /tmp/cdr.sql

If there are no errors, then it might say “0 rows affected” even though it actually imported.

Double check and make sure it’s all there:


It should show you 16 rows (it will say how many on the bottom).

Now, let’s create a user for CDR (and CEL):

CREATE USER 'asterisk'@'localhost' IDENTIFIED BY 'yourpasswordhere';

I used a random password generator site to generate a very long random password. I highly recommend using the longest, hardest, and strongest password you can bear to use to keep your system secure. These passwords will be stored in plain text in the configuration file, so DO NOT use your “normal” passwords. I immediately wrote the password down in a secure password file I have. Don’t lose this password! It will be needed in a few steps.

Now that all that is done, give this user permissions. For security the user will only be able to add or remove data, not tables or the entire database.

Add permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON asterisk.* TO 'asterisk'@'localhost';

To double check, you can execute the following command and you should see the permissions listed:

mysql> SHOW GRANTS FOR 'asterisk'@'localhost';
| Grants for asterisk@localhost                                                                                   |
| GRANT USAGE ON *.* TO 'asterisk'@'localhost' IDENTIFIED BY PASSWORD '*CCC9275DB00A1C4GH9B756752F9896DBF5EBE395' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `asterisk`.* TO 'asterisk'@'localhost'                                  |
2 rows in set (0.00 sec)

Now we are done with the MySQL side. type “exit” to leave the console.

Next we will need to configure unixODBC to connect to MySQL. This will vary slightly based on your installation. The file we are looking for is “libmyodbc.so”. Once we know where the file is, we can edit the odbc.ini file to set up a MySQL connection.

First, find and make a note of where libmyodbc.so file is located:

sudo updatedb
locate libmyodbc.so

This usually should return one line. If there is more, look for a path that’s similar to mine:


Do the same for libodbcmyS.so, but without the updatedb command as it’s not needed. Make sure to note both paths.

Edit the /etc/odbcinst.ini to reflect the MySQL setup correctly:

Driver          = /path/to/libmyodbc.so

Description     = MySQL driver
Driver          = /path/to/libmyodbc.so
Setup           = /path/to/libodbcmyS.so

Note: Make sure the [Default] section exists and specifies a driver, otherwise the res_odbc module in Asterisk will bark.

Now, edit the /etc/odbc.ini file (which might be blank) and add the following:

Driver          = MySQL
Description     = MySQL Connector for Asterisk
Server          = localhost
Port            = 3306
Database        = asterisk
username        = asterisk
password        = asteriskpasswordhere
Option          = 3
Socket          = /var/run/mysqld/mysqld.sock

Edit /etc/asterisk/res_odbc.conf to say the following:

sanitysql=select 1

Edit /etc/asterisk/cdr_odbc.conf to say the following:


Note: The dsn in cdr_odbc.conf is the dsn specified in res_odbc.conf, not the dsn specified in odbc.ini.

Edit cdr_manager.conf to say the following:

enabled = yes

Finally, edit /etc/ cdr_adaptive_odbc.conf to say the following:

alias start=calldate

NOTE: If you use the sample configs that come with Asterisk, then there is already a couple sections that are similar to this one. I personally backed up the default one, and then emptied it out to only say the above lines. This way, there are no problems. However, if you already have database connection definitions here, make sure to not delete those of course.

Save and exit, and then reload Asterisk:

sudo service asterisk restart

Now you can make a test call where the other end answers, and then hang up. There should be no CDR errors.

You can do a quick check to make sure the data made it after the call:

mysql -u asterisk -p yourasteriskpassword
use asterisk;
select * from cdr;

If there are no records, double check for configuration errors. “dsn” names are case sensitive, and must match exactly.

Whew! That’s it!


There are some commands that can be used to troubleshoot any issues you might have:

In the asterisk console, using “cdr show status” should get you something similar:

asterisk*CLI> cdr show status

Call Detail Record (CDR) settings
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       No
  Log congestion:             No

* Registered Backends
    Adaptive ODBC

If not, there are some configuration errors somewhere. Your registered backends section might be different, as I have pared mine down to the minimum, but the there should be at least those 3 listed.

In the asterisk console, the command “odbc show all” should look almost exactly like this:

asterisk*CLI> odbc show all

ODBC DSN Settings

  Name:   asterisk
  DSN:    MySQL-asterisk
    Last connection attempt: 1969-12-31 17:00:00
  Pooled: No
  Connected: Yes

If not, then there is a database connection issue. Check your odbc.ini and odbcinst.ini files to make sure they are correct, that the user/password is correct, and that the user has proper access to the correct database.