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);
for (my $i = 1 ; $i < scalar(@contents) ; $i++) {
        my $line = $contents[$i];
        chomp $line;
        my ($hostname, $model, $location, $mdf, $serial, $port_req, $ip) =
            split(/,/, $line);
        $hostname = trim($hostname);
        $model    = trim($model);
        $location = trim($location);
        $mdf      = trim($mdf);
        $ip       = trim($ip);
        $serial   = trim($serial);
        $port_req = trim($port_req);

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

        if ($hostname ne '') {
                if ($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 = ($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 $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'}, $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 $ip, hostname $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', $ip);
                                $res = $sth->execute(@params);
                                if (!$res) {
                                        print STDERR
                                            "Failed to execute query '$query': "
                                            . $dbh->errstr . "\n";
                                        next;
                                }
                                next;
                        }
                        $ip = $ref->{'address'};
                } else {
                        $query =
                            'SELECT address FROM ADDRESSES WHERE location=? AND used=? LIMIT 1';
                        $sth = $dbh->prepare($query);
                        if (!$sth) {
                                print STDERR
                                    "Failed to prepare query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        @params = ($mdf, '0');
                        $res = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query': "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                        my $r  = $sth->fetchrow_hashref;
                        $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', $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', $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;
                }

                if ($model =~ /3750[xX]/) {
                        $model = $PID_3750;
                } else {
                        $model    = $PID_3560;
                        $port_req = '8';
                }

                @params = ($hostname, $ip, $model, $location, $port_req);
                $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }

                if ($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 = ($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 $serial in DEVICE_MAP\n";
                                next;
                        } else {
                                my $r = $sth->fetchrow_hashref;
                                if ($r->{'max_ports'} ne $port_req) {
                                        print STDERR
                                            "Ports required for $hostname do not match max ports provided by $serial (required: $port_req, 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 = ($hostname, '1', $serial);
                        $res = $sth->execute(@params);
                        if (!$res) {
                                print STDERR
                                    "Failed to execute query '$query' for ($hostname, $serial): "
                                    . $dbh->errstr . "\n";
                                next;
                        }
                }
        }
}