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.
#!/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();
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:
./query-netdisco.pl | grep cisco >circus-inventory.csv
cat wcs-inventory.csv | cut -d, -f 1,2,3,4,5,6,7,12,13 >>circus-inventory.csv
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.
#!/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();
./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