Cisco Contract True-up
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.
The funniest thing about this post was the first time I ran the comparison script, nothing matched up. I started looking for logic errors and couldn’t find any. Next I compared the serial numbers from Netdisco and Cisco, they didn’t match. So I logged into a router and a switch, the Netdisco serial numbers were correct, however, the Cisco serial numbers were not. I started to write an email to our Cisco contact and decided I had better call him because I didn’t want him blowing me off. He started looking at what he sent me and noticed that he sent me item numbers, not serial numbers. Then he told me the craziest thing, that I was the first person to complain to him that I was not given serial numbers and he had been using the same macros in his spreadsheet for a long time. Makes you stop and think considering they had quite a bit of inventory on our support contract that we don’t have.
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, all the information I needed came from the devices table. I ran the query and messed around with the output format to get it to 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.
Here is the code for querying Netdisco to get inventory:
# 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' );
# Prepare query
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();
Then the fun began. I had to figure out the common fields between Netdisco and the Cisco WCS inventory report. Here they are:
| Netdisco | WCS |
| name | name |
| mac | mac |
| model | model |
| os_ver | software version |
| location | location |
| vendor | controller |
| ip | status |
| serial | serial |
So it’s time to massage the data:
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.
# match.pl
# 2009-09-01 Jud Bishop
# Released under GPLv2.
# This script compares the output of cisco-inventory.csv and circus-inventory.csv for inventory.
# 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.
# This is the follow up from the delete above.
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();
Now make a file for each instance of inventory. Then it was just a matter of time comparing the instances. Notice that I included the IP addresses in the Netdisco inventory. The first time I compared we didn’t have IP addresses and it was a pain checking the map for each one. I decided it was easier to rewrite the script so that we could easily find the devices.
cat true-up.txt | grep cisco >cisco-only.txt
cat true-up.txt | grep circus >circus-only.txt
cat true-up.txt | grep both >both.txt
Hi, i came accross your website.
Is there any way netdisco can be setup to export to CSV?
@maurice We are running an a pre-1.0 version of NetDisco so there may be a way on the newer version, but I don’t see a way in ours. I have found it easiest to look at the database tables and pull from there. Good luck.