On Mondays my colleague and I discuss the big things that need to be done for the week. He was going to work on the network maps and I was going to work on reconciling our network inventory against what Cisco has for us on the support contract.

First I got out one of my generic Netdisco queries and started looking at the tables in the Netdisco sql files. It seemed pretty straightforward what I needed as all the information came from the devices table. I ran the query and messed around with the output format to get it what I needed. Next I went to the WCS and hunted for an inventory report, which they had. So I ran the report and exported it to .csv format.

[code lang=”perl”]
#!/usr/bin/perl

# query-netdisco.pl
# 2009-09-01 Judson Bishop
# Released under GPLv2
# Simple Netdisco query, output in .csv.
use DBI;

# Connect to DB
my $dbh = DBI->connect ( "dbi:ODBC:netdisco", "netdisco", "CHANGE_PASSWORD", {PrintError => 1})
or die "connection failed to database $DBI::errstr\n";

# Set up tracing
# This should only do something when you add prepare, execute,
# fetch and disconnect.
unlink ‘/tmp/trace.log’ if -e ‘/tmp/trace.log’;
DBI->trace( 2, ‘/tmp/trace.log’ );

my $sql_st = $dbh->prepare( "SELECT name, mac, model, os_ver, location, vendor, ip, serial FROM device" );

$sql_st->execute()
or die "Cannot execute SQL statement $DBI::errstr\n";

# Retrieve data
my @row;
while ( @row = $sql_st->fetchrow_array() )
{
foreach (@row)
{
print $_ . ",";
}
print "\n";
}
warn "Data fetch terminated by error $DBI::errstr\n"
if $DBI::err;

# Disconnect
$dbh->disconnect or warn "disconnection failed $DBI::errstr\n";

exit();
[/code]

Then the fun began. I had to figure out the common fields between Netdisco and the Cisco WCS inventory report. Here they are:

So it’s time to massage the data:

[code]./query-netdisco.pl | grep cisco >circus-inventory.csv [/code]

[code]cat wcs-inventory.csv | cut -d, -f 1,2,3,4,5,6,7,12,13 >>circus-inventory.csv [/code]

After saving the Cisco spreadsheet into a .csv file it was off to the races to make a short script comparing what was in our inventory and what Cisco believes we have.

[code lang=”perl”]
#!/usr/bin/perl -w

# This is the order of the columns from Netdisco and the WCS inventory report:
# Name,Ethernet MAC,Model,Software Version,Location,Controller Name,ip,Serial Number

#
# This is the order from the Cisco spreadsheet:
# Contract #,Contract Status,Contract Type,Item Beg Date,Item End Date,Item Name,Serial #,Item Status,Item Type,Last Date of Support,Install Site Id,Install Site Name,Install Site Addr1,Install Site Addr2,Install Site City,Install Site State/Province,Install Site Zipcode,Install Site Country,Bill-To Id,Billto Name

my %circus;
my %cisco;

# Read in the local inventory.
open (FILE,"circus-inventory.csv") or die "Error: can’t open file\n $!";
while ()
{
chomp;
my ($name,$mac,$model,$version,$location,$vendor,$ip,$serial) = split ‘,’;
$circus{$serial} = { ‘name’=>$name, ‘mac’=>$mac, ‘model’=>$model, ‘version’=>$version,
‘location’=>$location, ‘vendor’=>$vendor, ‘ip’=>$ip, ‘serial’=>$serial};
}
close FILE or die "Error: can’t close file\n $!";

# Read in the cisco inventory.
open (FILE,"cisco-inventory.csv") or die "Error: can’t open file\n $!";
while ()
{
chomp;
my ($con_num,$con_stat,$con_type,$start_date,$end_date,$name,$serial,$status,
$type,$end_support,$site_id,$site_name,$addr1,$addr2,$city,$state,$zip,
$country,$bill_id,$bill_name) = split ‘,’;

$cisco{$serial} = { ‘con_num’=>$con_num, ‘con_stat’=>$con_stat, ‘con_type’=>$con_type,
‘start_date’=>$start_date, ‘end_date’=>$end_date,’name’=>$name, ‘serial’=>$serial,
‘status’=>$status, ‘type’=>$type, ‘end_support’=>$end_support, ‘site_id’=>$site_id,
‘site_name’=>$site_name, ‘addr1’=>$addr1, ‘addr2’=>$addr2, ‘city’=>$city,
‘state’=>$state, ‘zip’=>$zip, ‘country’=>$country, ‘bill_id’=>$bill_id,
‘bill_name’=>$bill_name };
}
close FILE or die "Error: can’t close file\n $!";

# Compare what Cisco has in their list to our inventory.
for my $key (keys %cisco)
{
if(exists $circus{$key})
{
# We both have this serial number, so we agree.
print "both $circus{$key}->{name},$circus{$key}->{mac}, $circus{$key}->{model},$circus{$key}->{serial}, $circus{$key}->{version},$circus{$key}->{location}, $circus{$key}->{ip}\n";
# We delete the key from circus to make sure that when we write
# the list later we know which are not in the contract.
delete($circus{$key});
} else {
# Cisco has it in their inventory but we don’t.
print "cisco only $cisco{$key}->{serial},$cisco{$key}->{name}\n";

}
}

for my $key (keys %circus)
{
# We have it on our inventory but they don’t have it in theirs.
print "circus only $circus{$key}->{name},$circus{$key}->{mac}, $circus{$key}->{model},$circus{$key}->{serial}, $circus{$key}->{version}, $circus{$key}->{location},$circus{$key}->{ip}\n";
}

exit();
[/code]

[code]
./match.pl >true-up.txt
cat true-up.txt | grep cisco > cisco-only.txt
cat true-up.txt | grep eamc > eamc-only.txt
cat true-up.txt | grep both > both.txt
[/code]