Search:
Asterisk cdr mysql
How to download cdr_mysql
Due to Mysql client libraries licensing, the Mysql billing application is no longer an integrated part of the Asterisk standard distribution. It is now located in the asterisk-addons CVS directory.Follow the instructions on http://www.asterisk.org/index.php?menu=download for Subversion download
# cd /usr/src
# svn checkout http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2
You must have mysql and mysql-devel packages installed.
- In an RPM-based Linux, you can check presence of MySQL like this:
- rpm -qa | grep mysql
- For debian or other dpkg-based systems, check like this:
- dpkg -l mysql-server libmysqlclient*dev
- In FreeBSD, you'll find MySQL in the ports library, /usr/ports/databases/mysql4-server
Compile
# cd asterisk-addons-1.2# make clean
# make
# make install
Check that in make stage that there are no mysql.h errors, which mean you are missing mysql-devel package
root@localhost asterisk-addons-1.2# make
./mkdep -fPIC -I../asterisk -D_GNU_SOURCE `ls *.c`
app_addon_sql_mysql.c:23:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:38:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:39:20: error: errmsg.h: No such file or directory
res_config_mysql.c:53:19: error: mysql.h: No such file or directory
res_config_mysql.c:54:27: error: mysql_version.h: No such file or directory
res_config_mysql.c:55:20: error: errmsg.h: No such file or directory
If make fails due to complaining about a missing "asterisk.h" file you can either copy this file from your asterisk directory, or create a soft link ("ln -s ...") for /usr/src/asterisk that points to your asterisk source directory.
Another way to resolve the missing "asterisk.h" file is to run the ./configure with option "--with-asterisk=" like
./configure --with-asterisk=MyAsteriskSourceDir
A sample configuration file, can be found on the cdr_mysql.conf page.
Copy the sample configuration file to /etc/asterisk/cdr_mysql.conf and edit it according to your requirements. Then edit your modules.conf to load cdr_addon_mysql.so and finally restart asterisk; before the restart you should, however, check that your cdr table has been created correctly and is accessible to the username and password you specified.
Table definitions for Asterisk cdr_mysql
This is the database definition you use to install in Mysql to support billing.Create the database
mysql --user=root --password=password [-h dbhost]
CREATE DATABASE asterisk;
GRANT INSERT
ON asterisk.*
TO asterisk@localhost
IDENTIFIED BY 'yourpassword';
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` );
Please note that the rights granted in GRANT above is the _least_ the asterisk user will need. To allow the user to do more than just add new data to the table, see the MySQL manual on the topic
for trunk version since 29 Dec 2007 must be:
GRANT INSERT, SELECT ...
because cdr_addon_mysql now do DESC 'cdr'; for check tables fields.
Hint: Copy and paste this SQL command into a text file, save it under an appropriate name, then execute the following command:
mysql --user=username --password=password databasename < nameoftextfile
Voila! The table is created for you. (:biggrin:)
An interesting note: on versions prior to v1.2, the name of the table, 'cdr', is hard-coded into the mysql interface, so if the table is created under a different name, the mysql CDR backend will not work. In v1.2 the table name is configurable via the "table=" option in cdr_mysql.conf
Yet another note: It probably isn't a good idea to use the InnoDB engine. Asterisk keeps autocommit turned on by default; therefore, each INSERT query is its own transaction. This will cause the query to take significantly longer to execute. On my personal computer, the "clock time" penalty is about 4-7 times that of MyISAM on a default Ubuntu install. Please also note that the CDR table does not require the features that the InnoDB engine provides.
Extending CDR
;dial trunk
exten => _X.,1,Dial(SIP/trunk/${EXTEN})
;exten h must be in same context!
exten => h,1,noop("extended CDR")
exten => h,n,set(CDR(hangupcause)=${HANGUPCAUSE}) ; hangupcause
exten => h,n,set(CDR(peerip)=${CHANNEL(peerip)}) ; like 10.0.0.5 if behind nat
exten => h,n,set(CDR(recvip)=${CHANNEL(recvip)}) ; like 194.79.52.192 - public ip
exten => h,n,set(CDR(from)=${CHANNEL(from)}) ; like sip:1111@sip.proxy.cz
exten => h,n,set(CDR(uri)=${CHANNEL(uri)}) ; like sip:1111@10.0.0.5
exten => h,n,set(CDR(useragent)=${CHANNEL(useragent)}) ; useragent like Aastra_57i
exten => h,n,set(CDR(codec1)=${CHANNEL(audioreadformat)}) ; codec *
exten => h,n,set(CDR(codec2)=${CHANNEL(audiowriteformat)}) ;
exten => h,n,set(CDR(llp)=${CHANNEL(rtpqos,audio,local_lostpackets)}) ; lost packets by local end **
exten => h,n,set(CDR(rlp)=${CHANNEL(rtpqos,audio,remote_lostpackets)}) ; lost packets by remote end
exten => h,n,set(CDR(ljitt)=${CHANNEL(rtpqos,audio,local_jitter)}) ; the same for jitter
exten => h,n,set(CDR(rjitt)=${CHANNEL(rtpqos,audio,remote_jitter)})
Storing the Unique ID
Q: It would appear that the "uniqueid" field is not being populated in the MySQL CDR DB. Is this an obsolete field or is a bug?A: You need to define MYSQL_LOGUNIQUEID at compile time for it to use that field.
You have two options in /usr/src/asterisk-addons:
1. Either add CFLAGS+=-DMYSQL_LOGUNIQUEID to the Makefile.
Note that recently (around Asterisk 1.4.18 or before) this has changed to ASTCFLAGS+=-DMYSQL_LOGUNIQUEID
2. Or instead add a #define MYSQL_LOGUNIQUEID to the top of cdr_addon_mysql.c.
Finally perform the usual make clean, make, make install. Be sure to check the Makefile for the presence of this flag after having done a CVS update! You will most probably also want to index the uniqueid field in your cdr table to improve performance.
You will also have to add a `uniqueid` column in your mysql database after the `accountcode` column:
ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ''
after `accountcode`;
What would I need all this for? For example you are running an AGI script and would like to be able to related AGI data with the CDR table. The problem is that the AGI script will lose connection to the call as soon as the caller hangs up, so you'll need a way to find the correct cdr entry (that'll also be created only after the call has been completed).
Attention! The uniqueid field is not guaranteed to be unique across the different CDR entries, even though the name suggests exactly that.
If you get the following error while compiling cdr_mysql:
/usr/bin/ld: cannot find -lz
Make sure you have the zlib-devel package installed.
See also
- Asterisk cdr odbc
- Asterisk billing
- Asterisk config cdr_mysql.conf
- Asterisk voicemail database
- Asterisk CDR csv mysql import
- Asterisk CDR csv mysql Perl import
- Asterisk queue_log on MySQL
- Asterisk CDR csv conversion mysql
- Asterisk CDR Master.csv postgresql Perl import
- What is CDR
Page Changes
