Netdisco PSQL Queries

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