...
We heavily suggest you read this entire page before running any commands.
The script is available upon request from Opmantek.
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.
The information to be consumed relies on the Cisco Ready SMARTNET reports that can be requested from Cisco if you have SMARTNET contracts with them.
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 (usually named "Powered by Cisco Ready") as a CSV. Copy that file to your Open-AudIT server in to the /usr/local/omk/bin directory. Name it cisco.csv.
...
We have some options that you can set by directly editing the script. These might be settable on the command line in future. They are below.
name | default value | what it does |
---|---|---|
conf_file | /usr/local/omk/conf/opCommon.nmis | The full path to the Opmantek configuration file. Used to retrieve the UUID. |
debug | 0 | 0 - Summary output only 1 - Commands run 2 - Command outputs (usually file contents). A lot of information is dumped, use for troubleshooting only. |
filename | cisco.csv | The filename to be imported. If it's not in the default directory, the full path including name is OK to use. |
last_date_of_support | 2019-01-01 | The date in YYYYYYY-MM-DD format. Devices with a column value for 'Last Date of Support' that's older than this date will not be imported. |
user | admin | The name of the Open-AudIT user performing the action. Admin 'admin' is the default user and fine to use. |
...
You may see lines beginning with DEBUG, INFO, WARNING or ERROR. Generally only ERRORs ERROR lines are bad and will immediately halt the script execution.
...
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.
The dates we are assuming have a format of D/M/YYYY. These will be reformatted to YYYY-MM-DD for Open-AudIT.
Serial Number / PAK number | Coverage | Covered Line Status | Business Entity | Sub Business Entity | Product Family | Product ID | Product Description | Asset Type | Product Type | Item Quantity | Covered Line Start Date | Covered Line End Date | Covered Line End Date FY-FQ | Contract Type | Service Brand Code | Contract Number | Subscription Reference ID | Ship Date | End of Product Sale Date | End of Software Maintenance Date | Last Date of Support | LDOS FY-FQ | End Of Life Product Bulletin | Warranty Type | Warranty End Date | Install Site Customer Registry GU Name | Install Site Customer Registry Party Name | Install Site Customer Registry Party ID | Install Site Name | Install Site ID | Install Site Address 1 | Install Site City | Install Site State | Install Site Country | Install Site Postal Code | Product Bill to ID | Product Bill-to Partner Name | Product Partner BE GEO ID | POS Partner BE GEO ID | POS Partner BE GEO Name | Service Bill to ID | Service Bill-to Partner Name | Service Partner BE GEO ID | Product List Price $ | Default Service List Price $ | Default Service Level | Existing Coverage Level List Price $ | Instance ID | Parent Instance ID | Product SO | Product PO | Service SO | Service PO | Web Order ID | Mapped to SWSS (Y/N) | Mapped to C1 (Y/N) | Auto-renewal flag | Configuration |
...
So you may already have some of these devices in Open-AudIT, you may not. How does Open-AudIT know what to do here? It all depends on the serial number. The spreadsheet doesn't contain the usual items we need to match devices (think hostname, ip, type, UUID, etc), but it does contain the device serial number. So you'll need to ensure your configuration item named 'match_serial' is set to 'y' (which is the default). As long as that is set, you can import and run discoveries in whichever order you like. Obviously discovery must be working and able to retrieve the serial from the device to perform the match. If you don't have working discovery (usually SNMP based for Cisco network equipment), you'll likely end up with duplicate devices - you have been warned! Just remember, Bulk Edit is your friend.
If you import a device, then run a discovery some fields may be overwritten. Model is likely the first one you'll notice. This is expected. Two screenshots are below of a device after import, then after discovery.
Note that this was an example only as the import was a Cisco Nexus and the discovered device was a Cisco 1841 router. I manually changed the serial to cause them to match.
After import.
After Discovery.
Sample Queries
Below are some sample queries we may refine and include in newer releases of Open-AudIT. To import them, copy the JSON contents and go to menu → Manage → Queries → List Queries and click the Import button in the top right of the table panel.
Devices Not Discovered
Code Block |
---|
{
"name" : "Devices Not Discovered",
"sql" : "SELECT system.id AS `system.id`, system.type AS `system.type`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.serial AS `system.serial`, system.owner AS `system.owner`, locations.name AS `locations.name`, locations.id AS `locations.id` FROM `system` LEFT JOIN `locations` ON system.location_id = locations.id WHERE @filter AND system.ip = ''",
"description" : "Devices Imported but Never Seen by Discovery",
"menu_display" : "y",
"org_id" : 1,
"menu_category" : "Device",
"link" : ""
} |
Devices Discovered Out of Contract
Code Block |
---|
{
"org_id" : 1,
"menu_category" : "Device",
"menu_display" : "y",
"link" : "",
"sql" : "SELECT system.id AS `system.id`, system.type AS `system.type`, system.ip AS `system.ip`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.serial AS `system.serial`, DATE(system.last_seen) AS `system.last_seen`, system.end_of_service AS `system.end_of_service`, system.owner AS `system.owner`, locations.name AS `locations.name`, locations.id AS `locations.id` FROM `system` LEFT JOIN `locations` ON system.location_id = locations.id WHERE @filter AND system.ip != '' AND DATE(system.last_seen) > system.end_of_service",
"name" : "Devices Discovered Out of Contract",
"description" : "Devices that have been seen by discovery but whose end_of_service has expired."
} |
Discovered Inventory Report
Code Block |
---|
{
"link" : "",
"org_id" : 1,
"menu_category" : "Device",
"menu_display" : "y",
"description" : "Inventory details for discovered devices",
"sql" : "SELECT system.id AS `system.id`, system.type AS `system.type`, system.ip AS `system.ip`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.serial AS `system.serial`, DATE(system.last_seen) AS `system.last_seen`, system.end_of_service AS `system.end_of_service`, system.end_of_life AS `system.end_of_life`, system.end_of_production AS `system.end_of_production`, system.maintenance_expires AS `system.maintenance_expires`, system.warranty_expires AS `system.warranty_expires`, system.owner AS `system.owner`, locations.name AS `locations.name`, locations.id AS `locations.id` FROM `system` LEFT JOIN `locations` ON system.location_id = locations.id WHERE @filter AND system.ip != ''",
"name" : "Discovered Inventory Report"
}
|
Serial Numbers Not Found
Code Block |
---|
{
"link" : "",
"org_id" : 1,
"menu_category" : "Device",
"menu_display" : "y",
"description" : "Devices imported with a serial number that have not been discovered",
"sql" : "SELECT system.id AS `system.id`, system.type AS `system.type`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.serial AS `system.serial`, system.end_of_service AS `system.end_of_service`, system.end_of_life AS `system.end_of_life`, system.end_of_production AS `system.end_of_production`, system.maintenance_expires AS `system.maintenance_expires`, system.warranty_expires AS `system.warranty_expires`, system.owner AS `system.owner`, locations.name AS `locations.name`, locations.id AS `locations.id` FROM `system` LEFT JOIN `locations` ON system.location_id = locations.id WHERE @filter AND system.ip = ''",
"name" : "Serial Numbers Not Found"
}
|
Devices Without Serial Numbers
Code Block |
---|
{
"link" : "",
"menu_category" : "Device",
"org_id" : 1,
"menu_display" : "y",
"description" : "Devices discovered that have no reported serial",
"sql" : "SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.ip AS `system.ip`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.identification AS `identification`, locations.name AS `locations.name`, locations.id AS `locations.id` FROM `system` LEFT JOIN `locations` ON system.location_id = locations.id WHERE @filter AND system.serial = ''",
"name" : "Devices Without Serial Numbers"
|
Script
View file | ||||
---|---|---|---|---|
|