ODBC
Using MySQL for CDR records is supported by using ODBC and the cdr_adaptive_odbc module (depends on res_odbc).
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'
Comments:
There are more fields available, 'start', 'answer' and 'end'. ![]() |
The start is already there, but it's called calldate in MySQL for instance. If you do a grep "^[^;]" /etc/asterisk/cdr_mysql.conf on an Asterisk 1.8.4 installation amongst other things it reveals: [columns] alias start => calldate alias callerid => clid 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:
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 In SQL this would look like this: SELECT *, (`calldate`+(`duration`-`billsec`)) AS `answer`, (`calldate`+`duration`) AS `end` FROM cdr; If you want to create a view to avoid this calculation by hand you can do so with this command: CREATE VIEW YOUR_DATABASE.cdr_full AS SELECT *, (`calldate`+(`duration`-`billsec`)) AS `answer`, (`calldate`+`duration`) AS `end` FROM cdr; 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): CREATE VIEW YOUR_DATABASE.cdr_full AS SELECT *, CAST( `calldate`+(`duration`-`billsec`) AS DATETIME ) AS `answer`, CAST( `calldate`+`duration` AS DATETIME ) AS `end` FROM `cdr`; 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: CREATE VIEW YOUR_DATABASE.cdr_full AS SELECT *, CAST( COALESCE(`calldate`+(`duration`-`billsec`), '0000-00-00 00:00:00') AS DATETIME ) AS `answer`, CAST( COALESCE(`calldate`+`duration`,'0000-00-00 00:00:00') AS DATETIME ) AS `end` FROM `cdr`; ![]() |
I am using this database structure: 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; 1. This table uses InnoDB that is much better for writing operations like logging. ![]() |