CDR

[gmcust3]

I have installed Asterisk CDR.

Looks good.

Created Table also.

Wondering, when asterisk is running, it will AUTOMATICALLY insert data in cdr table ? Need any modification in Asterisk ?

[gmcust3]

I see cdr_mysql.conf

;
; Note - if the database server is hosted on the same machine as the
; asterisk server, you can achieve a local Unix socket connection by
; setting hostname=localhost
;
; port and sock are both optional parameters. If hostname is specified
; and is not "localhost", then cdr_mysql will attempt to connect to the
; port specified or use the default port. If hostname is not specified
; or if hostname is "localhost", then cdr_mysql will attempt to connect
; to the socket file specified by sock or otherwise use the default socket
; file.
;
;[global]
;hostname=database.host.name
;dbname=asteriskcdrdb
;table=cdr
;password=password
;user=asteriskcdruser
;port=3306
;sock=/tmp/mysql.sock
;userfield=1

Do I need to modify this file ?

If yes,

[global]
hostname=192.168.0.1
dbname=asterisk
table=cdr
password=somepassword
user=root
port=3306
sock=/tmp/mysql.sock
userfield=1

Is above Entry are correct ?

[brett05]

i use cdr before
and you need to have a script that convert csv to cdr mysql database .
i will show you my tips:
asterisk store in :/var/log/asterisk/cdr-csv/Master.csv
so what we need here we need to convert this csv file to a mysql table.
first 1=
we create a cdr table into asterisk database >
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` );
then we need a script thant convert csv to mysql:
so do this :
cd /usr/src
then pico or nano or vim a file named csv2cdr.php and put in it this :

<?PHP
$locale_db_host = 'localhost';
$locale_db_name = 'asterisk';
$locale_db_login = 'XXXXX';
$locale_db_pass = 'XXXXX';
$logfile = "/var/log/asterisk/cdr-csv/Master.csv";
$linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass)
or die("Could not connect : " . mysql_error());
mysql_select_db($locale_db_name, $linkmb)
or die("Could not select database $locale_db_name");
$sql="SELECT UNIX_TIMESTAMP(calldate) as calldate FROM cdr ORDER BY calldate DESC LIMIT 1";

if(!($result = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
$result_array = mysql_fetch_array($result);
$lasttimestamp = $result_array['calldate'];
$rows = 0;
$handle = fopen($logfile, "r");
while ( ($data = fgetcsv($handle, 1000, ",") ) !== FALSE) {
list($accountcode, $src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp,
$lastdata, $start, $answer, $end, $duration, $billsec, $disposition,
$amaflags ) = $data;
if (strtotime($end) > $lasttimestamp) {
$sql = "INSERT INTO cdr (calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode) VALUES('$end', '".mysql_real_escape_string($clid)."', '$src', '$dst', '$dcontext', '$channel', '$dstchannel', '$lastapp', '$lastdata', '$duration', '$billsec','$disposition', '$amaflags', '$accountcode')";
if(!($result2 = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
$rows++;
}
}
fclose($handle);
print("$rows imported\n");
?>

ok here we have all this we just miss to run the php file to have csv2cdr database.
so do this : php /usr/src/csv2cdr.php and you will see after that the table cdr in the database asterisk have all the information about the csv file .
you can also do a crontab if you want :
@hourly php /usr/src/csv2cdr.php
enjoy

[gmcust3]

Working now.

Thnx Brett.

[gmcust3]

Working now.

Thnx Brett.

[gmcust3]

Are there any Issues with Asterisk or Asterisk CDR ?

My VOIP Provider shows call duration as 11 sec and Asterisk CDR shows 26 secs. And in all connected calls, there is a Difference of 8 secs to 30 secs.

Where to set up cost in Asterisk CDR ?

I use Asterisk 1.2.30.2

[brett05]

try to upgrade to 1.4 of asterisk because he use more exactly the cdr .
then feedback if you have other thing