#!/usr/bin/perl # -T # ipdb/cgi-bin/extras/db2rwhois.pl # Pull data from ipdb and mangle it into RWHOIS # Initial version 03/26/2004 kdeugau against IPDB v1 ### # Revision info # $Date: 2006-04-06 20:27:26 +0000 (Thu, 06 Apr 2006) $ # SVN revision $Rev: 326 $ # Last update by $Author: kdeugau $ ### # Copyright (C) 2004,2005 - Kris Deugau use strict; use warnings; use DBI; use NetAddr::IP; use MyIPDB; $ENV{"PATH"} = "/bin;/usr/bin"; my $rwhoisDataPath = "/etc/rwhoisd"; my ($dbh,$msg) = connectDB_My; # For WHOIS purposes this may not be very useful. YMMV, we'll see. #initIPDBGlobals($dbh); my @masterblocks; my %netnameprefix; # Fill in data about our master blocks as allocated from ARIN # We open separate files for each of these as appropriate. # Note that this ASS-U-MEs that we do not add master IP blocks- # there should probably be a separate system for doing that. my $sth = $dbh->prepare("select cidr,ctime,mtime from masterblocks;"); $sth->execute; my $i=0; GETMASTERS: while (my @data = $sth->fetchrow_array()) { # Techically, we only need to exclude 204.138.172.0/24, as we "own" all of the other blocks. # However, 205.207.184.0/23 and 206.130.64.0/24 are, um, awkward. if ($data[0] =~ /^(192.168.0.0|172.16.0.0|10.0.0.0|20[456])/) { next GETMASTERS; } $masterblocks[$i] = new NetAddr::IP $data[0]; my ($ctime,undef) = split /\s/, $data[1]; my ($mtime,undef) = split /\s/, $data[2]; print "$masterblocks[$i] $ctime $mtime\n"; my $date; chomp ($date = `/bin/date +"%Y-%m-%d"`); # Whew! Ugly little varmint. my $masterfilename = "net-".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen. "/data/network/".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.".txt"; # Need check here to create tree for netblock? open MASTERFILE,">$rwhoisDataPath/$masterfilename"; print MASTERFILE "ID: NETBLK-ISP.$masterblocks[$i]\n". "Auth-Area: $masterblocks[$i]\n". "Network-Name: ISP-".$masterblocks[$i]->network."\n". "IP-Network: $masterblocks[$i]\n". "IP-Network-Block: ".$masterblocks[$i]->range."\n". "Org-Name: Friendly ISP\n". "Street-Address: 123 4th Street\n". "City: Anytown\n". "StateProv: Ontario\n". "Postal-Code: H0H 0H0\n". "Country-Code: CA\n". "Tech-Contact: ISP-ARIN-HANDLE\n". "Created: $ctime\n". "Updated: $mtime\n". "Updated-By: noc\@example.com\n"; close MASTERFILE; $i++; } # prefetch alloctype data $sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes"); $sth->execute; while (my @data = $sth->fetchrow_array) { $netnameprefix{$data[0]} = $data[2]; } # Now read out the data in the "main" delegation list, and check it # with the master blocks. We need to do this to decide which rWHOIS # "net-xxx.xxx.xxx.xxx-mask" tree the data belongs in. # Make sure to remove the private netblocks from this. # No use or point in broadcasting our use of them. # Also remove the details of our "reserved CORE/WAN" blocks; they're not critical. $sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ". "from allocations where ". "not (cidr <<= '192.168.0.0/16') and ". "not (cidr <<= '172.16.0.0/12') and ". "not (cidr <<= '10.0.0.0/8') and ". "not (type = 'wr') and ". "masklen(cidr) <=30"); #" and (custid='6750400' or custid like '%-RES' or custid like '%-BUS')"); $sth->execute; my $custsth = $dbh->prepare("select name,street,city,province,country,pocode,phone,tech_handle,special from customers where custid=?"); $i=0; while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $sth->fetchrow_array) { # We get master block info from @masterblocks. # ID: NETBLK-ISP.10.0.0.0/8 # Auth-Area: 10.0.0.0/8 # Network-Name: ISP-10.0.2.144 # IP-Network: 10.0.2.144.144/29 # IP-Network-Block: 10.0.2.144 - 10.0.2.151 # Organization: WidgetCorp # Tech-Contact: bob@widgetcorp.com # Admin-Contact: ISP-ARIN-HANDLE # Created: 20040314 # Updated: 20040314 # Updated-By: noc@example.com # Get the "full" network number my $net = new NetAddr::IP $cidr; # Assumptions: All data in ipdb is public # If not, we need another field to indicate "public/private". foreach my $master (@masterblocks) { if ($master->contains($net)) { # Whew! Ugly little varmint. my $masterfilename = "net-".$master->addr."-".$master->masklen. "/data/network/".$master->addr."-".$master->masklen.".txt"; open MASTERFILE,">>$rwhoisDataPath/$masterfilename" or print "File open error: '$!' on '$rwhoisDataPath/$masterfilename'\n"; # cidr custid type city description notes maskbits # Fill in a generic entry for nameless allocations if ($desc =~ /^\s*$/) { $desc = 'Friendly ISP'; } # Fix up datestamps. We don't *really* need sub-microsecond resolution on our exports... ($ctime) = ($ctime =~ /^(\d+-\d+-\d+)\s+/); ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/); # Notes: # Network-name should contain some component of "description" # Cust address/contact data should be included; NB, no phone for ARIN! # network:ID: NET-WIDGET # network:Network-Name: WIDGET [IPDB description, sort of] # network:IP-Network: 10.1.1.0/24 # network:Org-Name: Widget Corp [Cust name; from billing?] # network:Street-Address: 211 Oak Drive [May need more than one line, OR...] # network:City: Pineville [...this line...] # network:StateProv: WI [...and this line...] # network:Postal-Code: 48888 [...and this line] # network:Country-Code: US # network:Tech-Contact: BZ142-MYRWHOIS [ARIN handle?] # network:Updated: 19991221 [timestamp from db] # network:Updated-By: jo@myrwhois.net [noc@example, since that's our POC for IP netspace issues] # network:Class-Name:network [Provided by rWHOIS protocol] my $netname = $netnameprefix{$type}; if ($swip eq 'n') { print MASTERFILE "---\nID: NETBLK-ISP.$master\n". "Auth-Area: $master\n". "Network-Name: $netname-".$net->network."\n". "IP-Network: $net\n". "IP-Network-Block: ".$net->range."\n". "Org-Name: Friendly ISP\n". "Street-Address: 123 4th Street\n". "City: Anytown\n". "StateProv: Ontario\n". "Postal-Code: H0H 0H0\n". "Country-Code: CA\n". "Tech-Contact: ISP-ARIN-HANDLE\n". "Created: $ctime\n". "Updated: $mtime\n". "Updated-By: noc\@example.com\n"; } else { $custsth->execute($custid); my ($name, $street, $city, $prov, $country, $pocode, $phone, $tech, $special) = $custsth->fetchrow_array; $custsth->finish; if ($special && $special =~ /NetName/ && $special =~ /$cidr/) { ($netname) = ($special =~ /NetName$cidr: ([A-Z0-9_-]+)/); } else { $netname .= "-".$net->network; } print MASTERFILE "---\nID: NETBLK-ISP.$master\n". "Auth-Area: $master\n". "Network-Name: $netname\n". "IP-Network: $net\n". "IP-Network-Block: ".$net->range."\n". "Org-Name: $name\n". "Street-Address: $street\n". "City: $city\n". "StateProv: $prov\n". "Postal-Code: $pocode\n". "Country-Code: $country\n". "Tech-Contact: $tech\n". "Created: $ctime\n". "Updated: $mtime\n". "Updated-By: noc\@example.com\n"; } # swip } # net in master } # foreach master # print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ". # "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\t| $data[9]\n"; # print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ". # "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\n"; $i++; } # while fetchrow_array() $dbh->disconnect;