Skip to end of metadata
Go to start of metadata
ODBC

Using MySQL for CDR records is supported by using ODBC and the cdr_adaptive_odbc module (depends on res_odbc).

Icon

The below cdr_mysql module has been deprecated in 1.8.

Native

Alternatively, there is a native MySQL CDR module.

To use it, configure the module in cdr_mysql.conf. Create a table called cdr under the database name you will be using the following schema.

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

The following columns can also be defined:

        peeraccount varchar(20) NOT NULL default ''
        linkedid varchar(32) NOT NULL default ''
        sequence int(11) NOT NULL default '0'
  • No labels

4 Comments

  1. Eugene

    There are more fields available, 'start', 'answer' and 'end'.

    1. kwk

      The start is already there, but it's called calldate in MySQL for instance.

      If you do a

      on an Asterisk 1.8.4 installation amongst other things it reveals:

      So what's Asterisk internally called start is called calldate in MySQL. As you can see from the .conf excerpt, the same goes for callerid and clid.

      If I'm not totally mistaken, end and answer can be derived from duration and billsec. In the original description of the CDR Fields it says this:

      • start: Start of call (date/time)
      • answer: Answer of call (date/time)
      • end: End of call (date/time)
      • duration: Total time in system, in seconds (integer), from dial to hangup
      • billsec: Total time call is up, in seconds (integer), from answer to hangup

      In MySQL you only have duration, billsec, and start which is actually called calldate.

      Thus, this should be what you're looking for:

      start = calldate
      answer = start + (duration - billsec)
      end = start + duration

      In SQL this would look like this:

      If you want to create a view to avoid this calculation by hand you can do so with this command:

      There is one problem with the column types though: answer and end in my case are of type double(23,6) which is not what we want. To fix this we need to do some casting (Don't know if this is MySQL specific):

      If you want to be perfectly correct, the colum attributes like NOT NULL and default values need to be set correctly. Well, since all the other columns carry the NOT NULL definition it should suffice if you just do the calculation. Anyway should by whatever circumstance the result of a calculation be NULL you might want to default to '0000-00-00 00:00:00'. Here's how you can achieve this:

  2. Eugene

    I am using this database structure:

    1. This table uses InnoDB that is much better for writing operations like logging.
    2. It has microsecond resolutions for timers.
    3. It has more fields that are used in recent Asterisk versions.
    4. It has indexes that make search and select operations faster

    1. Bart Coninckx

      There is an error in the SQL code - there should not be a comma after 

      INDEX `clid` (`clid`)

      If not the import fails