Hello,
I need to verify nine hundred MAC address. These need to appears on interfaces with one especific VLAN ID, on pid or trunk (tag)
If appears on one interface without the VLAN, I need to list sysname and port of switch.
It is an job to do using an SELECT on the NAV database.
Can anyone help me to write the SELECT?
Tia,
On Thu, 18 May 2023 00:55:23 -0300 (1 week, 5 days, 7 hours ago) Marcos Tadeu marcos@telecom.uff.br wrote:
I need to verify nine hundred MAC address. These need to appears on interfaces with one especific VLAN ID, on pid or trunk (tag). If appears on one interface without the VLAN, I need to list sysname and port of switch. It is an job to do using an SELECT on the NAV database.
Can anyone help me to write the SELECT?
Possibly, but it's not clear to me what is meant by "need to verify nine hundred MAC address". What is the selection criteria?
Well... embarrassing
There are Wi-fi APs.
The Wi-fi system have an separated manager technical team. They have the MAC list.
The problems:
1) no sw config backup (350)
2) no ports or path documentation;
3) lots of errors on VLAN configuration, by the last technical team (OFF team!)
1 + 2 + 3 = lost APs ! we need to search MACs and confirm that they are on correct VLAN
Em 30/05/2023 08:50, Morten Brekkevold escreveu:
On Thu, 18 May 2023 00:55:23 -0300 (1 week, 5 days, 7 hours ago) Marcos Tadeu marcos@telecom.uff.br wrote:
I need to verify nine hundred MAC address. These need to appears on interfaces with one especific VLAN ID, on pid or trunk (tag). If appears on one interface without the VLAN, I need to list sysname and port of switch. It is an job to do using an SELECT on the NAV database.
Can anyone help me to write the SELECT?
Possibly, but it's not clear to me what is meant by "need to verify nine hundred MAC address". What is the selection criteria?
Marcos Tadeu marcos@telecom.uff.br writes:
1 + 2 + 3 = lost APs ! we need to search MACs and confirm that they are on correct VLAN
Depending on what you want, and given that you have a long list of MAC addresses to search for, you might able to do something like this:
SELECT mac, n.sysname AS switch, i.ifname AS interface, i.vlan AS vlan FROM cam JOIN netbox n USING (netboxid) JOIN interface i ON (cam.netboxid = i.netboxid AND cam.ifindex = i.ifindex) WHERE end_time >= 'infinity' AND mac IN ('00:0b:ad:c0:ff:ee', '00:00:ca:fe:ba:be', ... );
But that list might get really long if had over 900 MAC addresses to search for. You might want to consider dumping the list of MAC addresses into a temporary table and joining with that instead of putting everything into the query each time.
Very good
TKS
Also need to list the VLANs allowed, if the interface have trunk.
Em 12/06/2023 05:43, Morten Brekkevold escreveu:
Marcos Tadeu marcos@telecom.uff.br writes:
1 + 2 + 3 = lost APs ! we need to search MACs and confirm that they are on correct VLAN
Depending on what you want, and given that you have a long list of MAC addresses to search for, you might able to do something like this:
SELECT mac, n.sysname AS switch, i.ifname AS interface, i.vlan AS vlan FROM cam JOIN netbox n USING (netboxid) JOIN interface i ON (cam.netboxid = i.netboxid AND cam.ifindex = i.ifindex) WHERE end_time >= 'infinity' AND mac IN ('00:0b:ad:c0:ff:ee', '00:00:ca:fe:ba:be', ... );
But that list might get really long if had over 900 MAC addresses to search for. You might want to consider dumping the list of MAC addresses into a temporary table and joining with that instead of putting everything into the query each time.
-
Marcos Tadeu -
Morten Brekkevold