# xlsummary.pl
#
# Extract summary figures from the MRTG log files. In particular it summarises
# all sites (i.e. as many as will fit on a page) into a single Excel chart.
#
# Args are:
# h help
# d,w,m do stats for last day, week or month
# f directory containing mrtg log files
#
# v1.1 17/2/99 Tony Farr
#
use Getopt::Std;
use File::Basename;
use strict;
my $progname = basename($0);
my $usage = "Usage: $progname [-h] [-dwm] [-f path_of_logs]\n";
use vars qw/$opt_h $opt_d $opt_w $opt_m $opt_f/;
getopts('hdwmf:') || die $usage;
if ( defined($opt_h) ) {
print $usage;
exit(0);
}
if ( $opt_d + $opt_w + $opt_m != 1) {
die $usage;
}
if ( $opt_f ) {
chdir $opt_f || die "$progname: Unable to chdir to $opt_f; $!";
}
my ($start, $end) = &get_duration;
my $fin= $ENV{"TMP"}."\\$$.csv";
write_csv($start, $end, $fin);
my $fout = "$opt_f\\" . basename($progname,".pl") . ".xls";
graph_csv($start, $end, $fin, $fout);
unlink($fin);
exit(0);
sub write_csv {
my ($start, $end, $fname)= @_;
# Process all the log files
open(CSV, ">$fname");
print CSV "Site, Maximum, Minimum, Halfway\n";
foreach my $fname (sort glob("*.log")) {
my ($t, $lastt, $sumt, $deltat, $max, $lastmax, $summax, $min, $lastmin, $summin);
open(LOG, "<$fname") || die "$progname: Unable to open $fname; $!";
<LOG>; # Skip the header line
while (<LOG>) {
chomp;
($t, $max, $min)= split/ /;
if ($t < $start) { last }; # Log has latest first
if ($t <= $end) {
if ($lastmax) { # Zeroes mean the link was down.
$deltat= $lastt - $t;
$sumt += $deltat;
$summax += $lastmax * $deltat;
$summin += $lastmin * $deltat;
}
($lastt, $lastmax, $lastmin)= ($t, $max, $min);
}
}
# This is site specific. The devices we ping have names like SSSS-rtr
# or SSSS-lan where SSSS is the site name. This strips off the "-rtr"
# or "-lan" suffix.
my $target= ucfirst( basename($fname, "-lan\.log", "-rtr\.log", "\.log") );
if ($sumt) {
print CSV "$target, ". ($summax/$sumt) .", ". ($summin/$sumt) .", ". (($summax+$summin)/(2*$sumt)) ."\n";
}
close(LOG);
}
close(CSV);
}
sub get_duration {
# Returns the starttime & endtime to be reported on
my($starttime, $endtime);
my $t= time;
my ($sec,$min,$hour,$mday,$mon,$year,$wday) = localtime($t);
if ($opt_d) {
$endtime= $t - ($sec + 60*$min + 60*60*$hour);
$starttime= $endtime - 24*60*60;
} elsif ($opt_w) {
$endtime= $t - ($sec + 60*$min + 60*60*$hour + 24*60*60*$wday);
$starttime= $endtime - 7*24*60*60;
} else { # opt_m
$endtime= $t - ($sec + 60*$min + 60*60*$hour + 24*60*60*($mday-1));
$starttime= $endtime - days_in_month($mon - 1, $year)*24*60*60;
}
($starttime, $endtime);
}
sub days_in_month {
(31, $_[1] % 4 ? 28 : 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)[${_[0]}];
}
sub graph_csv {
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Const 'Microsoft Office';
use POSIX;
my ($start, $end, $fin, $fout)= @_;
# use existing instance if Excel is already running
my $xl;
eval {$xl = Win32::OLE->GetActiveObject('Excel.Application')};
die "$progname: Excel not installed" if $@;
unless (defined $xl) {
$xl = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
|| die "$progname: Cannot start Excel; $!";
}
# open the CSV
my $book = $xl->Workbooks->Open($fin);
# Sort data into descending order
my $sheet = $book->Worksheets(1);
my $a1 = $sheet->Cells(1, 1);
my $range = $sheet->Range($a1, $a1->End(xlDown)->End(xlToRight));
$range->Sort( {Key1 => $sheet->Range("D2"), Order1 => xlDescending} );
# Put data into a chart. The most that can be sensibly squeezed in
# is 52 sites. So select the slowest.
$sheet->Range($a1, $sheet->Cells(53, 4))->Select;
$book->Charts->Add;
# And format it.
my $chart= $book->ActiveChart;
$chart->{'ChartType'} = xlStockHLC;
$chart->{'HasLegend'} = msoFalse;
$chart->{'HasTitle'} = msoTrue;
# The description here fits ping response times.
$chart->ChartTitle->Characters->{'Text'} =
"Typical Maximum & Minimum Round Trip Times (msecs) between ".
localtime($start) ." and ". localtime($end);
$chart->PlotArea->Interior->{'ColorIndex'} = xlNone;
$chart->ChartGroups(1)->HiLoLines->Border->{'Weight'} = xlMedium;
$chart->PageSetup->{'LeftMargin'} = 5;
$chart->PageSetup->{'RightMargin'} = 5;
$chart->PageSetup->{'TopMargin'} = 5;
$chart->PageSetup->{'BottomMargin'} = 10;
$chart->PageSetup->{'Orientation'} = xlLandscape;
$chart->Axes(xlCategory)->TickLabels->Font->{'Size'} = 8;
$chart->Axes(xlValue)->TickLabels->Font->{'Size'} = 8;
# save and exit
$book->SaveAs($fout, xlWorkbookNormal);
$book->Close;
undef $xl;
}