Asterisk, and other worldly endeavours.

A blog by Leif Madsen

Importing Master.csv into MySQL CDR table


Edit: I have updated the original post, which contained ‘custom’ field name in the database instead of ‘userfield’ which is a field Asterisk-Stat uses.
Edit2: Updated the field from ‘amaflag’ to ‘amaflags’ thanks to Justin.
Edit3: Updated the script based on feedback from Mads Peter Nielsen and Gabriel. Thanks!

Today I had a client that had a need to run some statistical analysis on their data, but since they didn’t have anything like that developed, I had to go on a search to find them something. After asking around on IRC (and pretty much knowing what the answer was already), I settled on installing the Areski asterisk-stat application.

Note: Installing Asterisk-Stat is really quite straight forward, however, the installation documentation is not verbose. It says to modify the describe.php file, but doesn’t tell you where it is. After figuring that I needed to modify my own describe.php file in the root folder, I found that it exists in the lib/ subdirectory. Hope that helps someone.

Since I had data in the Master.csv file (which is the file Asterisk writes its CDR data to by default), I just needed to import the data into a MySQL database (which I was doing because Asterisk-Stat doesn’t support reading in data from a flat-file), and run my newly installed Asterisk-Stat application.

Instead of reinventing the wheel again, I figured I’d check out the various links on the internet for how to do it. This inevitably lead me to the voip-info wiki, where lots of outdated, incomplete, misinformation exists. I will give credit that it gave me enough information (or links to information) to get what I needed done, but it required putting together a few pieces, and then modifying those pieces.

OK, time to get off my soap box and give you what you really want. And really, I can’t yell at it too much — I did eventually end up with what I needed!

First, I copied the Master.csv from /var/log/asterisk/cdr-csv/ into a temporary working directory. This way I wasn’t messing around with the customers live data. Also note, this is a one time import, and will eventually need to move them over to writing their data to the database directly, but that is another article.

So now that I have my working copy of Master.csv, it’s time to create the database, and import the data. I started by first installing the various applications I needed: httpd (apache), php, php-mysql, gd, and mysql-server. I then configured the database with a user login (so I didn’t need to modify as root), and created a database to put Asterisk related data into.

# mysql -u root -p
mysql> create database asterisk;
mysql> GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'localhost' IDENTIFIED BY 'somemagicpassword';
mysql> flush privileges;

OK awesome, I have now created my database, and have the ‘asterisk’ user assigned to it. Now lets create our ‘cdr’ table. Note that the following table description was taken from this page. It was the table description I found which contained the appropriate number of columns to match up with *my* Master.csv file, and which the column names matched up with the data. Your mileage may vary based on Asterisk version (note I’m using a 1.4 based version of Asterisk). Run the following from the MySQL command prompt.

create table cdr (
       accountcode varchar (30),
       src varchar(64),
       dst varchar(64),
       dcontext varchar(32),
       clid varchar(32),
       channel varchar(32),
       dstchannel varchar(32),
       lastapp varchar(32),
       lastdata varchar(64),
       calldate timestamp NOT NULL,
       answerdate timestamp NOT NULL,
       hangupdate timestamp NOT NULL,
       duration int(8) unsigned default NULL,
       billsec int(8) unsigned default NULL,
       disposition varchar(32),
       amaflags varchar(128),
       uniqueid varchar(128),
       userfield varchar(128),
       PRIMARY KEY (clid,channel,calldate,uniqueid)
);

Now that we have a table to import the data into, I had to find a script that would work. I found one originally, then realized it didn’t import the data as I needed it (oops, my bad for not looking carefully enough at the table structure), so I modified it to work with my table structure. The script was originally created by John Lange (thanks John!), and you can find it on his blog. Find below my modified version of his script to work with the above mentioned CDR table layout.

<?php
/*** 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.
*
* Author: John Lange (john@johnlange.ca)
* Date: Version 2 Released July 8, 2008
*
* Here is what the script does:
*
* Parse each row from the text log and insert it into the database after testing for a
* matching "calldate, src, duration" record in the database. Note that not all fields are
* tested.
*
* If you have a large existing database it is recommended that you add an index to the calldate
* field which will greatly speed up this import.
*
*/
/*
 * Modified by Leif Madsen, July 29, 2009 to add additional columns.
 * Original post and code by John Lange: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/
 */
$locale_db_host = 'localhost';
$locale_db_name = 'asterisk';
$locale_db_login = 'asterisk';
$locale_db_pass = 'somemagicpassword';
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());
mysql_select_db($locale_db_name, $linkmb) or die("Could not select database $locale_db_name");
//** 1) Find 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, $uniqueid, $userfield ) = $data;
/** 2) Test to see if the entry is unique **/
$sql="SELECT calldate, src, duration".
" FROM cdr".
" WHERE calldate='$start'".
" AND src='$src'".
" AND duration='$duration'".
" LIMIT 1";
if(!($result = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
if(mysql_num_rows($result) == 0) { // we found a new record so add it to the DB
// 3) insert each row in the database
$sql = "INSERT INTO cdr (calldate, answerdate, hangupdate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, userfield) VALUES('$start', '$answer', '$end', '".mysql_real_escape_string($clid)."', '$src', '$dst', '$dcontext', '$channel', '$dstchannel', '$lastapp', '$lastdata', '$duration', '$billsec', '$disposition', '$amaflags', '$accountcode', '$uniqueid', '$userfield')";
if(!($result2 = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
print("Inserted: $end $src $duration\n");
$rows++;
} else {
print("Not unique: $end $src $duration\n");
}
}
fclose($handle);
print("$rows imported\n");
?>

Now all that is left to do is import the data!

# php importcdr.php Master.csv
Advertisements

Written by Leif Madsen

2009/07/29 at 7:49 am

Posted in Asterisk

Tagged with , , , ,

24 Responses

Subscribe to comments with RSS.

  1. I just realized that the ‘custom’ column should probably be named ‘userfield’ since that is what Asterisk-Stat is going to be looking up. Will update post.

    Leif Madsen

    2009/07/29 at 9:02 am

  2. hellow
    this process is very easy and helpful for every body.
    many many thnaks

    musfiq
    dhaka
    bangladesh

    musfiq

    2009/08/21 at 2:50 am

  3. where do I put the script and what do i name the file?

    kelly

    2009/09/10 at 11:53 am

  4. Worked like a charm. Thanks!

    Justin

    2009/11/19 at 10:41 am

    • Whoops, one error I guess. The SQL table should have column amaflags, not amaflag.

      Justin

      2009/11/19 at 11:13 am

  5. Thanks much Leif,
    nice script and it is working.

    I googled out that you can load csv files directly into MySQL. A faster option to move big csv files of over 1 mln rows into database. Takes seconds compared to hours of using script.

    On MySQL command line run:

    load data local infile ‘/root/Master.csv’ into table cdr
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\n’
    (accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, answerdate, hangupdate, duration, billsec, disposition, amaflags, uniqueid, userfield);

    I tried it. Looks to work too.

    Vladislav (Beltsy, Republic of Moldova)

    2009/12/08 at 12:03 pm

  6. I have a problem, because the first line of my Master.csv is the oldest, so when it begins to read found a duplicate entry.

    Could you help me, Thanks.

    Javi

    2010/01/29 at 3:33 am

  7. Hi,
    uniqueid ? and duplicate entries ?
    How is it possible that there are duplicate entries ?
    How is the uniqueid generated ?
    Thanks

    Alain

    alainbastien

    2010/01/31 at 11:55 am

    • The script basically does some checks to determine if there are duplicate entries in the database already. I can’t quite remember how it does it, but I don’t think it’s based on the uniqueid as obviously the database would make sure that value is actually unique 🙂

      Leif Madsen

      2010/02/09 at 4:54 pm

  8. Can you explain better your question?

    Thanks

    Javi

    2010/02/01 at 6:49 am

  9. Thank you. Article is not bad, although there are shortcomings.

    whopix

    2010/03/09 at 3:17 am

    • If there are shortcomings, perhaps you could elaborate so I could update the article. Just stating that there are shortcomings is obviously vague.

      Leif Madsen

      2010/03/09 at 7:40 am

  10. Dear Leif Madsen,

    I think that you have an error in your script, in the line where it says:

    $sql=”SELECT calldate, src, duration”.
    ” FROM cdr”.
    ” WHERE calldate=’$end'”.
    ” AND src=’$src'”.
    ” AND duration=’$duration'”.
    ” LIMIT 1″;

    Shouldn’t it be: $start instet of $end

    $sql=”SELECT calldate, src, duration”.
    ” FROM cdr”.
    ” WHERE calldate=’$start'”.
    ” AND src=’$src'”.
    ” AND duration=’$duration'”.
    ” LIMIT 1″;

    Best regards

    Mads Peter Nielsen

    Mads Peter Nielsen

    2010/06/11 at 3:35 am

    • Yep, $start is correct. $end give you an error.

      I debug myself to understand, but you hit the problem. 🙂

      Gabriel

      2010/09/13 at 2:13 pm

  11. Hi,

    Mabe a stupid question. When I import the Master.csv information.Where will it?

    Flavio

    2010/10/01 at 12:25 pm

  12. It gives a error on line 41:

    PHP Parse error: syntax error, unexpected T_LNUMBER in /var/log/asterisk/cdr-csv/import.php on line 41

    please help

    Mrityunjoy Chattopadhyay

    2010/12/03 at 2:23 pm

  13. Sorry my bad, was doing it wrong.

    But I am stuck now with duplicate entry. Please help.

    Mrityunjoy Chattopadhyay

    2010/12/05 at 5:43 am

  14. really i loving u ….. you save me… thanks

    lulu

    2011/08/23 at 7:36 pm

  15. Thanks leif, it was really helpful. Can you please tell me how to import only the latest records after the first import, instead of trying to check the duplicacy by parsing through the whole records and getting the new records in the second import.

    anupam chakma

    2012/01/12 at 5:07 am

  16. Thanks Leif,

    A few edits to match my cdr table and it imported all the missing records!

    Saved me alot of time 🙂

    Tristan

    2012/02/20 at 9:10 pm

  17. Wow nice script dude….its really help…..

    gaban

    2012/04/10 at 2:46 am

  18. Hey Leif, thanks a bunch. Was looking for a way to get the total call minutes for accounts purposes and once I created the table and fomatted my cdr output to contain only the months i needed, i ran mysql “load data infile…” and was able to calculate the total miutes very easily. Thanks.

    Faith

    2012/07/24 at 1:58 am


Comments are closed.

%d bloggers like this: