#!/usr/bin/perl -w
use DBI;
use DBD::mysql;
my $database="switch";
my $user="root";
my $password="stuffandthings";
$dbh = DBI->connect("DBI:mysql:$database", $user, $password);

my $date=`/bin/date -d -s +%Y-%m-%d`;
chomp $date;
my $switch;
my $string="manhatten";
#my $null='\N';
my @vlanarray=("1", "10", "100", "1005", "11", "12", "13", "15", "17", "18", "19", "2", "20", "21", "22", "23", "24", "28", "29", "3", "301", "302", "304", "305", "306", "307", "308", "309", "35", "36", "37", "38", "4", "40", "400", "4096", "50", "51", "52", "6", "8", "9", "99", "999");
my @switcharray=("cat-6506-a", "cat-corporate-a", "cat-corporate-b", "cat-corporate-c", "cat-corporate-d", "cat-4912-a", "cat-4912-b", "cat-4912-c", "cat-2924-1n", "cat-2924-1s", "cat-2924-2n", "cat2924-2s", "cat-2924-3n", "cat-2924-3s", "cat-2924-4n", "cat-2924-4s");
# these are the OIDs to walk the cam table, bridge, mapping, and interface names via snmp
my $camoid=".1.3.6.1.2.1.17.4.3.1.1";
my $bridgeoid=".1.3.6.1.2.1.17.4.3.1.2";
my $mappingoid=".1.3.6.1.2.1.17.1.4.1.2";
my $ifnameoid=".1.3.6.1.2.1.31.1.1.1.1";
my $snmpwalk="/usr/bin/snmpwalk -v 1";
my (@maclist,@iflist,@vlanlist);
open(HANDLE,">output.txt")||die "$!\n";
# put the results of the snmpwalk into an array and chomp
foreach my $switch (@switcharray){
  foreach my $vlan (@vlanarray){
    my @camtable=`$snmpwalk $switch -c "$string\@$vlan" $camoid`;
    chomp @camtable;
    my @bridgetable=`$snmpwalk $switch -c "$string\@$vlan" $bridgeoid`;
    chomp @bridgetable;
    my @mappingtable=`$snmpwalk $switch -c "$string\@$vlan" $mappingoid`;
    chomp @mappingtable;
    my @ifnametable=`$snmpwalk $switch -c "$string\@$vlan" $ifnameoid`;
    chomp @ifnametable;
  #    * From @camtable , we have:
#SNMPv2-SMI::mib-2.17.4.3.1.1.8.0.32.27.134.94 = Hex-STRING: 08 00 20 1B 86 5E
  #      .iso.3.6.1.2.1.17.4.3.1.1.0.0.12.56.22.105 = Hex 00 00 0C 38 16 69
  #
  #    * From @bridgetable, we have:
#SNMPv2-SMI::mib-2.17.4.3.1.2.8.0.32.27.134.94 = INTEGER: 1
  #      .iso.3.6.1.2.1.17.4.3.1.2.0.0.12.56.22.105 = 386
  #
  #      This tells you that this MAC address (00 00 0C 38 16 69) is from bridge port number 386.
  #    * From @mappingtable, we have:
  #      bridge port number 386 has an ifIndex number 298
#SNMPv2-SMI::mib-2.17.1.4.1.2.1 = INTEGER: 3
  #      .iso.3.6.1.2.1.17.1.4.1.2.386 = 298
  #
  #    * From @ifnametable , we have:
#IF-MIB::ifName.3 = STRING: 1/1
  #      ifIndex 298 corresponds to port 7/2
  #      .iso.3.6.1.2.1.31.1.1.1.1.298 = "7/2" Hex 37 2F 32
    foreach (@camtable){
      my $oidpart="17.4.3.1.1.0";
      if ($_=~/$oidpart(.*)\s=\s+Hex-STRING:\s(.*)/){
        my $oidremainder=$1;
        my $mac=lc($2);
        my @bridgenum=grep(/$oidremainder/,@bridgetable);
        if ($#bridgenum>0){
          print "error - there was more than one entry in the bridge table\n";
          exit;
        }
        else {
          if ($bridgenum[0]=~/$oidremainder\s=\sINTEGER:\s(\d+)/){
            my $bridge=$1;
            my @ifindex=grep(/\.$bridge\s=\sINTEGER:\s\d+/,@mappingtable);
            if ($ifindex[0]=~/\.$bridge\s=\sINTEGER:\s(\d+)/){
              my $if=$1;
              my @ifname=grep(/\.$if\s=\sSTRING:\s/,@ifnametable);
              if ($ifname[0]=~/\.$if\s=\sSTRING:\s(.*)/){
                my $interface=$1;
  # at this point we have all the information for one port, let's put that somewhere
  # I should use a hash, but mac addresses and interface names with slashes hardly
  # make ideal keys.
                push(@maclist,$mac);
		push(@vlanlist,$vlan);
                push(@iflist,$interface);
              }
            }
          }
          else {
            print "error - the bridgenum line didn't match the regular expression\n";
            exit;
          }
        }
      }
    }
    for(my $i=0;$i<=$#maclist;$i++){
      if($vlanlist[$i]==$vlan){
        print HANDLE "$switch\t$iflist[$i]\t$maclist[$i]\t$date\t$vlanlist[$i]\n";
      }
    }
  }
}
close HANDLE;
&importdata();
&cleanup();

sub importdata{
  my $sql="delete from cam;";
  my $sth=$dbh->prepare($sql);
  $sth->execute() || warn "**** SELECT ERROR **** $!";
  $sth->finish;
  $sql="load data local infile \"/home/ryan/output.txt\" into table cam;";
  $sth=$dbh->prepare($sql);
  $sth->execute() || warn "**** LOAD ERROR **** $!";
  $sth->finish;
}
sub cleanup{
  my $sql="create table nodupecam select distinct switch,port,mac,vlan from cam order by switch,port;";
  my $sth=$dbh->prepare($sql);
  $sth->execute() || warn "**** SELECT ERROR **** $!";
  $sth->finish;
  $sql="drop table cam;";
  $sth=$dbh->prepare($sql);
  $sth->execute() || warn "**** SELECT ERROR **** $!";
  $sth->finish;
  $sql="alter table nodupecam rename to cam;";
  $sth=$dbh->prepare($sql);
  $sth->execute() || warn "**** SELECT ERROR **** $!";
  $sth->finish;
}