Also, the following assumes that you (or the creator of whatever package you're using) enabled cdr_addon_mysql at compile time. If this isn't the case, you need to enable it and recompile the appropriate add-on.
Contents |
Edit /etc/asterisk/cdr_mysql.conf so that the following lines are uncommented.
[global] hostname=localhost dbname=asteriskcdr password=PASSWORD user=astcdr port=3306 table=cdr
Note: The database name (dbname), table name (table), username (user), and password can be whatever you want. Recommend generating your own values, at least, for username and password.
Note: If the hostname is localhost, Asterisk will attempt to connect to the MySQL socket vice the port. If you're running MySQL on a localhost port, I recommend using the "port" directive.
Log into MySQL and create the database by running the following:
create database asteriskcdr
The output should look something like:
mysql> create database asteriskcdr; Query OK, 1 row affected (0.00 sec)
Still within MySQL, set up access to the database by running the following:
grant all on asteriskcdr.* to astcdr@localhost identified by "PASSWORD";
The output should look something like the following:
mysql> grant all on asteriskcdr.* to astcdr@localhost identified by "PASSWORD"; Query OK, 0 rows affected (0.09 sec)
Note: Asterisk only needs the grant permission, so if you're setting up a production system, you may want to reconsider "grant all" in the above.
While still logged into MySQL, declare "use" of the newly created database by running:
use asteriskcdr;
Note the semi-colon at the end. The output should look something like:
mysql> use asteriskcdr; Database changed
At the MySQL prompt, type in (or cut and paste) the following:
USE asterisk;
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 "" ); ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ""; ALTER TABLE `cdr` ADD INDEX ( `calldate` ); ALTER TABLE `cdr` ADD INDEX ( `dst` ); ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
The output should look something like:
mysql> 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 ""
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `cdr` ADD INDEX ( `calldate` );
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `cdr` ADD INDEX ( `dst` );
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Those last four entries are recommended tweaks to the database. The source for this info (the cdr mysql page on VoIP-Info.org) has been around for awhile and these tweaks were added to keep the database current with the version of Asterisk.
At this point, it's a good idea to notify MySQL that it should reload the privilege table. This can be done by running
flush privileges;
The output should look like:
mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
Restart Asterisk and make a call into your system. (I called into an empty conference room, waited a few seconds and hung up.) You can then look at your records (while stilled logged into MySQL) by running:
select * from cdr;
The output will look something like:
mysql> select * from cdr; +---------------------+------+------+-----+----------+--------... | calldate | clid | src | dst | dcontext | channel... +---------------------+------+------+-----+----------+--------... | 2009-03-28 09:38:54 | | 1701 | 201 | default | SIP/170... +---------------------+------+------+-----+----------+--------... 1 row in set (0.00 sec)
I've truncated the above for brevity. Yours should be much wider and contain more detail.
The above is a very basic setup. The CDR facility in Asterisk is quite powerful and supports all numbers of other functions (CallerID munging, billing, etc.). I recommend reading up on it if you're doing anything more than running a simple Asterisk rig.