source: trunk/cgi-bin/extras/db2rwhois.pl@ 324

#!/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 19:47:21 +0000 (Thu, 06 Apr 2006) $
# SVN revision $Rev: 324 $
# 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 where listorder <500");
$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;