Configuring Asterisk from the Ground Up (2/2)

Disclaimer: I recommend and acknowledge www.voip-info.org as my comprehensive online reference to the details on Asterisk. 
Also, blogspot doesn't seems to like "<" and ">" symbols, hence some of the code posted maybe incorrect.


This is the second part of the Asterisk build guide, which covers setting Asterisk to store the CDR (call details record) in an MySQL back-end and reading the records through a web-browser.


By default, /var/log/asterisk/cdr-csv/ Master.csv

"","xlite","1234","default","""jlam"" ","SIP/xlite-2a2b6000","SIP/xlite-2a2c5000","Dial","SIP/xlite","2012-01-14 09:00:54",,"2012-01-14 09:01:01",7,0,"NO ANSWER","DOCUMENTATION","1326531654.11","" "","xlite","1234","default","""jlam"" ","SIP/xlite-2a2b6000","SIP/xlite-2a2c5000","Dial","SIP/xlite","2012-01-14 09:02:17",,"2012-01-14 09:02:37",20,0,"NO ANSWER","DOCUMENTATION","1326531737.13",""

Asterisk addons is required to be installed (/usr/ports/net/asterisk14-addons) because this functions uses the 

(/usr/local/lib/asterisk/modules/)cdr_addon_mysql.so module. 

Note: libtool24 (/usr/ports/devel/libtool24) is required to be installed first before adding asterisk14-addons.

Load module every time Asterisk starts - modules.conf

;;; CDR MySQL backend addon: load => cdr_addon_mysql.so

On MySQL, I used this example MySQL script in voip-info.org which basically reflects the structure of the Master.csv file:

# mysql –u asterisk –ppassword (note there is no space between “-p” and the password)

CREATE DATABASE asterisk; GRANT INSERT ON asterisk.* TO asterisk@localhost IDENTIFIED BY 'password'; 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` );

cdr_mysql.conf - define MySQL database and table name

[global] dbname=asterisk table=cdr password=password user=asterisk

cdr.conf - comment out "csv" back end and added "mysql"

; CHOOSING A CDR "BACKEND"  (what kind of output to generate) ;[csv] ;usegmtime=yes    ; log date/time in GMT.  Default is "no" ;loguniqueid=yes  ; log uniqueid.  Default is "no" ;loguserfield=yes ; log user field.  Default is "no" [mysql] usegmtime=yes    ; log date/time in GMT.  Default is "no" loguniqueid=yes  ; log uniqueid.  Default is "no" loguserfield=yes ; log user field.  Default is "no"

Note: remember to add user “asterisk” on the local host, else the CDR records won’t be inserted into MySQL.

Displaying the records using web browser:

Ensure Apache and PHP are installed. The versions of the software on my system are:

home-unix6# httpd -v

Server version: Apache/2.2.14 (FreeBSD)

Server built:   Feb 21 2010 13:14:06

home-unix6# php -v

PHP 5.3.2 with Suhosin-Patch (cli) (built: Jun  3 2010 20:54:27)

Copyright (c) 1997-2009 The PHP Group

Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies

This link contains a sample php file which prints all contents in the MySQL database. I have hard-coded the database and table name. 

[index.php not shown here due to blogspot incorrectly displaying "<" and ">"]

A new directory for CDR records is created, where the file above is saved - /usr/local/www/cdr/index.php

Modify the /usr/local/etc/apache22/httpd.conf

Listen 0.0.0.0:80 DocumentRoot "/usr/local/www/cdr" LoadModule php5_module libexec/apache22/libphp5.so Options Indexes FollowSymLinks AllowOverride None Order allow,deny Allow from all # # DirectoryIndex: sets the file that Apache will serve if a directory # is requested. # #DirectoryIndex index.html DirectoryIndex index.php

 CDR records on Web browser