| 1 | #!/usr/bin/perl
|
|---|
| 2 | # -T
|
|---|
| 3 | # ipdb/cgi-bin/extras/db2rwhois.pl
|
|---|
| 4 | # Pull data from ipdb and mangle it into RWHOIS
|
|---|
| 5 | # Initial version 03/26/2004 kdeugau against IPDB v1
|
|---|
| 6 | ###
|
|---|
| 7 | # Revision info
|
|---|
| 8 | # $Date: 2006-07-06 22:03:10 +0000 (Thu, 06 Jul 2006) $
|
|---|
| 9 | # SVN revision $Rev: 341 $
|
|---|
| 10 | # Last update by $Author: kdeugau $
|
|---|
| 11 | ###
|
|---|
| 12 | # Copyright (C) 2004,2005 - Kris Deugau
|
|---|
| 13 |
|
|---|
| 14 | use strict;
|
|---|
| 15 | use warnings;
|
|---|
| 16 | use DBI;
|
|---|
| 17 | use NetAddr::IP;
|
|---|
| 18 | use MyIPDB;
|
|---|
| 19 |
|
|---|
| 20 | $ENV{"PATH"} = "/bin;/usr/bin";
|
|---|
| 21 |
|
|---|
| 22 | my $rwhoisDataPath = "/etc/rwhoisd";
|
|---|
| 23 |
|
|---|
| 24 | my ($dbh,$msg) = connectDB_My;
|
|---|
| 25 |
|
|---|
| 26 | # For WHOIS purposes this may not be very useful. YMMV, we'll see.
|
|---|
| 27 | #initIPDBGlobals($dbh);
|
|---|
| 28 |
|
|---|
| 29 | my @masterblocks;
|
|---|
| 30 | my %netnameprefix;
|
|---|
| 31 | my %def_custids;
|
|---|
| 32 |
|
|---|
| 33 | # Fill in data about our master blocks as allocated from ARIN
|
|---|
| 34 | # We open separate files for each of these as appropriate.
|
|---|
| 35 | # Note that this ASS-U-MEs that we do not add master IP blocks-
|
|---|
| 36 | # there should probably be a separate system for doing that.
|
|---|
| 37 | my $sth = $dbh->prepare("select cidr,ctime,mtime from masterblocks;");
|
|---|
| 38 | $sth->execute;
|
|---|
| 39 | my $i=0;
|
|---|
| 40 | GETMASTERS: while (my @data = $sth->fetchrow_array()) {
|
|---|
| 41 |
|
|---|
| 42 | # Techically, we only need to exclude 204.138.172.0/24, as we "own" all of the other blocks.
|
|---|
| 43 | # However, 205.207.184.0/23 and 206.130.64.0/24 are, um, awkward.
|
|---|
| 44 | if ($data[0] =~ /^(192.168.0.0|172.16.0.0|10.0.0.0|20[456])/) {
|
|---|
| 45 | next GETMASTERS;
|
|---|
| 46 | }
|
|---|
| 47 | $masterblocks[$i] = new NetAddr::IP $data[0];
|
|---|
| 48 | my ($ctime,undef) = split /\s/, $data[1];
|
|---|
| 49 | my ($mtime,undef) = split /\s/, $data[2];
|
|---|
| 50 | print "$masterblocks[$i] $ctime $mtime\n";
|
|---|
| 51 |
|
|---|
| 52 | my $date;
|
|---|
| 53 | chomp ($date = `/bin/date +"%Y-%m-%d"`);
|
|---|
| 54 |
|
|---|
| 55 | # Whew! Ugly little varmint.
|
|---|
| 56 | my $masterfilename = "net-".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.
|
|---|
| 57 | "/data/network/".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.".txt";
|
|---|
| 58 |
|
|---|
| 59 | # Need check here to create tree for netblock?
|
|---|
| 60 |
|
|---|
| 61 | open MASTERFILE,">$rwhoisDataPath/$masterfilename";
|
|---|
| 62 |
|
|---|
| 63 | print MASTERFILE "ID: NETBLK-ISP.$masterblocks[$i]\n".
|
|---|
| 64 | "Auth-Area: $masterblocks[$i]\n".
|
|---|
| 65 | "Network-Name: ISP-".$masterblocks[$i]->network."\n".
|
|---|
| 66 | "IP-Network: $masterblocks[$i]\n".
|
|---|
| 67 | "IP-Network-Block: ".$masterblocks[$i]->range."\n".
|
|---|
| 68 | "Org-Name: Friendly ISP\n".
|
|---|
| 69 | "Street-Address: 123 4th Street\n".
|
|---|
| 70 | "City: Anytown\n".
|
|---|
| 71 | "StateProv: Ontario\n".
|
|---|
| 72 | "Postal-Code: H0H 0H0\n".
|
|---|
| 73 | "Country-Code: CA\n".
|
|---|
| 74 | "Tech-Contact: ISP-ARIN-HANDLE\n".
|
|---|
| 75 | "Created: $ctime\n".
|
|---|
| 76 | "Updated: $mtime\n".
|
|---|
| 77 | "Updated-By: noc\@example.com\n";
|
|---|
| 78 |
|
|---|
| 79 | close MASTERFILE;
|
|---|
| 80 | $i++;
|
|---|
| 81 | }
|
|---|
| 82 |
|
|---|
| 83 | # prefetch alloctype data
|
|---|
| 84 | $sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes");
|
|---|
| 85 | $sth->execute;
|
|---|
| 86 | while (my @data = $sth->fetchrow_array) {
|
|---|
| 87 | $netnameprefix{$data[0]} = $data[2];
|
|---|
| 88 | $def_custids{$data[0]} = $data[1];
|
|---|
| 89 | }
|
|---|
| 90 |
|
|---|
| 91 | # Now read out the data in the "main" delegation list, and check it
|
|---|
| 92 | # with the master blocks. We need to do this to decide which rWHOIS
|
|---|
| 93 | # "net-xxx.xxx.xxx.xxx-mask" tree the data belongs in.
|
|---|
| 94 |
|
|---|
| 95 | # Make sure to remove the private netblocks from this.
|
|---|
| 96 | # No use or point in broadcasting our use of them.
|
|---|
| 97 | # Also remove the details of our "reserved CORE/WAN" blocks; they're not critical.
|
|---|
| 98 | $sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ".
|
|---|
| 99 | "from allocations where ".
|
|---|
| 100 | "not (cidr <<= '192.168.0.0/16') and ".
|
|---|
| 101 | "not (cidr <<= '172.16.0.0/12') and ".
|
|---|
| 102 | "not (cidr <<= '10.0.0.0/8') and ".
|
|---|
| 103 | "not (type = 'wr') and ".
|
|---|
| 104 | "masklen(cidr) <=30");
|
|---|
| 105 | #" and (custid='6750400' or custid like '%-RES' or custid like '%-BUS')");
|
|---|
| 106 | $sth->execute;
|
|---|
| 107 |
|
|---|
| 108 | my $custsth = $dbh->prepare("select name,street,city,province,country,pocode,phone,tech_handle,special from customers where custid=?");
|
|---|
| 109 |
|
|---|
| 110 | $i=0;
|
|---|
| 111 | while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $sth->fetchrow_array) {
|
|---|
| 112 |
|
|---|
| 113 | # We get master block info from @masterblocks.
|
|---|
| 114 | # ID: NETBLK-ISP.10.0.0.0/8
|
|---|
| 115 | # Auth-Area: 10.0.0.0/8
|
|---|
| 116 | # Network-Name: ISP-10.0.2.144
|
|---|
| 117 | # IP-Network: 10.0.2.144.144/29
|
|---|
| 118 | # IP-Network-Block: 10.0.2.144 - 10.0.2.151
|
|---|
| 119 | # Organization: WidgetCorp
|
|---|
| 120 | # Tech-Contact: bob@widgetcorp.com
|
|---|
| 121 | # Admin-Contact: ISP-ARIN-HANDLE
|
|---|
| 122 | # Created: 20040314
|
|---|
| 123 | # Updated: 20040314
|
|---|
| 124 | # Updated-By: noc@example.com
|
|---|
| 125 |
|
|---|
| 126 | # Get the "full" network number
|
|---|
| 127 | my $net = new NetAddr::IP $cidr;
|
|---|
| 128 |
|
|---|
| 129 | # Assumptions: All data in ipdb is public
|
|---|
| 130 | # If not, we need another field to indicate "public/private".
|
|---|
| 131 |
|
|---|
| 132 | foreach my $master (@masterblocks) {
|
|---|
| 133 | if ($master->contains($net)) {
|
|---|
| 134 |
|
|---|
| 135 | # Whew! Ugly little varmint.
|
|---|
| 136 | my $masterfilename = "net-".$master->addr."-".$master->masklen.
|
|---|
| 137 | "/data/network/".$master->addr."-".$master->masklen.".txt";
|
|---|
| 138 |
|
|---|
| 139 | open MASTERFILE,">>$rwhoisDataPath/$masterfilename"
|
|---|
| 140 | or print "File open error: '$!' on '$rwhoisDataPath/$masterfilename'\n";
|
|---|
| 141 |
|
|---|
| 142 | # cidr custid type city description notes maskbits
|
|---|
| 143 |
|
|---|
| 144 | # Fill in a generic entry for nameless allocations
|
|---|
| 145 | if ($desc =~ /^\s*$/) { $desc = 'Friendly ISP'; }
|
|---|
| 146 |
|
|---|
| 147 | # Fix up datestamps. We don't *really* need sub-microsecond resolution on our exports...
|
|---|
| 148 | ($ctime) = ($ctime =~ /^(\d+-\d+-\d+)\s+/);
|
|---|
| 149 | ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/);
|
|---|
| 150 |
|
|---|
| 151 | # Notes:
|
|---|
| 152 | # Network-name should contain some component of "description"
|
|---|
| 153 | # Cust address/contact data should be included; NB, no phone for ARIN!
|
|---|
| 154 | # network:ID: NET-WIDGET
|
|---|
| 155 | # network:Network-Name: WIDGET [IPDB description, sort of]
|
|---|
| 156 | # network:IP-Network: 10.1.1.0/24
|
|---|
| 157 | # network:Org-Name: Widget Corp [Cust name; from billing?]
|
|---|
| 158 | # network:Street-Address: 211 Oak Drive [May need more than one line, OR...]
|
|---|
| 159 | # network:City: Pineville [...this line...]
|
|---|
| 160 | # network:StateProv: WI [...and this line...]
|
|---|
| 161 | # network:Postal-Code: 48888 [...and this line]
|
|---|
| 162 | # network:Country-Code: US
|
|---|
| 163 | # network:Tech-Contact: BZ142-MYRWHOIS [ARIN handle?]
|
|---|
| 164 | # network:Updated: 19991221 [timestamp from db]
|
|---|
| 165 | # network:Updated-By: jo@myrwhois.net [noc@example, since that's our POC for IP netspace issues]
|
|---|
| 166 | # network:Class-Name:network [Provided by rWHOIS protocol]
|
|---|
| 167 |
|
|---|
| 168 | my $netname = $netnameprefix{$type};
|
|---|
| 169 |
|
|---|
| 170 | if ($swip eq 'y' && $def_custids{$type} eq '') {
|
|---|
| 171 | # def_custids{$type} should only be blank on types that are
|
|---|
| 172 | # allocated direct to customers. i are sMart!!11!!oneone!
|
|---|
| 173 | $custsth->execute($custid);
|
|---|
| 174 | my ($name, $street, $city, $prov, $country, $pocode, $phone, $tech, $special) = $custsth->fetchrow_array;
|
|---|
| 175 | $custsth->finish;
|
|---|
| 176 | if ($special && $special =~ /NetName/ && $special =~ /$cidr/) {
|
|---|
| 177 | ($netname) = ($special =~ /NetName$cidr: ([A-Z0-9_-]+)/);
|
|---|
| 178 | } else {
|
|---|
| 179 | $netname .= "-".$net->network;
|
|---|
| 180 | }
|
|---|
| 181 | print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
|
|---|
| 182 | "Auth-Area: $master\n".
|
|---|
| 183 | "Network-Name: $netname\n".
|
|---|
| 184 | "IP-Network: $net\n".
|
|---|
| 185 | "IP-Network-Block: ".$net->range."\n".
|
|---|
| 186 | "Org-Name: $name\n".
|
|---|
| 187 | "Street-Address: $street\n".
|
|---|
| 188 | "City: $city\n".
|
|---|
| 189 | "StateProv: $prov\n".
|
|---|
| 190 | "Postal-Code: $pocode\n".
|
|---|
| 191 | "Country-Code: $country\n".
|
|---|
| 192 | "Tech-Contact: $tech\n".
|
|---|
| 193 | "Created: $ctime\n".
|
|---|
| 194 | "Updated: $mtime\n".
|
|---|
| 195 | "Updated-By: noc\@example.com\n";
|
|---|
| 196 | } else {
|
|---|
| 197 | print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
|
|---|
| 198 | "Auth-Area: $master\n".
|
|---|
| 199 | "Network-Name: $netname-".$net->network."\n".
|
|---|
| 200 | "IP-Network: $net\n".
|
|---|
| 201 | "IP-Network-Block: ".$net->range."\n".
|
|---|
| 202 | "Org-Name: Friendly ISP\n".
|
|---|
| 203 | "Street-Address: 123 4th Street\n".
|
|---|
| 204 | "City: Anytown\n".
|
|---|
| 205 | "StateProv: Ontario\n".
|
|---|
| 206 | "Postal-Code: H0H 0H0\n".
|
|---|
| 207 | "Country-Code: CA\n".
|
|---|
| 208 | "Tech-Contact: ISP-ARIN-HANDLE\n".
|
|---|
| 209 | "Created: $ctime\n".
|
|---|
| 210 | "Updated: $mtime\n".
|
|---|
| 211 | "Updated-By: noc\@example.com\n";
|
|---|
| 212 | } # decide which contact data to use (cust data only on swip==y && def custid=='')
|
|---|
| 213 | } # net in master
|
|---|
| 214 | } # foreach master
|
|---|
| 215 |
|
|---|
| 216 |
|
|---|
| 217 |
|
|---|
| 218 | # print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ".
|
|---|
| 219 | # "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\t| $data[9]\n";
|
|---|
| 220 | # print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ".
|
|---|
| 221 | # "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\n";
|
|---|
| 222 | $i++;
|
|---|
| 223 | } # while fetchrow_array()
|
|---|
| 224 |
|
|---|
| 225 |
|
|---|
| 226 | $dbh->disconnect;
|
|---|
![[ DeepNet IP Database ]](/images/ipdb-logo.png){kind=logo}
