switch-ztp/scripts/importsw.pl

#!/usr/bin/perl

use strict;
use DBI;
use vars qw($PID_3560 $PID_3750 $DB_USER $DB_PASS);
require 'dbi.ph';

sub trim($) {
        my ($str) = shift;

        $str //= '';

        $str =~ s/^\s+//g;
        $str =~ s/\s+$//g;

        return $str;
}

$PID_3560 = 'WS-C3560CG-8PC-S';
$PID_3750 = 'WS-C3750X';

my $dsn = "DBI:mysql:database=noc";

my $dbh = DBI->connect($dsn, $DB_USER, $DB_PASS, {PrintError => 0})
    or die "ERROR: Failed to connect to the database.";

open(IN, "/home/jclarke/invsw.csv")
    or die "ERROR: Failed to open /home/jclarke/invsw.csv for reading.";

my @contents = <IN>;
close(IN);
my $header = $contents[0];
chomp $header;
my @elements = split(/,/, $header);
for (my $i = 1 ; $i < scalar(@contents) ; $i++) {
        my %field = (
                'Hostname' => '',
                'Ports'    => '',
                'Serial'   => '',
                'Location' => '',
                'MDF'      => '',
                'IDF'      => '',
                'Template' => '',
                'IP'       => '',
        );

        my $line = $contents[$i];
        chomp $line;
        my @fields = split(/,/, $line);
        for (my $j = 0 ; $j < scalar(@elements) ; $j++) {
                $field{$elements[$j]} = trim($fields[$j]);
        }

        my $query = '';
        my ($sth, $res, $ref, @params);

        if ($field{'Hostname'} ne '') {
                if ($field{'IP'} ne '') {
                        $query =
                            'SELECT address FROM ADDRESSES WHERE address = ?';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR
                                    "ERROR: Failed to prepare query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($field{'IP'});
                        $res    = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "ERROR: Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        $ref = $sth->fetchrow_hashref;
                        if (!$ref) {
                                print STDERR
                                    "ERROR: No matching IP address record for $field{'IP'}\n";
                                print STDERR "XXX: Line is '$line'\n";
                                next;
                        }
                        $query =
                            'SELECT name FROM SWITCHES WHERE ip_address = ? AND name != ?';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR "Failed to prepare '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($ref->{'address'}, $field{'Hostname'});
                        $res = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        if ($sth->rows != 0) {
                                my $r = $sth->fetchrow_hashref;
                                print STDERR
                                    "Duplicate IP detected for IP $field{'IP'}, hostname $field{'Hostname'} (current device is "
                                    . $r->{'name'} . ")\n";
                                $query =
                                    'UPDATE ADDRESSES SET used=? WHERE address=?';
                                $sth = $dbh->prepare($query);
                                if (!$sth) {
                                        print STDERR
                                            "Failed to prepare query '$query': "
                                            . $dbh->errstr . "\n";
                                        next;
                                }
                                @params = ('1', $field{'IP'});
                                $res = $sth->execute(@params);
                                if (!$res) {
                                        print STDERR
                                            "Failed to execute query '$query': "
                                            . $dbh->errstr . "\n";
                                        next;
                                }
                                next;
                        }
                        $field{'IP'} = $ref->{'address'};
                } else {
                        $query =
                            'SELECT address FROM ADDRESSES WHERE location=? AND used=? LIMIT 1';
                        $query =
                            'SELECT ip_address FROM SWITCHES WHERE name = ?';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR
                                    "Failed to prepare query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($field{'IDF'});
                        $res    = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        my $r = $sth->fetchrow_hashref;
                        my ($subnet) =
                            ($r->{'ip_address'} =~ /^(\d+\.\d+\.\d+\.)/);
                        $query =
                            'SELECT address FROM ADDRESSES WHERE location = ? AND address LIKE ? AND used = ? AND reserved = ? ORDER BY INET_ATON(address) LIMIT 1';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR
                                    "Failed to prepare query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($field{'MDF'}, $subnet . '%', '0', '0');
                        $res = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        $r = $sth->fetchrow_hashref;
                        $field{'IP'} = $r->{'address'};
                }
                $query = 'UPDATE ADDRESSES SET used=? WHERE address=?';
                $sth   = $dbh->prepare($query);
                if (!$sth) {
                        print STDERR "Failed to prepare query '$query': "
                            . $dbh->errstr . "\n";
                        next;
                }
                @params = ('1', $field{'IP'});
                $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to execute query '$query': "
                            . $dbh->errstr . "\n";
                        next;
                }
                $query =
                    'UPDATE DEVICE_MAP SET assigned_switch = NULL, checked_out=? WHERE assigned_switch = ?';
                $sth = $dbh->prepare($query);
                if (!$sth) {
                        print STDERR "Failed to prepare query '$query': "
                            . $dbh->errstr . "\n";
                        next;
                }

                @params = ('0', $field{'Hostname'});
                $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to execute query '$query': "
                            . $dbh->errstr . "\n";
                        next;
                }
                $query =
                    'REPLACE INTO SWITCHES (name, ip_address, pid, location, ports_required) VALUES (?, ?, ?, ?, ?)';
                $sth = $dbh->prepare($query);
                if (!$sth) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }

                my $model;
                if ($field{'Ports'} == 24 || $field{'Ports'} == 48) {
                        $model = $PID_3750;
                } else {
                        $model = $PID_3560;
                }

                @params = (
                        $field{'Hostname'}, $field{'IP'}, $model,
                        $field{'Location'}, $field{'Ports'}
                );
                $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }

                if ($field{'Template'} ne '') {
                        $query = 'SELECT path FROM EXCEPTIONS WHERE name = ?';
                        $sth   = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR "Failed to prepare query '$query':"
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($field{'Template'});
                        $res    = $sth->execute(@params);
                        if (!$res) {
                                print STDERR "Failed to execute query '$query':"
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        if ($sth->rows == 0) {
                                print STDERR
                                    "Unable to find $field{'Template'} in EXCEPTIONS\n";
                                next;
                        }

                        my $r = $sth->fetchrow_hashref;
                        symlink($r->{'path'},
                                      '/tftpboot/'
                                    . $field{'Hostname'}
                                    . '-ports.tmpl');
                }

                if ($field{'Serial'} ne '') {
                        $query =
                            'SELECT serial_number, max_ports FROM DEVICE_MAP WHERE serial_number=?';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR "Failed to prepare query '$query':"
                                    . $dbh->errstr . "\n";
                                next;
                        }

                        @params = ($field{'Serial'});
                        $res    = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }

                        if ($sth->rows == 0) {
                                print STDERR
                                    "Unable to find $field{'Serial'} in DEVICE_MAP\n";
                                next;
                        } else {
                                my $r = $sth->fetchrow_hashref;
                                if ($r->{'max_ports'} ne $field{'Ports'}) {
                                        print STDERR
                                            "Ports required for $field{'Hostname'} do not match max ports provided by $field{'Serial'} (required: $field{'Ports'}, provided: "
                                            . $r->{'max_ports'} . ")\n";
                                        next;
                                }
                        }
                        $query =
                            'UPDATE DEVICE_MAP SET assigned_switch=?, checked_out=? WHERE serial_number=?';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR
                                    "Failed to prepare query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }

                        @params = ($field{'Hostname'}, '1', $field{'Serial'});
                        $res = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query' for ($field{'Hostname'}, $field{'Serial'}): "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                }
        }
}