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 (Chris Weakland)
2. Re: SQL query export for subnets and location (Michael Butash)
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
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/
>
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
