maxcallcount

maxcallcount is nice little perl script that generates meaningful statistics from the asterisk cdr database or alternatively from the asterisk text based cdr-csv/Master.csv files. (note: if you intend to read from asterisk log files you will need to install the perl module Text::CSV_XS and uncomment line 13).

Example usage: when invoked from the command line as follows:

# /maxcallcount -h localhost -u root -p PASSWORD -d asterisk -t cdr `date –date=yesterday +%F` `date +%F`

it generates these output statistics:

Report from: Tue Jul  8 00:00:00 2008 – Wed Jul  9 00:00:00 2008
Total calls: 4335 (879671 sec – 14661 min)
Longest: 3 days 16:57:15 (320235 sec) Source: 9056867668 Date: Tue Jul  8 13:02:17 2008 – Sat Jul 12 05:59:32 2008
Peak: 32 calls on Tue Jul  8 14:50:40 2008
ZapPeak: 13 calls on Tue Jul  8 16:31:48 2008
SipPeak: 25 calls on Tue Jul  8 14:50:40 2008
IAXPeak: 0 calls on Tue Jul  8 00:08:23 2008

Here is the source code (warning: use the download link to get sourecode):

#!/usr/bin/perl

# look through Asterisk cdr database and max usage for given time period
#
# Author: John Lange 
# Dec 15, 2006

$|++;
use strict;
use DBI; #comment this line if you don't need database access and don't have the module
use Date::Format;
use Date::Parse;
#use Text::CSV_XS; #comment this line if you don't need file access and don't have the module
#use Text::CSV; #The older name for this module. Try this if CSV_XS doesn't work for you.

my $calls = 0;
my $totalduration = 0;
my $longest = 0;
my @longestdetail;
my %peak;
my %zappeak;
my %sippeak;
my %iaxpeak;
my $startstamp;
my %opt;
my $verbose;
my $db;
my $host;
my $user;
my $pass;
my $dbtable;
my $infile;
my $delim;

# Command line options processing
sub init() {
use Getopt::Std;
my $opt_string = 'vs:f:h:u:p:d:t:';
getopts( "$opt_string", \%opt ) or usage();
}

# Message about this program and how to use it
sub usage()
{
print STDERR << "EOF";
usage: $0 [-v [-s ]] [-f logfile] [-h dbhost] [-u dbuser] [-p dbpass] [-d database] [-t table]  
-f    : input file - asterisk csv logfile (default none)
-h    : mysql database host (default localhost)
-u    : databaes user (default 'root')
-p    : database password (default '')
-d    : database (default 'asterisk')
-t    : database table (default 'cdr')
-v    : verbose - display every record to stdout (default off)
-s    : deliminator to use between fields during verbose output (default is tab)
startdate : Must be in mysql compatible date format (YYYY-MM-DD HH:MM:SS)
enddate   : Same format as startdate.

example: $0 "2006-12-01" "2006-12-31 23:59:59"
Note: remember that mysql interprets a date with no time as midnight thus enddate should be the next day or include the time as in the example.

EOF
exit;
}

init();

sub process {
my @calldetail = @_;
(my $calldate, my $src, my $dst, my $duration, my $accountcode, my $channel) = @calldetail;
if($calls == 0) {
$startstamp = $calldate;
}
$calls++;
$totalduration += $duration;
if($duration > $longest) {
$longest = $duration;
@longestdetail = @calldetail;
}
for(my $x=0; $x connect("DBI:mysql:$db:host=$host", $user, $pass);
my $sql = "SELECT UNIX_TIMESTAMP(calldate) as calldate, src, dst, duration, accountcode, channel
FROM $dbtable
WHERE calldate > '$startdate'
AND calldate prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
my $rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
while (my @row = $sth->fetchrow_array()) {
process(@row);
if($verbose) {
printf("%s\n", join ("$delim", @row));
}
}
} else {
my $csv = Text::CSV_XS->new();
open (CSV, "<", $infile) or die $!;
while () {
if ($csv->parse($_)) {
(my $accountcode,my $src,my $dst,my $dcontext,my $clid,my $channel,my $dstchannel,my $lastapp,my $lastdata,my $start,my $answer,my $end,my $duration,my $billsec,my $disposition,my $amaflags) = $csv->fields();
my $timestamp = str2time($start);
if($verbose) {
print("$start\t$src\t$dst\t$duration\t$accountcode\n");
}
if($timestamp > str2time($startdate) && $timestamp error_input;
print "Failed to parse line: $err";
}
}
close CSV;
}

my @peaks = sort {$peak{$b}  $peak{$a}} keys %peak;     # asc by value
my @zappeaks = sort {$zappeak{$b}  $zappeak{$a}} keys %zappeak;     # asc by value
my @sippeaks = sort {$sippeak{$b}  $sippeak{$a}} keys %sippeak;     # asc by value
my @iaxpeaks = sort {$iaxpeak{$b}  $iaxpeak{$a}} keys %iaxpeak;     # asc by value
(my $calldate, my $src, my $dst, my $duration, my $accountcode) = @longestdetail;
my @parts = gmtime($longest); # Convert seconds to days, hours, minutes, seconds

printf("Report from: %s - %s\n", time2str("%a %b %e %T %Y", str2time($startdate)), time2str("%a %b %e %T %Y", str2time($enddate)));
printf("Total calls: %d (%d sec - %d min)\n", $calls, $totalduration, ($totalduration / 60));
printf("    Longest: %01d days %01d:%02d:%02d (%4d sec) Source: %s Date: %s - %s\n",@parts[7,2,1,0],$longest, $src, time2str("%a %b %e %T %Y", $calldate), time2str("%a %b %e %T %Y", $calldate + $duration));
printf("       Peak: %01d calls on %s\n", $peak{$peaks[0]}, time2str("%a %b %e %T %Y", $peaks[0] + $startstamp));
printf("    ZapPeak: %01d calls on %s\n", $zappeak{$zappeaks[0]}, time2str("%a %b %e %T %Y", $zappeaks[0] + $startstamp));
printf("    SipPeak: %01d calls on %s\n", $sippeak{$sippeaks[0]}, time2str("%a %b %e %T %Y", $sippeaks[0] + $startstamp));
printf("    IAXPeak: %01d calls on %s\n", $iaxpeak{$iaxpeaks[0]}, time2str("%a %b %e %T %Y", $iaxpeaks[0] + $startstamp));