VoIP Mailing List Archives

[noah at allresearch.com]

Hi,

I managed to write a really short perl script to insert cdr records into mysql. I wanted to share it with the group...


Just call this from cron at whatever frequency you want to update your records.


DISCLAIMER: THIS IS A "QUICK AND DIRTY" SCRIPT. IT HAS NO BUILT IN ERROR CHECKING AND WAS JUST DESIGNED TO WORK FOR MY BASIC SETUP. YOU MAY NEED TO HEAVILY MODIFY IT TO WORK FOR YOU...




-Noah


-----------------------------------------------------------
#!/usr/local/bin/perl
use DBI;
use Text::ParseWords;


#connect to database and define path to your log files
$dbh = DBI->connect( 'DBI:mysql:freeswitch', 'username', 'password' ) or die "connect error";
$LOG_DIR = "/usr/local/freeswitch/log/cdr-csv";




#Make Freeswitch rotate the log files
system("killall -HUP freeswitch");


#prepare the insert statement - You may have to change this depending on the log file definintion in your freeswitch config.
$insert_sql = $dbh->prepare("insert into cdr (caller_id_name, caller_id_number, destination_number, context, start_timestamp, answer_timestamp, end_timestamp, duration, billsec, hangup_cause, uuid, bleg_uuid, accountcode) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");


#get a list of all the Master files with a datestamp on the end. (These are created when log files rotated above)
@log_files = <$LOG_DIR/Master.csv.*>;


#Loop through the files and insert into the table;
foreach $file (@log_files){
print "$file\n";
open(IN, $file) or die "cant open $file\n";
while (<IN>){
($caller_id_name, $caller_id_number, $destination_number, $context, $start_stamp, $answer_stamp, $end_stamp, $duration, $billsec, $hangup_cause, $uuid, $bleg_uuid, $accountcode, $read_codec, $write_codec) = quotewords(",", 0, $_);;
$insert_sql->execute($caller_id_name, $caller_id_number, $destination_number, $context, $start_stamp, $answer_stamp, $end_stamp, $duration, $billsec, $hangup_cause, $uuid, $bleg_uuid, $accountcode);
}
#Delete the log file since we've finished with it.
close(IN);
unlink($file);
}

[mcollins at fcnetwork.com]

Thanks for putting comments in the code. Is there possibly a place on the wiki where this script might be appropriate?
-MC


From: freeswitch-users-bounces@lists.freeswitch.org [mailto:freeswitch-users-bounces@lists.freeswitch.org] On Behalf Of Noah Silverman
Sent: Thursday, October 09, 2008 3:11 PM
To: Freeswitch-users@lists.freeswitch.org
Subject: [Freeswitch-users] Insert CDR into Mysql


Hi,


I managed to write a really short perl script to insert cdr records into mysql. I wanted to share it with the group...



Just call this from cron at whatever frequency you want to update your records.



DISCLAIMER: THIS IS A "QUICK AND DIRTY" SCRIPT. IT HAS NO BUILT IN ERROR CHECKING AND WAS JUST DESIGNED TO WORK FOR MY BASIC SETUP. YOU MAY NEED TO HEAVILY MODIFY IT TO WORK FOR YOU...





-Noah



-----------------------------------------------------------

#!/usr/local/bin/perl

use DBI;

use Text::ParseWords;



#connect to database and define path to your log files

$dbh = DBI->connect( 'DBI:mysql:freeswitch', 'username', 'password' ) or die "connect error";

$LOG_DIR = "/usr/local/freeswitch/log/cdr-csv";





#Make Freeswitch rotate the log files

system("killall -HUP freeswitch");



#prepare the insert statement - You may have to change this depending on the log file definintion in your freeswitch config.

$insert_sql = $dbh->prepare("insert into cdr (caller_id_name, caller_id_number, destination_number, context, start_timestamp, answer_timestamp, end_timestamp, duration, billsec, hangup_cause, uuid, bleg_uuid, accountcode) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");



#get a list of all the Master files with a datestamp on the end. (These are created when log files rotated above)

@log_files = <$LOG_DIR/Master.csv.*>;



#Loop through the files and insert into the table;

foreach $file (@log_files){

print "$file\n";

open(IN, $file) or die "cant open $file\n";

while (<IN>){

($caller_id_name, $caller_id_number, $destination_number, $context, $start_stamp, $answer_stamp, $end_stamp, $duration, $billsec, $hangup_cause, $uuid, $bleg_uuid, $accountcode, $read_codec, $write_codec) = quotewords(",", 0, $_);;

$insert_sql->execute($caller_id_name, $caller_id_number, $destination_number, $context, $start_stamp, $answer_stamp, $end_stamp, $duration, $billsec, $hangup_cause, $uuid, $bleg_uuid, $accountcode);

}

#Delete the log file since we've finished with it.

close(IN);

unlink($file);

}