Updated Wireless Client Tracking

Some of the comments in my older post about RRDTool Wireless Graphing asked for detailed graph creation code. Below is what I use for generating three types of graphs. 1. A single record graph. 2. An Access Point graph with multiple clients displayed. 3. A composite graph that includes all the access points the client has associated to.

First, I query our wireless controller and save an RRD database for each client/ap association. I use an HTML and perl based web page for the user interface. I also save the records to a database in order to query for the most recent records. I use datatables to display the records. And some jqueryui buttons to limit the search filtering.

When the user clicks a row in datatables, the script below processes the request and creates the graphs. The other graphs besides daily are hidden by default, but a click of the button will swap the images. Also, using daterangepicker the user can select a specific range of dates.

Here is an example of the results. This gives us a nice visual of clients moving from one access point to another.

print "Content-type:text/html\n\n";
use Win32::ODBC;
use CGI;
use CGI::Carp qw( fatalsToBrowser );
use CGI qw/:standard/;
use RRDs;

my $GraphType = param('GraphType');
my $ApName = param('ApName');
my $ClientMac = param('ClientMac');
#print "
GraphType: $GraphType";
#print "
ApName: $ApName";
#print "
ClientMac: $ClientMac";

my $dateRange=param('dateRange');
#print $dateRange;
my $startDate;
my $endDate;

my $image_path='../../Charts'; 
my $web_image_path='/Charts';
my $stats_path='../../Stats';
my $stats_file="2gwlan_".$ClientMac."_".$ApName.".rrd";
my $random_number=time();

my $AreaColor = '54EC48';
my $LineColor = '24BC14';
my @DEF;
my $counter = 0;

if ($dateRange=~/^$/) {
 & create_def;
 & daily;
 & weekly;
 & monthly;
 & yearly;
}
else {
 if ($dateRange=~/(\d\d)\-(\d\d)\-(\d\d\d\d)\s\-\s(\d\d)\-(\d\d)\-(\d\d\d\d)/) {
  $startDate = "$3$1$2";
  $endDate = "$6$4$5";
 }
 elsif ($dateRange=~/^(\d\d)\-(\d\d)\-(\d\d\d\d)$/) {
  $endDate = "$3$1$2";
  my $year = $3;
  my $month = $1;
  my $day = $2;
  use Date::Calc qw(Add_Delta_Days); 
  ($year, $month, $day) = Add_Delta_Days($year, $month, $day, -1);
  $month = "0$month" if $month < 10;
  $day = "0$day" if $day < 10;
  $startDate = "$year$month$day";
  #print "SD:$startDate";
 }
 & create_def;
 & custom; 
}


############## create_def ################
sub create_def {
 my $query;
 if ($GraphType=~/Record/) {
  #Get Single Record
  & save_def;
 }
 else {
  if ($GraphType=~/Client/) {
   #Get top 6 most recent client records
   $query = "SELECT * FROM ClientDetails WHERE ClientMac LIKE '%$ClientMac%' ORDER BY modified DESC";
  }
  elsif ($GraphType=~/AP/) {
   #Get top 6 most recent AP records
   $query = "SELECT * FROM ClientDetails WHERE ApName LIKE '%$ApName%' ORDER BY modified DESC";
  }

  my($find_record_db) = new Win32::ODBC('DB') || die "Error connecting:" .Win32::ODBC::Error();
  $find_record_db->Sql("$query");
  while ($find_record_db->FetchRow()) {
   my(%data) = $find_record_db->DataHash();
   $ApName = $data{'ApName'};
   $ClientMac = $data{'ClientMac'};
   $stats_file="2gwlan_".$ClientMac."_".$ApName.".rrd";
   & save_def;
   last if $counter == 7;
  }
 }
}
#####################################


############## save_def ################
sub save_def {
 $AreaColor = "54EC48" if $counter == 0;
 $LineColor = "24BC14" if $counter == 0;
 $AreaColor = "EA644A" if $counter == 1;
 $LineColor = "CC3118" if $counter == 1;
 $AreaColor = "DE48EC" if $counter == 2;
 $LineColor = "B415C7" if $counter == 2;
 $AreaColor = "7648EC" if $counter == 3;
 $LineColor = "4D18E4" if $counter == 3;
 $AreaColor = "ECD748" if $counter == 4;
 $LineColor = "C9B215" if $counter == 4;
 $AreaColor = "48C4EC" if $counter == 5;
 $LineColor = "1598C3" if $counter == 5;
 $AreaColor = "EC9D48" if $counter == 6;
 $LineColor = "CC7016" if $counter == 6;
 
 push (@DEF,"DEF:DS$counter=$stats_path/$stats_file:snr:AVERAGE:step=300");
 push (@DEF,"AREA:DS$counter#$AreaColor:") if $GraphType!~/AP/;
 push (@DEF,"LINE2:DS$counter#$LineColor:$ApName $ClientMac \\t");
 push (@DEF,"GPRINT:DS$counter:LAST:Last\\: %1.lf");
 push (@DEF,"GPRINT:DS$counter:MIN:Min\\: %1.lf");
 push (@DEF,"GPRINT:DS$counter:MAX:Max\\: %1.lf");
 push (@DEF,"GPRINT:DS$counter:AVERAGE:Avg\\: %1.lf\\n");
 $counter++;
}
#####################################


############## daily ################
sub daily {
 $graphlength="-24 hours";
 RRDs::graph ("$image_path/my_2gwlan_stats_daily_graph.png",
  "-s $graphlength",
  "-t 24 Hour Wireless Stats",
  "-v SNR",
  "--height=250",
  "--width=800",
  "--slope-mode",
  @DEF,
 );

 if ($ERROR = RRDs::error) {print "$0: unable to generate graph: $ERROR\n";}

 print "
  
";
}

#################################################


############## weekly ################
sub weekly {
 $graphlength="-168 hours";
 RRDs::graph ("$image_path/my_2gwlan_stats_weekly_graph.png",
  "-s $graphlength",
  "-t Weekly Wireless Stats",
  "-v SNR",
  "--height=250",
  "--width=800",
  "--slope-mode",
  @DEF,
 );

 if ($ERROR = RRDs::error) {print "$0: unable to generate graph: $ERROR\n";}

 print "
  
";
}

#################################################


############## monthly ################
sub monthly {
 $graphlength="-720 hours";
 RRDs::graph ("$image_path/my_2gwlan_stats_monthly_graph.png",
  "-s $graphlength",
  "-t Monthly Wireless Stats",
  "-v SNR",
  "--height=250",
  "--width=800",
  "--slope-mode",
  @DEF,
 );

 if ($ERROR = RRDs::error) {print "$0: unable to generate graph: $ERROR\n";}

 print "
  
";
}

#################################################


############## yearly ################
sub yearly {
 $graphlength="-8760 hours";
 RRDs::graph ("$image_path/my_2gwlan_stats_yearly_graph.png",
  "-s $graphlength",
  "-t Yearly Wireless Stats",
  "-v SNR",
  "--height=250",
  "--width=800",
  "--slope-mode",
  @DEF,
 );

 if ($ERROR = RRDs::error) {print "$0: unable to generate graph: $ERROR\n";}

 print "
  
";
}

#################################################


############## custom ################
sub custom {
 RRDs::graph ("$image_path/my_2gwlan_stats_custom_graph.png",
  #"-s 20110819",
  #"-e 20110822",
  "-s $startDate",
  "-e $endDate",
  "-t $startDate - $endDate Wireless Stats",
  "-v SNR",
  "--height=250",
  "--width=800",
  "--slope-mode",
  @DEF,
 );

 if ($ERROR = RRDs::error) {print "$0: unable to generate graph: $ERROR\n";}

 print "
  
";
}
#################################################