Asterisk CDR Master.csv postgresql Perl import
Created by: stefmtl,Last modification on Wed 21 of Mar, 2007 [02:45 UTC]
Here is a little perl script to import your Master.csv asterisk log file into your cdr postgresql table :
import.pl
import.pl
#!/usr/bin/perl -w
use strict;
use DBI;
# this script import asterisk cdr log Master.csv into a postgresql table
my $cdr_log_file = $ARGV[0];
my $pg_host = $ARGV[1];
my $pg_db = $ARGV[2];
my $pg_table = $ARGV[3];
my $pg_user = $ARGV[4];
my $pg_pwd = $ARGV[5];
&dbformat if ($ARGV[0] eq "database_format");
&usage if (!$ARGV[5]);
# Connect to database
print "Connecting to database...\n\n";
my $dsn="DBI:Pg:dbname=$pg_db;host=$pg_host;port=5432";
my $dbh=DBI->connect($dsn,$pg_user,$pg_pwd);
if ($dbh) {
print "Successfully connected to $dsn";
open cdr_log, "<$cdr_log_file" or die "Cannot open cdr_log_file\n";
while (<cdr_log>) {
$_ =~ s/\n//iog;
$_ =~ s/\"//iog;
my (@fields) = split(/\,/, $_);
my $insert_str = "insert into $pg_table (src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec, disposition, amaflags) values (\"".$fields[1]."\", \"".$fields[2]."\", \"".$fields[3]."\", \"".$fields[4]."\", \"".$fields[5]."\", \"".$fields[6]."\", \"".$fields[7]."\", \"".$fields[8]."\", \"".$fields[9]."\", \"".$fields[12]."\", \"".$fields[13]."\", \"".$fields[14]."\", \"".$fields[15]."a\");\n";
print $insert_str."\n";
my $sth = $dbh->prepare($insert_str);
$sth->execute();
$sth->finish();
}
$dbh->disconnect();
close (cdr_log);
}
else{
die("Problem connecting to : $dsn\n");
}
print "\n\nEnd.\n";
exit;
sub usage() {
print_header();
print "\nUsage: perl import.pl <cdr_log_file> <pg_hostname> <database> <table> <username> <password>";
print "\n\nTo see the expected database format run perl import.pl database_format\n\nEnd.\n";
die;
};
sub dbformat() {
print_header();
print "\nimport.pl expects a table containing the following fields:\n
calldate timestamp with time zone DEFAULT now() NOT NULL,
clid character varying(80) DEFAULT ''::character varying NOT NULL,
src character varying(80) DEFAULT ''::character varying NOT NULL,
dst character varying(80) DEFAULT ''::character varying NOT NULL,
dcontext character varying(80) DEFAULT ''::character varying NOT NULL,
channel character varying(80) DEFAULT ''::character varying NOT NULL,
dstchannel character varying(80) DEFAULT ''::character varying NOT NULL,
lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
lastdata character varying(80) DEFAULT ''::character varying NOT NULL,
duration bigint DEFAULT (0)::bigint NOT NULL,
billsec bigint DEFAULT (0)::bigint NOT NULL,
disposition character varying(45) DEFAULT ''::character varying NOT NULL,
amaflags bigint DEFAULT (0)::bigint NOT NULL,
accountcode character varying(20) DEFAULT ''::character varying NOT NULL,
userfield character varying(255) DEFAULT ''::character varying NOT NUL
";
die;
};
sub print_header() {
print "\nimport.pl - Load Asterisk CDR datas to PGSQL database\n";
print "written by Stéphane HENRY stephane.henry (=at=) heberge.net - 2007-03-20\n";
}


Comments