source: trunk/cgi-bin/consistency-check.pl@ 405

#!/usr/bin/perl
# ipdb/cgi-bin/consistency-check.pl
# Does full check to see if the data in the db is consistent and complete.
###
# SVN revision info
# $Date: 2010-05-11 21:33:34 +0000 (Tue, 11 May 2010) $
# SVN revision $Rev: 402 $
# Last update by $Author: kdeugau $
###
# Copyright (C) 2004 - Kris Deugau

use DBI;
use MyIPDB;
use NetAddr::IP;

print "Content-type: text/plain\n\n";

($dbh,$errstr) = connectDB_My;
die $errstr if !$dbh;
# May as well.  We need a number of globals.
initIPDBGlobals($dbh);

print "Checking master containment...\n";

# First check - make sure ALL routes and allocated blocks are part
# of one of the master blocks.
print "  Checking routed blocks:";
$sth = $dbh->prepare("select cidr from routed");
$sth->execute;
$flag = '';
ROUTED: while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP $data[0];
  foreach $master (@masterblocks) {
    if ($master->contains($cidr)) { next ROUTED; }
  }
  print "\n    $cidr not mastered";
}
print "$flag done.\n";

# Next test:  All allocations must be part of a master.
print "  Checking allocations:";
$sth = $dbh->prepare("select cidr from allocations");
$sth->execute;
$flag = '';
ALLOCATED: while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP $data[0];
  foreach $master (@masterblocks) {
    if ($master->contains($cidr)) { next ALLOCATED; }
  }
  print "\n    $cidr not mastered";
  $flag = "\n ";
}
print "$flag done.\n";

# Next:  free blocks
print "  Checking freeblocks:";
$sth = $dbh->prepare("select cidr from freeblocks order by cidr");
$sth->execute;
$flag = '';
FREEBLOCK: while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP $data[0];
  foreach $master (@masterblocks) {
    if ($master->contains($cidr)) { next FREEBLOCK; }
  }
  print "\n    $cidr not mastered";
  $flag = "\n ";
}
print "$flag done.\n";

print "Done checking master containment.\n\n";

print "Checking pool containment...\n";

$sth = $dbh->prepare("select pool,ip from poolips order by ip");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  $pool = new NetAddr::IP $data[0];
  $ip = new NetAddr::IP $data[1];
  print "  IP $ip listed with incorrect pool $pool\n"
    if !$pool->contains($ip);
}
$sth = $dbh->prepare("select distinct pool from poolips order by pool");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  $sth2 = $dbh->prepare("select cidr from allocations where cidr='$data[0]'");
  $sth2->execute;
  print "  Pool $data[0] does not exist in allocations table\n"
    if (($sth2->fetchrow_array)[0] eq '');
}

print "Done checking pool containment.\n\n";

print "Checking block-alignment consistency...\n";

# Block alignment consistency:  All allocated+free blocks within a master
# must NOT overlap, and they must show no gaps.
# eg, if we have blocks:
#  master is 192.168.2.0/24
#  allocated 192.168.2.0/29, 192.168.2.8/29, 192.168.2.64/26
#  free 192.168.2.16/28, 192.168.2.32/27, 192.168.2.128/25
# then we're OK, but if any one of the allocated or free blocks is missing,
# something b0rked.

# (select cidr from allocations where cidr <<= '$master') union
#  (select cidr from freeblocks where cidr <<= '$master')
#  order by cidr

foreach $master (@masterblocks) {
  print "  Master $master:\n";
  $prev = $master;
  $sth = $dbh->prepare("(select network(cidr) as net, broadcast(cidr) as bcast ".
        "from allocations where cidr <<= '$master' and type not like '_c') ".
        "union (select network(cidr) as net, broadcast(cidr) as bcast ".
        "from freeblocks where cidr <<= '$master' and not (routed='c')) order by net");
  $sth->execute;

  while (@data = $sth->fetchrow_array) {
    $cur = new NetAddr::IP $data[0];

    if ($master->numeric == $prev->numeric) {
      # check if cur starts with master
      if ($cur->numeric > $prev->numeric) {
        print "    Gap from start of master $master to first block $cur\n";
      } elsif ($cur->numeric < $prev->numeric) {
        print "    BIG problem!  Current block $cur begins before master $master!\n";
      }
    } else {
      if ($cur->numeric < ($prev->numeric + 1)) {
        print "    Block ".$prev->network." overlaps block $cur\n";
      } elsif ($cur->numeric > ($prev->numeric + 1)) {
        print "    Gap between end of block ".$prev->network." and block $cur\n";
      }
    }

    $prev = $cur;
    $prev--;

  } # while (@data...)

  $cur--;
  $master--;
  if ($cur->numeric ne $master->numeric) {
    print "    Gap from $cur to end of master at $master\n";
  }
  $master++;
  print "  done $master.\n";
}

print "Done checking block alignment.\n\n";

print "Checking containment on container blocks...\n";
# First, we need a list of containers.
# Then, we check all of the contained blocks to see if they're within
# the proper container.
$sth = $dbh->prepare("select cidr from allocations where type like '_c' order by cidr");
$sth->execute;
$i=0;
while (@data = $sth->fetchrow_array) {
  $containers[$i++] = new NetAddr::IP $data[0];
}
print "  Checking general containment:";
$sth = $dbh->prepare("select cidr from allocations where type like '_r' order by cidr");
$sth->execute;
$flag = '';
CONTAINED: while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP $data[0];
  foreach $container (@containers) {
    next CONTAINED if $container->contains($cidr);
  }
  print "\n    $cidr not contained";
  $flag = "\n ";
}
print "$flag done.\n";
print "  Checking alignment:\n";
foreach $container (@containers) {
  print "    Container $container:\n";
  $prev = $container;
  $sth = $dbh->prepare("(select network(cidr) as net, broadcast(cidr) as bcast ".
        "from allocations where cidr <<= '$container' and type like '_r') ".
        "union (select network(cidr) as net, broadcast(cidr) as bcast ".
        "from freeblocks where cidr <<= '$container' and not (routed='y' or routed='n')) ".
        "order by net");
    $sth->execute;

  while (@data = $sth->fetchrow_array) {
    $cur = new NetAddr::IP $data[0];

    if ($container->numeric == $prev->numeric) {
      # check if cur starts with master
      if ($cur->numeric > $prev->numeric) {
        print "      Gap from start of container $container to first block $cur\n";
      } elsif ($cur->numeric < $prev->numeric) {
        print "      BIG problem!  Current block $cur begins before container $container!\n";
      }
    } else {
      if ($cur->numeric < ($prev->numeric + 1)) {
        print "      Block ".$prev->network." overlaps block $cur\n";
      } elsif ($cur->numeric > ($prev->numeric + 1)) {
        print "      Gap between end of block ".$prev->network." and block $cur\n";
      }
    }

    $prev = $cur;
    $prev--;

  } # while (@data...)

  $cur--;
  $container--;
  if ($cur->numeric ne $container->numeric) {
    print "      Gap from $cur to end of container at $container\n";
  }
  $container++;
  print "    done $container.\n";

}
print "  done container alignment.\n";
print "Done checking container containment.\n\n";

print "Checking for correctness on 'defined' CustIDs:\n";
# New check:  Make sure "defined" CustIDs are correct.
$sth = $dbh->prepare("select cidr,type,custid from allocations where not (type='cn' or type like '_r') order by cidr");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  print "$data[0] ($disp_alloctypes{$data[1]}) has incorrect CustID $data[2]\n"
        if $data[2] ne $def_custids{$data[1]};
}
print "Done predefined CustID correctness check.\n\n";

print "Checking for customer blocks with 'bad' CustIDs:\n";
# Make sure cn-type ("customer netblock") blocks have "real" CustIDs.
$sth = $dbh->prepare("select cidr,type,custid from allocations where type='cn' and (custid='6750400' or custid='STAFF') order by cidr");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  print "cn block $data[0] has incorrect CustID $data[2]\n";
}
print "Done checking customer blocks\n";