Asterisk – CDR Logging in MySQL

Okay, so you have got your asterisk box online. You have all the configuration done according to your needs. And now you are starting to develop applications to generate graphs and reports based on your call volume and flow. You’ll probably want to have your CDR logs entered into a database for easier management.



You will first need to install Asterisk-Addons. If you already have this installed, great. But we’ll probably need to make a quick change in order to have the uniqueid logged within the CDR. So you will edit the ‘cdr_addon_mysql.c’ and compile/recompile Asterisk-Addons. If you followed my quick installation guide, you will do the following:

nano /usr/src/asterisk-addons/cdr/cdr_addon_mysql.c

Add the following line to the top:

#define MYSQL_LOGUNIQUEID

Recompile Asterisk-Addons:

cd /usr/src/asterisk-addons/
make clean
make
make install

Now, we’ll copy our configuration file over to the Asterisk directory:

cp /usr/src/asterisk-addons/configs/cdr_mysql.conf.sample /etc/asterisk/cdr_mysql.conf

Once this is complete, we’ll edit to to match the database settings for our asterisk database:

[global]
hostname=localhost
dbname=asterisk
password=yourpassword
user=asterisk
loguniqueid=yes

And now we setup our tables within the database…

CREATE TABLE `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(100) NOT NULL default ''
);

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

Now just reload Asterisk from the Asterisk Cli:

reload

You’re done! Now you can write applications to query the database for the data you need. I’ll include some examples of this shortly.

UPDATE 12/10/08: Not sure which version brought this change… but if you want unanswered calls to be logged in the CDR database, you need to add the following to the cdr.conf:

unanswered = yes
This entry was posted on Friday, February 1st, 2008 at 6:18 pmand is filed under Asterisk PBX. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

8 Responses to “Asterisk – CDR Logging in MySQL”

  • AHostedSeries » Asterisk - Getting Results from CDR Database! Says:
    February 5th, 2008 at 1:36 pm

    [...] in my previous article, I said I would provide some sample scripts to view information from our CDR database. My script is [...]

  • unixvps Says:
    January 9th, 2009 at 1:05 pm

    Good article!
    Very useful!
    Tnx!

  • Author Says:
    April 15th, 2009 at 5:25 pm

    If you want to have userfield logged, you have to add the following within:
    /usr/src/asterisk-addons/cdr/cdr_addon_mysql.c

    #define MYSQL_LOGUSERFIELD

  • GF Says:
    October 14th, 2009 at 2:09 am

    hi,
    what about this fields : start, answer, end ?
    do you don\’t use anymore ?

  • Author Says:
    October 21st, 2009 at 4:52 pm

    GF,

    At the time that I wrote this article, those fields weren’t in the CDR. The Asterisk community was still discussing how they were going to adjust the CDR system. (Which I think they are still doing as well.)

    But yes, you could use these fields. Having the duration and billsec really does it for me. But if you want to know when the call started, was answered, and was ended, you can add those columns as well.

  • Mantas Gavenas Says:
    November 9th, 2009 at 2:08 pm

    Hi,

    Where could be the problem?

    [Nov 5 17:56:36] ERROR[6054]: cdr_addon_mysql.c:160 mysql_log: cdr_mysql: cannot connect to database server localhost.

  • Author Says:
    November 9th, 2009 at 3:01 pm

    Hi Mantas,

    Make sure your connection details are correct in the cdr_mysql.conf? And make sure that your database user has permissions to access that database?
    Let me know if you still have problems. Email me your server details and I can have a look: author (@) hostseries.com

  • Asterisk – Getting Results from CDR Database! | bahjons.com Says:
    May 14th, 2010 at 5:58 pm

    [...] in my previous article, I said I would provide some sample scripts to view information from our CDR database. My script is [...]

Leave a Reply