Asterisk CDR csv mysql import

This version still works but is not up to date. Please go here for the current version

Here is another take on a PHP script to import cdr.csv files into a mysql
cdr database. Useful for transitioning from text based cdr files to
database or even for nightly imports.

This script is safe to run multiple times on a log file that keeps growing
because it first checks the database table to find the oldest record and
then only imports CDRs that are NEWER than the oldest database record.
Note: Make sure you run this script before you activate mysql logging in Asterisk otherwise this script will import nothing since the newest record in the DB will be newer than the records in the Master.csv file.
Copy and paste the script into a text file, for example "importcdr.php" and then use php at the command line to execute it as follows:

  1. php importcdr.php Master.csv

UPDATE July 9, 2008: The script below still works with current versions of Asterisk, PHP, and mySQL. However, I have rewritten the script entirely and have released it as version 2. The source code has been moved to my own web site where it will me maintained from this point forward. Please follow this link for the most recent version: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/

UPDATE August 23, 2007: Still Working fine, tested with Asterisk 1.2.16, Mysql 4.1.20 and PHP 5.0.4

UPDATE December 4, 2006: This script still works fine with Asterisk 1.2.13 and PHP5.

UPDATE July 21, 2005: I fixed this code so it should copy and paste properly now. The wiki doesn't like square brakets!

NOTE: The fields in the Master.csv file can vary depending on your settings. There is a comment in the source about it.




<?
/*** process asterisk cdr file (Master.csv) insert usage
 * values into a mysql database which is created for use
 * with the Asterisk_addons cdr_addon_mysql.so
 * The script will only insert NEW records so it is safe
 * to run on the same log over-and-over such as in the
 * case where logs have not been rotated.
 *
 * Author: John Lange (john.lange@open-it.ca)
 * Date: May 4, 2005. Updated July 21, 2005
 *
 * Here is what the script does:
 *
 * 1) Find the last log entry in the database cdr table.
 * 2) scan the asterisk logs until the dates are larger than the last log entry (so we don't duplicate entries)
 * 3) parse each row from the text log and insert it into the database.
 * 
 */

$locale_db_host  = 'localhost';
$locale_db_name  = 'asteriskcdrdb';
$locale_db_login = 'asteriskcdruser';
$locale_db_pass  = 'password';

if($argc == 2) {
    $logfile = $argv[1];
} else {
    print("Usage ".$argv[0]." <filename>\n");
    print("Where filename is the path to the Asterisk csv file to import (Master.csv)\n");
    print("This script is safe to run multiple times on a growing log file as it only imports records that are newer than the database\n");
    exit(0);
}

// connect to db
$linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass)
       or die("Could not connect : " . mysql_error());
//echo "Connected successfully\n";
mysql_select_db($locale_db_name, $linkmb) or die("Could not select database $locale_db_name");

/** 1) Find the last log entry **/
// look in cdr table to see when the last entry was made.
// this establishes the starting point for the asterisk data.
$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 = date("Y-m-d H:i:s", $result_array['voip_stamp']);
$lasttimestamp = $result_array['calldate'];

//** 2) Find new records in the asterisk log file. **

$rows = 0;
$handle = fopen($logfile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    // NOTE: the fields in Master.csv can vary. This should work by default on all installations but you may have to edit the next line to match your configuration
    list($accountcode,$src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration,
     $billsec, $disposition, $amaflags ) = $data;
    
    // 3) parse each row and add to the database
    if(strtotime($end) > $lasttimestamp) { // we found a new record so add it to the DB
        $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");
?>



see also * Asterisk cdr csv

This version still works but is not up to date. Please go here for the current version

Here is another take on a PHP script to import cdr.csv files into a mysql
cdr database. Useful for transitioning from text based cdr files to
database or even for nightly imports.

This script is safe to run multiple times on a log file that keeps growing
because it first checks the database table to find the oldest record and
then only imports CDRs that are NEWER than the oldest database record.
Note: Make sure you run this script before you activate mysql logging in Asterisk otherwise this script will import nothing since the newest record in the DB will be newer than the records in the Master.csv file.
Copy and paste the script into a text file, for example "importcdr.php" and then use php at the command line to execute it as follows:

  1. php importcdr.php Master.csv

UPDATE July 9, 2008: The script below still works with current versions of Asterisk, PHP, and mySQL. However, I have rewritten the script entirely and have released it as version 2. The source code has been moved to my own web site where it will me maintained from this point forward. Please follow this link for the most recent version: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/

UPDATE August 23, 2007: Still Working fine, tested with Asterisk 1.2.16, Mysql 4.1.20 and PHP 5.0.4

UPDATE December 4, 2006: This script still works fine with Asterisk 1.2.13 and PHP5.

UPDATE July 21, 2005: I fixed this code so it should copy and paste properly now. The wiki doesn't like square brakets!

NOTE: The fields in the Master.csv file can vary depending on your settings. There is a comment in the source about it.




<?
/*** process asterisk cdr file (Master.csv) insert usage
 * values into a mysql database which is created for use
 * with the Asterisk_addons cdr_addon_mysql.so
 * The script will only insert NEW records so it is safe
 * to run on the same log over-and-over such as in the
 * case where logs have not been rotated.
 *
 * Author: John Lange (john.lange@open-it.ca)
 * Date: May 4, 2005. Updated July 21, 2005
 *
 * Here is what the script does:
 *
 * 1) Find the last log entry in the database cdr table.
 * 2) scan the asterisk logs until the dates are larger than the last log entry (so we don't duplicate entries)
 * 3) parse each row from the text log and insert it into the database.
 * 
 */

$locale_db_host  = 'localhost';
$locale_db_name  = 'asteriskcdrdb';
$locale_db_login = 'asteriskcdruser';
$locale_db_pass  = 'password';

if($argc == 2) {
    $logfile = $argv[1];
} else {
    print("Usage ".$argv[0]." <filename>\n");
    print("Where filename is the path to the Asterisk csv file to import (Master.csv)\n");
    print("This script is safe to run multiple times on a growing log file as it only imports records that are newer than the database\n");
    exit(0);
}

// connect to db
$linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass)
       or die("Could not connect : " . mysql_error());
//echo "Connected successfully\n";
mysql_select_db($locale_db_name, $linkmb) or die("Could not select database $locale_db_name");

/** 1) Find the last log entry **/
// look in cdr table to see when the last entry was made.
// this establishes the starting point for the asterisk data.
$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 = date("Y-m-d H:i:s", $result_array['voip_stamp']);
$lasttimestamp = $result_array['calldate'];

//** 2) Find new records in the asterisk log file. **

$rows = 0;
$handle = fopen($logfile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    // NOTE: the fields in Master.csv can vary. This should work by default on all installations but you may have to edit the next line to match your configuration
    list($accountcode,$src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration,
     $billsec, $disposition, $amaflags ) = $data;
    
    // 3) parse each row and add to the database
    if(strtotime($end) > $lasttimestamp) { // we found a new record so add it to the DB
        $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");
?>



see also * Asterisk cdr csv

Created by: johnlange, Last modification: Wed 09 of Jul, 2008 (19:45 UTC)
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+