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. Re: Complete Device Inventory Report (Oliver Gorwits)
--- Begin Message ---
Thank you Muris and Jethro!
I've added this report to the next release, called Device Inventory
(alongside the existing Module Inventory).
regards
Oliver
On Tue, 24 Sept 2024 at 13:35, Jethro Binks <jethro.bi...@strath.ac.uk>
wrote:
> That's really good Muris thanks, it's always been a bother that ND focused
> on an Inventory being based on a logical object which has one or more IP
> addresses, when that object could represent a whole stack of physical
> things that you might want to care about in the real world.
>
> It's quite a similar report to the Module Inventory report (yours produces
> about 5% more lines ... I think yours also captures things that have no
> serial number captured which the Module Inventory doesn't) but it's
> certainly a bit more pleasing to read.
>
> J.
>
> . . . . . . . . . . . . . . . . . . . . . . . . .
>
> Jethro R Binks, Network Manager,
>
> Information Services Directorate, University Of Strathclyde, Glasgow, UK
>
>
> The University of Strathclyde is a charitable body, registered
> in Scotland, number SC015263.
> ------------------------------
> *From:* Muris <alcat...@gmail.com>
> *Sent:* 24 September 2024 10:42 AM
> *To:* netdisco-users@lists.sourceforge.net <
> netdisco-users@lists.sourceforge.net>
> *Subject:* [Netdisco] Complete Device Inventory Report
>
>
> 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;
> _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
>
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
