Skip to end of banner
Go to start of banner

Reconciling a spreadhseet from Cisco

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

Pre-Release Only

As at 2020-08-13, this is currently in selective pre-release to supported customers only. Please contact Opmantek to be involved.

All of the below is subject to change in released versions of Open-AudIT, however the functionality will remain the same.

We heavily suggest you  read this entire page before running any commands.

Introduction

So you have a Cisco supplied spreadsheet in .xlsb format that contains lots of great information like serial numbers, date of purchase, warranty details and more and you want to get that information into Open-AudIT for easy reporting. Say no more...

We are developing a script to just this very thing. This is a perl script for Linux Open-AudIT servers only.

Running the Script

First thing to do is to convert the spreadsheet to something workable. That's a CSV. Open the spreadsheet in your program of choice and save the relevant tab as a CSV. Copy that file to your Open-AudIT server in to the /usr/local/omk/bin directory. Name it cisco.csv.

Next, log on to your Open-AudIT server and change into the directory /usr/local/omk/bin

Now run the script. Done. It's that easy.

cd /usr/local/omk/bin
./import_cisco_into_open-audit.pl

What is imported?

But which rows are imported? We have a few rules around this, namely:

  • Product Type must equal CHASSIS
  • A Serial Number must be present
  • Covered Line Status must be either ACTIVE, EXPIRED or SIGNED
  • The Last Date of Support must be after the configured value of 2019-01-01 (see below).


You'll not see much by default until it's complete. When it has done so, you'll see a summary like below.

mark@desktop:/usr/local/opmojo/bin$ ./import_cisco_into_open-audit.pl
Start Time: Thu Aug 13 14:13:11 2020
End Time: Thu Aug 13 14:22:05 2020
Duration: 534 seconds
Location Count: 32
Device Count: 896
mark@desktop:/usr/local/opmojo/bin$

Options

We have some options that you can set by directly editing the script. They are below.

namedefault valuewhat it does
conf_file/usr/local/omk/conf/opCommon.nmisThe full path to the Opmantek configuration file. Used to retrieve the UUID.
debug0

0 - Summary output only

1 - Commands run

2 - Command outputs (usually file contents).

filenamecisco.csvThe filename to be imported. If it's not in the default directory, the full path including name is OK to use.
last_date_of_support2019-01-01The date in YYY-MM-DD format. Devices with a column value for 'Last Date of Support' that's older than this date will not be imported.
useradminThe name of the Open-AudIT user performing the action. Admin is the default user and fine to use.

Errors and Warnings (oh my!)

You may see lines beginning with DEBUG, INFO, WARNING or ERROR. Generally only ERRORs are bad and will immediately halt the script execution.

Sample Spreadsheet Columns

Below are the columns from our current Cisco spreadsheet. If your order of these columns differs, it shouldn't matter, however your spreadsheet should contain all the columns used in the mappings below.

Serial Number / PAK numberCoverageCovered Line StatusBusiness EntitySub Business EntityProduct FamilyProduct IDProduct DescriptionAsset TypeProduct TypeItem QuantityCovered Line Start DateCovered Line End DateCovered Line End Date FY-FQContract TypeService Brand CodeContract NumberSubscription Reference IDShip DateEnd of Product Sale DateEnd of Software Maintenance DateLast Date of SupportLDOS FY-FQEnd Of Life Product BulletinWarranty TypeWarranty End DateInstall Site Customer Registry GU NameInstall Site Customer Registry Party NameInstall Site Customer Registry Party IDInstall Site NameInstall Site IDInstall Site Address 1Install Site CityInstall Site StateInstall Site CountryInstall Site Postal CodeProduct Bill to IDProduct Bill-to Partner NameProduct Partner BE GEO IDPOS Partner BE GEO IDPOS Partner BE GEO NameService Bill to IDService Bill-to Partner NameService Partner BE GEO IDProduct List Price $Default Service List Price $Default Service LevelExisting Coverage Level List Price $Instance IDParent Instance IDProduct SOProduct POService SOService POWeb Order IDMapped to SWSS (Y/N)Mapped to C1 (Y/N)Auto-renewal flag

Configuration


Mapping the spreadsheet to Open-AudIT

Below are the mappings between the spreadsheet columns and those in Open-AudIT.

Cisco SpreadsheetOpen-AudIT
Product Familyasset_tag
Product Descriptiondescription
Last Date of Supportend_of_life
End of Product Sale Dateend_of_production (new, see below)
Covered Line End Dateend_of_service
Serial Number / PAK numberhostname
End of Software Maintenance Datemaintenance_expires (new, see below)
Product IDmodel
Install Site Customer Registry GU Nameowner
Product List Price $purchase_amount
Covered Line Start Datepurchase_date
Contract Numberpurchase_service_contract_number
Product Bill-to Partner Namepurchase_vendor
Serial Number / PAK numberserial
Warranty End Datewarranty_expires
Covered Line Statuswarranty_status (new, see below)

In addition to the above we set a few other fields as below.

FieldSet to
last_seen_bynmap - so we can override this by discovery, audit or the GUI.
location_idCalculated by inserting any missing Locations as below, then associating to the device.
org_id1 - the default Org.
type

By default, set to 'network device'. If one of the below matches, set.

if ($line->{'Business Entity'} eq 'Collaboration' and CORE::index($line->{'Product Description'}, 'Phone') != -1) { $device->{'system'}->{'type'} = 'voip phone'; }

if ($line->{'Business Entity'} eq 'Collaboration' and CORE::index($line->{'Product Description'}, 'Camera') != -1) { $device->{'system'}->{'type'} = 'webcam'; }

if ($line->{'Business Entity'} eq 'Computing Systems') { $device->{'system'}->{'type'} = 'computer'; }

if ($line->{'Business Entity'} eq 'Data Center Networking') { $device->{'system'}->{'type'} = 'switch'; }

if ($line->{'Business Entity'} eq 'Enterprise Routing') { $device->{'system'}->{'type'} = 'router'; }

if ($line->{'Business Entity'} eq 'Enterprise Switching') { $device->{'system'}->{'type'} = 'switch'; }

if ($line->{'Business Entity'} eq 'IOT') { $device->{'system'}->{'type'} = 'network device'; }

if ($line->{'Business Entity'} eq 'Other') { $device->{'system'}->{'type'} = 'network device'; }

if ($line->{'Business Entity'} eq 'Security') { $device->{'system'}->{'type'} = 'firewall'; }

if ($line->{'Business Entity'} eq 'Service Provider Routing') { $device->{'system'}->{'type'} = 'router'; }

if ($line->{'Business Entity'} eq 'Wireless') { $device->{'system'}->{'type'} = 'wap'; }

We also import the list of Locations mapping the fields as below. If already present, we leave the Location as is. We test for this using the spreadsheet value for Install Site ID.

Cisco SpreadsheetOpen-AudIT
Install Site Address 1address
Install Site Citycity
Install Site Countrycountry
Install Site IDexternal_ident
Install Site Namename
1 (the default Org)org_id
Install Site Postal Codepostcode
Install Site Statestate
  • No labels