Skip to content

Instantly share code, notes, and snippets.

@consentfactory

consentfactory/netdisco_queries.sql

Created July 19, 2021 18:44
Show Gist options
  • Save consentfactory/3990cfc6c1b75eb65fcfe1fdccf013ff to your computer and use it in GitHub Desktop.
Save consentfactory/3990cfc6c1b75eb65fcfe1fdccf013ff to your computer and use it in GitHub Desktop.
Download ZIP
Netdisco PSQL Queries
Raw
netdisco_queries.sql
select *
from device_port as dp
join device as d
on dp.ip = d.ip
where
d.model = '38xxstack' and
dp.descr ~ '(Giga|TenGiga)' and
dp.ip << '10.0.0.0/8'
order by dp.ip desc;
select d.ip, d.name, d.model, dm.model as module_name, dm.name
from device as d
inner join device_module as dm
on d.ip = dm.ip
where
d.model = '38xxstack' and
dm.model <> ''
and dm.model not like 'PWR%'
order by d.name;
select d.ip, d.name, temp.counts as router_port_count
from device as d
left join
(
select dp.ip as ip, count (dp.ip) as counts
from device_port as dp
where
dp.descr ~ '(Giga|TenGiga|Po)' and
dp.up = 'up' and
dp.ip << '10.0.0.0/8'
group by ip
)
temp on temp.ip = d.ip
where
d.model = '38xxstack' and
d.ip << '10.0.0.0/8'
order by router_port_count desc;
select
d.name as name, d.ip as ip,
dmp.port as port, dmp.port_desc as port_desc, dmp.remote_ip as remote_ip, dmp.remote_id as remote_id, dmp.module as module
from
device as d
left join
(
select
dp.ip as ip, dp.port as port, dp.up as up, dp.name as port_desc, dp.remote_ip as remote_ip, dp.remote_id as remote_id,
dm.description as module
from
device_port as dp
left join
device_module as dm
on dp.ip = dm.ip
and dp.port = dm.name
)
dmp on d.ip = dmp.ip
where
d.model = '38xxstack' and
d.ip << '10.0.0.0/8' and
--d.ip = '10.199.9.1' and
dmp.up = 'up' and
dmp.port not similar to '(Vlan|Loop|Port|Tunn|Null)%'
order by d.ip, port
select cast (ip as text) from
device_module
where cast (ip as text) like '%.199.%'
group by ip having count(*) = 1
order by ip
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment