1#!/usr/local/bin/perl 2# 3# Program: report.pl 4# Author: Rob Beverly <rbeverly@users.sourceforge.net> 5# Adam Rothschild <asr@latency.net> 6# Date: August 13, 2002 7# Orig: October 1, 2001 8# Purpose: Generates RTG traffic report for a particular customer 9# 10 11use DBI; 12 13$cust = $ARGV[0]; 14shift(@ARGV); 15($startdate, $enddate) = getrange(@ARGV); 16 17die <<USAGE unless $cust && $startdate && $enddate; 18 19USAGE: $0 <customer> -[##d][##h][##m][##s] 20 OR: $0 <customer> <mm/dd/yyyy[+hh:mm[:ss]]> <mm/dd/yyyy[+hh:mm[:ss]]> 21USAGE 22 23$DEBUG = 0; 24 25# Begin with reasonable defaults 26$db="rtg"; 27$host="localhost"; 28$user="snmp"; 29$pass="rtgdefault"; 30$onedaysec=60*60*24; 31 32# Default locations to find RTG configuration file 33@configs = ("rtg.conf", "/usr/local/etc/rtg/rtg.conf", "/usr/local/rtg/etc/rtg.conf", "/etc/rtg.conf"); 34foreach $conf (@configs) { 35 if (open CONF, "<$conf") { 36 print "Reading [$conf].\n" if $DEBUG; 37 while ($line = <CONF>) { 38 @cVals = split /\s+/, $line; 39 if ($cVals[0] =~ /DB_Host/) { 40 $host=$cVals[1]; 41 } elsif ($cVals[0] =~ /DB_User/) { 42 $user=$cVals[1]; 43 } elsif ($cVals[0] =~ /DB_Pass/) { 44 $pass=$cVals[1]; 45 } elsif ($cVals[0] =~ /DB_Database/) { 46 $db=$cVals[1]; 47 } 48 } 49 last; 50 } 51} 52 53 54sub getrange { 55 my($param1, $param2) = @_; 56 57 if(!$param2) { # First format: -[[[##d]##h]##m]##s 58 return unless $param1 =~ /^-/; 59 $datediff = substr($param1, 1); 60 return unless $datediff =~ /^(([0-9]+)d)?(([0-9]+)h)?(([0-9]+)m)?(([0-9]+)s)?$/; 61 ($days,$hours,$minutes,$secs) = ($2,$4,$6,$8); 62 $endtime = time(); 63 $starttime = $endtime - (((((($days * 24) + $hours) * 60) + $minutes) * 60) + $secs); 64 $startdate = timestamp($starttime); 65 $enddate = timestamp($endtime); 66 } else { # Second format: <mm/dd/yyyy[+hh:mm[:ss]]> <mm/dd/yyyy[+hh:mm[:ss]]> 67 ($startdate = parsedate($param1)) || return; 68 ($enddate = parsedate($param2)) || return; 69 $startdate < $enddate || return; 70 } 71 72 return ($startdate, $enddate); 73} 74 75sub parsedate { 76 local($str) = @_; 77 78 $str =~ /^([0-9]{2})\/([0-9]{2})\/([0-9]{4})(\+([0-9]{2}):([0-9]{2})(:([0-9]{2}))?)?$/ || return; 79 ($month, $dd, $yyyy, $hh, $mm, $ss) = ($1, $2, $3, $5, $6, $8); 80 if(!$hh) { $hh = "00"; } 81 if(!$mm) { $mm = "00"; } 82 if(!$ss) { $ss = "00"; } 83 84 return "$yyyy$month$dd$hh$mm$ss"; 85} 86 87sub timestamp { 88 local ($time) = @_; 89 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time); 90 $mon += 1; 91 $year += 1900; 92 if($mday < 10) { $mday = "0".$mday; } 93 if($mon < 10) { $mon = "0".$mon; } 94 if($hour < 10) { $hour = "0".$hour; } 95 if($min < 10) { $min = "0".$min; } 96 if($sec < 10) { $sec = "0".$sec; } 97 return "$year$mon$mday$hour$min$sec"; 98} 99 100sub format_dt { 101 my($str) = @_; 102 $str =~ /^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$/; 103 return "$2/$3/$1 $4:$5:$6"; 104} 105 106sub commify { 107 local($_) = shift; 108 1 while s/^(-?\d+)(\d{3})/$1,$2/; 109 return $_; 110} 111 112sub today { 113 local($_) = shift; 114 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_); 115 $mon+=1; 116 $year+=1900; 117 if ($mday < 10) { $mday = "0".$mday; } 118 if ($mon < 10) { $mon = "0".$mon; } 119 @_ = ($mon,$mday,$year); 120 return(@_); 121} 122 123sub yesterday { 124 local($_) = shift; 125 $_-=86400; 126 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_); 127 $mon+=1; 128 $year+=1900; 129 if ($mday < 10) { $mday = "0".$mday; } 130 if ($mon < 10) { $mon = "0".$mon; } 131 @_ = ($mon,$mday,$year); 132 return(@_); 133} 134 135sub run_query { 136 # print "Query:", $statement, "\n"; 137 my $sth = $dbh->prepare($statement) 138 or die "Can't prepare $statement: $dbh->errstr\n"; 139 my $rv = $sth->execute 140 or die "can't execute the query: $sth->errstr\n"; 141 @row = $sth->fetchrow_array (); 142 $rc = $sth->finish; 143 if ($row[0] == "") { 144 $row[0] = 0; 145 } 146 return @row; 147} 148 149sub interface_stats{ 150 my $counter = $total = 0; 151 my $sample_secs = $last_sample_secs = 0; 152 my $num_rate_samples =0; 153 my $rate = $avgrate = $maxrate = 0; 154 my $max = $avg = 0; 155 156 my $sth = $dbh2->prepare($statement) 157 or die "Can't prepare $statement: $dbh2->errstr\n"; 158 my $rv = $sth->execute 159 or die "can't execute the query: $sth2->errstr\n"; 160 while (my @row = $sth->fetchrow_array()) { 161 ($counter, $sample_secs) = @row; 162 if ($last_sample_secs > $sample_secs || 163 $sample_secs - $last_sample_secs <= 0 || 164 $counter < 0) { 165 print "*** Bad Samples\n"; 166 print "*** count: $counter ss: $sample_secs lss: $last_sample_secs\n"; 167 print "*** stmt: $statement\n"; 168 } else { 169 $total += $counter; 170 if ($last_sample_secs != 0) { 171 $num_rate_samples++; 172 $rate = $counter*8/($sample_secs - $last_sample_secs); 173 $avgrate += $rate; 174 if ($rate > $maxrate) { $maxrate = $rate; } 175 } 176 $last_sample_secs = $sample_secs; 177 } 178 } 179 if ($num_rate_samples !=0) { 180 $avg = $avgrate/$num_rate_samples; 181 } 182 @_ = ($total,$maxrate,$avg); 183 return(@_); 184} 185 186 187($mon, $mday, $year) = &yesterday(time()); 188 189print "$cust Traffic\n"; 190print "Period: [", format_dt($startdate), " to ", format_dt($enddate), "]\n\n"; 191($router,$name,$bytesin, $bytesout, $ratein, $rateout, $utilin, $utilout, $maxratein, $maxrateout, $maxutilin, $maxutilout) = (" ", " ", "In", "Out", "Avg In", "Avg Out", "Util", "Util", "Max In", "Max Out", "Max Util", "Max Util"); 192write; 193($router,$name,$bytesin, $bytesout, $ratein, $rateout, $utilin, $utilout, $maxratein, $maxrateout, $maxutilin, $maxutilout) = ("","Connection", "MBytes", "MBytes", "Mbps", "Mbps", "In %", "Out%", "Mbps", "Mbps", "In%", "Out%"); 194write; 195print "------------------------------------------------------------------------------------------------------------------------------\n"; 196 197$dbh= DBI->connect("DBI:mysql:$db:host=$host", $user, $pass); 198$dbh2= DBI->connect("DBI:mysql:$db:host=$host", $user, $pass); 199$range="`dtime`>$startdate AND `dtime`<=$enddate"; 200 201$statement="SELECT `id` FROM `interface` WHERE `description` LIKE \"%$cust%\""; 202$sth = $dbh->prepare($statement) 203 or die "Can't prepare $statement: $dbh->errstr\n"; 204$rv = $sth->execute 205 or die "can't execute the query: $sth->errstr\n"; 206while (@row = $sth->fetchrow_array ()) 207{ 208 push(@interfaces, $row[0]); 209} 210 211foreach $interface (@interfaces) { 212 $statement="SELECT `rid`,`name`,`speed` FROM `interface` WHERE `id`=$interface"; 213 &run_query($statement); 214 ($rid, $name, $speed) = @row; 215 216 $statement="SELECT `rid`,`name` FROM `router` WHERE `rid`=$rid"; 217 &run_query($statement); 218 ($rid, $router) = @row; 219 220 $statement="SELECT `counter`,UNIX_TIMESTAMP(`dtime`),`dtime` FROM `ifInOctets_".$rid."` WHERE $range AND `id`=$interface ORDER BY `dtime`"; 221 ($intbytes_in, $maxin, $avgin) = &interface_stats($statement); 222 $bytesin = int($intbytes_in/1000000 + .5); 223 224 $statement="SELECT `counter`,UNIX_TIMESTAMP(`dtime`),`dtime` FROM `ifOutOctets_".$rid."` WHERE $range AND `id`=$interface ORDER BY `dtime`"; 225 ($intbytes_out, $maxout, $avgout) = &interface_stats($statement); 226 $bytesout = int($intbytes_out/1000000 + .5); 227 228 $ratein = sprintf("%2.2f", $avgin/1000000); 229 $rateout = sprintf("%2.2f", $avgout/1000000); 230 $maxratein = sprintf("%2.2f", $maxin/1000000); 231 $maxrateout = sprintf("%2.2f", $maxout/1000000); 232 if ($speed != 0) { 233 $utilin = sprintf("%2.2f", $ratein/($speed/1000000)*100); 234 $utilout = sprintf("%2.2f", $rateout/($speed/1000000)*100); 235 $maxutilin = sprintf("%2.2f", $maxratein/($speed/1000000)*100); 236 $maxutilout = sprintf("%2.2f", $maxrateout/($speed/1000000)*100); 237 } else { 238 $utilin = $utilout = $maxutilin = $maxutilout = 0; 239 } 240 241 write; 242 $totalbytesin += $bytesin; 243 $totalbytesout += $bytesout; 244 $totalratein += $ratein; 245 $totalrateout += $rateout; 246 $totalmaxratein += $maxratein; 247 $totalmaxrateout += $maxrateout; 248 $bytesin = $bytesout = 0; 249} 250 251print "\n"; 252$name = "Total:"; 253$router = ""; 254$bytesin = $totalbytesin; 255$bytesout = $totalbytesout; 256$ratein = $totalratein; 257$rateout = $totalrateout; 258$maxratein = $totalmaxratein; 259$maxrateout = $totalmaxrateout; 260$utilin = ""; 261$utilout = ""; 262$maxutilin = ""; 263$maxutilout = ""; 264 265write; 266$rc = $dbh->disconnect; 267exit; 268 269format STDOUT = 270@<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<< @>>>>>>> @>>>>>>> @>>>>>> @>>>>>> @>>>>> @>>>>> @>>>>>> @>>>>>> @>>>>> @>>>>> 271$name,$router,&commify($bytesin), &commify($bytesout), $ratein, $rateout, $utilin, $utilout, $maxratein, $maxrateout, $maxutilin, $maxutilout 272. 273