Send netdisco-users mailing list submissions to
netdisco-users@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
netdisco-users-requ...@lists.sourceforge.net
You can reach the person managing the list at
netdisco-users-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Complete Device Inventory Report (Muris)
--- Begin Message ---
Hi Netdisco peeps,
I looked at the internal reporting for netdisco about providing a complete
device inventory report, but it was not sufficient for my needs and I needed to
work on something more to complete especially with complex network audit
reports and running comparisons.
So I developed the – “Complete Device Inventory Report” in a SQL query
This is what this particular inventory report does –
Consolidated Data - It pulls data from both the device and device_module
tables, ensuring that you get a unified view of each device's name, IP address,
location, model, serial number, vendor, and operating system. If the device has
a module (like a chassis – eg.stacked), the stacked device details are included
and individual serial numbers and number of the switch in the stack in device
details column.
Avoids Duplicates - By using the ROW_NUMBER() function, it ensures that only
the first instance of a device (or its module) is listed, preventing duplicate
entries and providing cleaner, more readable data.
Enhanced Model Data - The query merges the device and module models, appending
the module's model (if different) to the device's model, separated by a |. This
gives a more complete picture of the hardware involved.
Prioritizes Important Data - If a device has a module (chassis), it prioritizes
the module's serial number, software version, and model. If there’s no module
or the data is missing, it defaults to the device's information.
Clear and Organized Output: The results are sorted by Device Name and Serial,
making it easy to find and group related entries for the same device.
Easily searchable and exportable – The data is able to be filtered in the
report, and also exportable in CSV via the webpage, as well as via backend SQL
query like excel
To add this report simply modify deployment.yml and add the below. Happy to
hear your thoughts ☺
- tag: CompleteDeviceInventory
category: Device
label: 'Complete Device Inventory'
columns:
- {device_name: 'Device Name'}
- {device_details: 'Device Details'}
- {ip: 'IP Address', _searchable: true}
- {location: 'Location'}
- {model: 'Model'}
- {serial: 'Serial'}
- {vendor: 'Vendor'}
- {os: 'Operating System'}
- {version: 'OS Version'}
query: |
WITH ranked_devices AS (
SELECT
d.name AS device_name,
CASE
WHEN dm.ip IS NOT NULL AND dm.class = 'chassis' THEN dm.name
ELSE NULL
END AS device_details,
d.ip AS ip,
d.location AS location,
COALESCE(dm.serial, d.serial) AS serial,
d.vendor AS vendor,
d.os AS os,
COALESCE(
CASE
WHEN dm.model IS NOT NULL
AND LOWER(REGEXP_REPLACE(dm.model, '[^a-zA-Z0-9]', '', 'g')) <>
LOWER(REGEXP_REPLACE(d.model, '[^a-zA-Z0-9]', '', 'g'))
THEN dm.model || '|' || d.model
ELSE COALESCE(dm.model, d.model)
END, d.model
) AS model,
CASE
WHEN dm.sw_ver IS NOT NULL AND dm.sw_ver <> '' THEN dm.sw_ver
ELSE d.os_ver
END AS version,
ROW_NUMBER() OVER (
PARTITION BY d.name, COALESCE(dm.serial, d.serial)
ORDER BY d.ip
) AS rn
FROM
device d
LEFT JOIN
device_module dm
ON d.ip = dm.ip AND dm.class = 'chassis'
)
SELECT
device_name,
device_details,
ip,
location,
model,
serial,
vendor,
os,
version
FROM
ranked_devices
WHERE
rn = 1
ORDER BY
device_name, serial;
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
