#!/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";