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