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. Inverse vlan report (James Dore)
2. Re: Inverse vlan report (Christian Ramseyer)
3. Re: Inverse vlan report (Christian Ramseyer)
--- Begin Message ---
Hi list,
Is it possible to invert the VLAN report, to tell me which switches
*don't* have a particular vlan?
Cheers,
James
--
James Dore
IT Technical Officer
New College, Oxford, OX1 3BN
01865 279252 (Mon-Fri 0830-1600) - 01865 612345 All other
www.new.ox.ac.uk
--- End Message ---
--- Begin Message ---
On 10.10.22 14:38, James Dore wrote:
Hi list,
Is it possible to invert the VLAN report, to tell me which switches
*don't* have a particular vlan?
I think this should work:
with
a as (select distinct dns, device.ip, vlan from device, device_vlan
where dns ~* '.*'),
b as
( select d.dns, d.ip, dv.vlan from device d
left join device_vlan dv on d.ip = dv.ip
where d.dns ~* '.*' )
select a.dns, a.ip , case when b.vlan is not null then 'present' else
'missing' end as vlan_present , a.vlan from a
full outer join b on b.vlan = a.vlan and b.ip = a.ip
order by 1, 4;
Gets you something like
|dns |ip |vlan_present|vlan|
|-----------------------------|------------|------------|----|
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |118 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |119 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|present |120 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|present |121 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |122 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |123 |
You can put the sql in a custom report like shown in
https://github.com/netdisco/netdisco/wiki/Configuration#reports
Cheers
Christian
--- End Message ---
--- Begin Message ---
I later came back to this query for it not to be finished after some
hours. Apparently we have a policy to try to use as many of the 4096
different vlans as possible, making the "switches times all vlans"
cartesian product absolutely humongous.
This should be a bit faster by using explicitly materialized CTE
(Postgres >= 12), cutting down on first distinct operation with a
subselect, and only showing the "missing" items (b.ip is null):
with
a as materialized
(select distinct device.dns, device.ip, d0.vlan from device, (select
distinct vlan from device_vlan) d0),
b as materialized
(select d.dns, d.ip, dv.vlan from device d left join device_vlan dv
on d.ip = dv.ip )
select a.dns, a.ip ,
case when b.vlan is not null then 'present' else 'missing' end as
vlan_present ,
a.vlan from a
full outer join b on b.vlan = a.vlan and b.ip = a.ip
where b.ip is null
order by 1,4
;
If it's still slow, look at cranking up "work_mem" in Postgres. I used
SET session work_mem TO '2.0GB' in the end to make it all fit into memory.
On 10.10.22 17:50, Christian Ramseyer wrote:
On 10.10.22 14:38, James Dore wrote:
Hi list,
Is it possible to invert the VLAN report, to tell me which switches
*don't* have a particular vlan?
I think this should work:
with
a as (select distinct dns, device.ip, vlan from device, device_vlan
where dns ~* '.*'),
b as
( select d.dns, d.ip, dv.vlan from device d
left join device_vlan dv on d.ip = dv.ip
where d.dns ~* '.*' )
select a.dns, a.ip , case when b.vlan is not null then 'present' else
'missing' end as vlan_present , a.vlan from a
full outer join b on b.vlan = a.vlan and b.ip = a.ip
order by 1, 4;
Gets you something like
|dns |ip |vlan_present|vlan|
|-----------------------------|------------|------------|----|
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |118 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |119 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|present |120 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|present |121 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |122 |
|vsrth804.weyland-yutani.co.jp|10.40.167.65|missing |123 |
You can put the sql in a custom report like shown in
https://github.com/netdisco/netdisco/wiki/Configuration#reports
Cheers
Christian
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com
Phone: +41 79 644 77 64
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
