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: SQL query export for subnets and location (Oliver Gorwits)
2. Re: SQL query export for subnets and location (Michael Butash)
--- Begin Message ---
Hi Muris and Michael
For some reason I didn't get the original email so double thanks to Michael
for following up, and also I'm pleased to see the SQL report feature being
well used!
Anyway, in answer to the specific question of subnet (IP prefix) vs.
location, I have some bad news. Here's a quote from the wishlist item
<https://github.com/netdisco/netdisco/issues/756>:
As far as I can tell, this is not possible with Netdisco. This is because:
>
> - vlan (switchport interfaces) is a layer 2 concept, does not know
> about IPs
> - subnet (IP Prefix) is a layer 3 concept, and independent of vlans
>
> The only way to do this would be to somehow know the *layer 3 virtual
> interfaces* which exist within vlans (e.g. in Cisco speak, interface
> Vlan123) and then use the prefixes assigned to them to map back to Vlans.
> But I don't think Netdisco does this or stores the data needed to work it
> out (and then there would need to be assumptions about the virtual
> interfaces).
>
I did check some networks I run Netdisco on, and it would be possible but
not in a reliable way. For example some device interfaces are type l3ipvlan
and have IP prefixes (yay!) but I can't see Netdisco picking up vlan
information for them (boo!) so we are stuck. I guess there could be a rule
like if the interface is "Vlan123" then we assume VLAN 123 but it wouldn't
be reliable/safe.
If someone really wants to go ahead with the above, as Michael says it can
be put into a suitably fancy SQL query, it doesn't need more features in
Netdisco (unless getting VLANs of a type l3ipvlan interface is a missing
feature).
Regards
Oliver.
On Thu, 12 Oct 2023 at 00:12, Michael Butash <mich...@butash.net> wrote:
> This will give a full dump, but not quite what he was meaning I think.
>
> You want to assemble an output from the DB with the subnet to vlan mapping
> with descriptions and device, which is all there in various tables. You'll
> probably want to learn some basic postgres sql navigation and syntax like I
> had to, and simply find what tables have the bits you want. You know what
> you want, then just figure out how to relate the data and present it.
>
> I had to do this at the time to format some custom reports for some
> specific info none of the canned methods did, so I had to learn the db
> enough to find where the data was in what tables, relate them via joins,
> and used that to create the output as a report in netdisco.
>
> Alternatively, it might be worth having a postgres navigation tool, there
> are many out there to simply browse the data tables like a human to figure
> out what tables, columns, rows to pull the data from.
>
> Here's an example of a custom sql report I added to my netdisco yml:
>
> - tag: device-cdp-neighbor-inventory
> label: 'Custom - Device CDP Neighbor Inventory'
> category: Device
> columns:
> - { device_hostname: Device_Hostname}
> - { device_ip: Device_IP}
> - { device_dns: Device_DNS}
> - { device_port: Device_Port}
> - { device_name: Device_Port_Description}
> - { device_up: Device_Port_Status}
> - { device_vendor: Device_Vendor}
> - { device_os: Device_OS}
> - { device_osver: Device_OS_Version}
> - { remote_id: CDP_Neighbor_Hostname}
> - { remote_ip: CDP_Neighbor_IP}
> - { remote_port: CDP_Neighbor_Port}
> - { remote_type: CDP_Neighbor_Model}
> - { remote_creation: CDP_Neighbor_Created}
> query: |
> SELECT device.name AS device_hostname, device.ip, device.dns AS
> device_dns, device.vendor AS device_vendor, device.os AS device_os,
> device.os_ver AS device_osver, device_port.ip AS device_ip,
> device_port.port AS device_port, device_port.name AS device_name,
> device_port.up AS device_up, device_port.remote_id AS remote_id,
> device_port.remote_ip AS remote_ip, device_port.remote_port AS remote_port,
> device_port.remote_type AS remote_type, device_port.creation AS
> remote_creation
> FROM device_port
> LEFT JOIN device
> ON device_port.ip = device.ip
> WHERE device_port.remote_id IS NOT NULL
> ORDER BY device_port.remote_ip
>
> If you follow this, I find the info as I state above, define them all, and
> reformat using joins or whatever manipulation of the sql data you need.
>
> This produces a nice report in the menu to add you can spit out the data,
> or at least figure out your sql query to get using psql more directly.
> Plenty of how-to's on sql out there for aspiring or wannabe DBA's we
> network engineer's have to sometimes spoof. If nothing else, use this or
> the examples to reverse engineer enough to do your task.
>
> I hate dev stuff, and if I can do this, anyone can. :)
>
> -mb
>
>
>
>
> On Wed, Oct 11, 2023 at 12:19 PM Chris Weakland <chris.weakl...@gmail.com>
> wrote:
>
>> I do this, but I have a docker install:
>>
>> #!/bin/bash
>> DATE=`date +%Y%m%d`
>> docker exec netdisco-netdisco-postgresql-1 pg_dump -U netdisco -F c
>> --create -f /db-backup/netdisco-pgsql-$DATE.dump netdisco
>> gzip -9f /data/backups/servers/netdisco-db/netdisco-pgsql-$DATE.dump
>> /usr/bin/find /data/backups/servers/netdisco-db/ -type f -ctime +10 -exec
>> rm {} \;
>> ------------------------------
>> *From:* Muris <alcat...@gmail.com>
>> *Sent:* Wednesday, October 11, 2023 12:20 AM
>> *To:* netdisco-users@lists.sourceforge.net <
>> netdisco-users@lists.sourceforge.net>
>> *Subject:* [Netdisco] SQL query export for subnets and location
>>
>>
>> Hi,
>>
>>
>>
>> Is it possible to have a SQL query into the backend netdisco db to get an
>> export of the subnets, and the vlan and what the name of that vlan
>> is/description and what switch they are on?
>>
>>
>>
>> If someone can suggest the SQL query how I would go about getting an
>> export.
>>
>>
>>
>> It could also be useful to have a subnet inventory and where they are all
>> assigned switches etc to in the menus.
>>
>>
>>
>> Reason im asking is I want to export a /16 that’s been divided up and
>> provisioned on the network into a spreadsheet, and then want to reorganise
>> the data, and import spreadsheet data into a IPAM manager for documentation.
>>
>>
>>
>> Thanks
>>
>>
>>
>> Muris
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Netdisco mailing list
>> netdisco-users@lists.sourceforge.net
>> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
>>
> _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--- End Message ---
--- Begin Message ---
Agreed L2 info between vendors is somewhat unreliable (particularly outside
cisco vtp mibs), I forgot about that aspect, more assuming most vlan
interface names would reflect it being like you said ala cisco "interface
Vlan200" and needing some assumption that really *is* a vlan to match
against and extrapolate the VID there. Of course now add the other white
meat of cisco routers using "Interface GigabitEthernet0/0/2.200" as a vlan
gets even harder, and that's just cisco.
Good news is at least the L3 interfaces, ip addresses, and descriptions are
there to associate, you can mostly figure out which of those are related to
vlans vs say an ip directly on a port directly to parse out one way or
another which are vlan-related.
Having some experience with Netbrain (which I usually call Netdisco on
steroids) last year, it does a nice job of painting a whole picture by
pulling in configurations also to glean vlan relations better that way vs
just snmp to define topologies, but you get what you pay for and it's
expensive. Would be nice to see netdisco ssh collector jobs grab those
bits and enrich missing data like the arp and vrf/routing tables for L2 as
well.
-mb
On Fri, Oct 13, 2023 at 6:10 AM Oliver Gorwits <oli...@cpan.org> wrote:
> Hi Muris and Michael
>
> For some reason I didn't get the original email so double thanks to
> Michael for following up, and also I'm pleased to see the SQL report
> feature being well used!
>
> Anyway, in answer to the specific question of subnet (IP prefix) vs.
> location, I have some bad news. Here's a quote from the wishlist item
> <https://github.com/netdisco/netdisco/issues/756>:
>
> As far as I can tell, this is not possible with Netdisco. This is because:
>>
>> - vlan (switchport interfaces) is a layer 2 concept, does not know
>> about IPs
>> - subnet (IP Prefix) is a layer 3 concept, and independent of vlans
>>
>> The only way to do this would be to somehow know the *layer 3 virtual
>> interfaces* which exist within vlans (e.g. in Cisco speak, interface
>> Vlan123) and then use the prefixes assigned to them to map back to Vlans.
>> But I don't think Netdisco does this or stores the data needed to work it
>> out (and then there would need to be assumptions about the virtual
>> interfaces).
>>
>
> I did check some networks I run Netdisco on, and it would be possible but
> not in a reliable way. For example some device interfaces are type l3ipvlan
> and have IP prefixes (yay!) but I can't see Netdisco picking up vlan
> information for them (boo!) so we are stuck. I guess there could be a rule
> like if the interface is "Vlan123" then we assume VLAN 123 but it wouldn't
> be reliable/safe.
>
> If someone really wants to go ahead with the above, as Michael says it can
> be put into a suitably fancy SQL query, it doesn't need more features in
> Netdisco (unless getting VLANs of a type l3ipvlan interface is a missing
> feature).
>
> Regards
> Oliver.
>
> On Thu, 12 Oct 2023 at 00:12, Michael Butash <mich...@butash.net> wrote:
>
>> This will give a full dump, but not quite what he was meaning I think.
>>
>> You want to assemble an output from the DB with the subnet to vlan
>> mapping with descriptions and device, which is all there in various
>> tables. You'll probably want to learn some basic postgres sql navigation
>> and syntax like I had to, and simply find what tables have the bits you
>> want. You know what you want, then just figure out how to relate the data
>> and present it.
>>
>> I had to do this at the time to format some custom reports for some
>> specific info none of the canned methods did, so I had to learn the db
>> enough to find where the data was in what tables, relate them via joins,
>> and used that to create the output as a report in netdisco.
>>
>> Alternatively, it might be worth having a postgres navigation tool, there
>> are many out there to simply browse the data tables like a human to figure
>> out what tables, columns, rows to pull the data from.
>>
>> Here's an example of a custom sql report I added to my netdisco yml:
>>
>> - tag: device-cdp-neighbor-inventory
>> label: 'Custom - Device CDP Neighbor Inventory'
>> category: Device
>> columns:
>> - { device_hostname: Device_Hostname}
>> - { device_ip: Device_IP}
>> - { device_dns: Device_DNS}
>> - { device_port: Device_Port}
>> - { device_name: Device_Port_Description}
>> - { device_up: Device_Port_Status}
>> - { device_vendor: Device_Vendor}
>> - { device_os: Device_OS}
>> - { device_osver: Device_OS_Version}
>> - { remote_id: CDP_Neighbor_Hostname}
>> - { remote_ip: CDP_Neighbor_IP}
>> - { remote_port: CDP_Neighbor_Port}
>> - { remote_type: CDP_Neighbor_Model}
>> - { remote_creation: CDP_Neighbor_Created}
>> query: |
>> SELECT device.name AS device_hostname, device.ip, device.dns AS
>> device_dns, device.vendor AS device_vendor, device.os AS device_os,
>> device.os_ver AS device_osver, device_port.ip AS device_ip,
>> device_port.port AS device_port, device_port.name AS device_name,
>> device_port.up AS device_up, device_port.remote_id AS remote_id,
>> device_port.remote_ip AS remote_ip, device_port.remote_port AS remote_port,
>> device_port.remote_type AS remote_type, device_port.creation AS
>> remote_creation
>> FROM device_port
>> LEFT JOIN device
>> ON device_port.ip = device.ip
>> WHERE device_port.remote_id IS NOT NULL
>> ORDER BY device_port.remote_ip
>>
>> If you follow this, I find the info as I state above, define them all,
>> and reformat using joins or whatever manipulation of the sql data you
>> need.
>>
>> This produces a nice report in the menu to add you can spit out the data,
>> or at least figure out your sql query to get using psql more directly.
>> Plenty of how-to's on sql out there for aspiring or wannabe DBA's we
>> network engineer's have to sometimes spoof. If nothing else, use this or
>> the examples to reverse engineer enough to do your task.
>>
>> I hate dev stuff, and if I can do this, anyone can. :)
>>
>> -mb
>>
>>
>>
>>
>> On Wed, Oct 11, 2023 at 12:19 PM Chris Weakland <chris.weakl...@gmail.com>
>> wrote:
>>
>>> I do this, but I have a docker install:
>>>
>>> #!/bin/bash
>>> DATE=`date +%Y%m%d`
>>> docker exec netdisco-netdisco-postgresql-1 pg_dump -U netdisco -F c
>>> --create -f /db-backup/netdisco-pgsql-$DATE.dump netdisco
>>> gzip -9f /data/backups/servers/netdisco-db/netdisco-pgsql-$DATE.dump
>>> /usr/bin/find /data/backups/servers/netdisco-db/ -type f -ctime +10
>>> -exec rm {} \;
>>> ------------------------------
>>> *From:* Muris <alcat...@gmail.com>
>>> *Sent:* Wednesday, October 11, 2023 12:20 AM
>>> *To:* netdisco-users@lists.sourceforge.net <
>>> netdisco-users@lists.sourceforge.net>
>>> *Subject:* [Netdisco] SQL query export for subnets and location
>>>
>>>
>>> Hi,
>>>
>>>
>>>
>>> Is it possible to have a SQL query into the backend netdisco db to get
>>> an export of the subnets, and the vlan and what the name of that vlan
>>> is/description and what switch they are on?
>>>
>>>
>>>
>>> If someone can suggest the SQL query how I would go about getting an
>>> export.
>>>
>>>
>>>
>>> It could also be useful to have a subnet inventory and where they are
>>> all assigned switches etc to in the menus.
>>>
>>>
>>>
>>> Reason im asking is I want to export a /16 that’s been divided up and
>>> provisioned on the network into a spreadsheet, and then want to reorganise
>>> the data, and import spreadsheet data into a IPAM manager for documentation.
>>>
>>>
>>>
>>> Thanks
>>>
>>>
>>>
>>> Muris
>>>
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> Netdisco mailing list
>>> netdisco-users@lists.sourceforge.net
>>> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
>>>
>> _______________________________________________
>> 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
