When I do an FDB search for a mac address I get a lot of results which to me feel redundant and irrelevant. The results show every switch port where the mac address is seen, including for example port channels, when what I think the feature is implemented to do is provide a way of locating clients on your network. So what you’re interested in is basically which switch port the device is connected to.
First of all, am I correct in my reasoning above?
Here is an example output of an FDB search:
As you see there are a lot of hits which I feel are irrelevant. There are actually two rows which hold the information I’m interested in, one which points to the actual switch and port the device is connected to (Gi2/0/43) and one which hold information of the ip address (which is provided by my access aggregation switch (sw-c3750). So ideally I want to get rid of all the other rows and get one single row showing both connected switch/port and ip address.
So I did some fiddling with the sql query construction for the fdb search. With that patch I get the following result:
Observant readers might notice that the total row count is non-functioning with this patch, but otherwise the result is what I want. To achieve this I had to rewrite the sql query so it uses a sub query to get only port_id’s which has only one mac address attached to them, effectively cleaning away portchannels and other stuff. I also changed the JOIN of the ‘ipv4_mac’ table to get the ip address associated with the mac showing up on the same line.
So, the original sql query looks like this:
SELECT F.port_id AS port_id, device_id, ifInErrors, ifOutErrors, ifOperStatus, ifAdminStatus, ifAlias AS interface, ifDescr, mac_address, V.vlan_vlan AS vlan, hostname, hostname AS device , group_concat(M.ipv4_address SEPARATOR ', ') AS ipv4_address FROM ports_fdb AS F LEFT JOIN devices AS D USING(device_id) LEFT JOIN ports AS P USING(port_id, device_id) LEFT JOIN vlans AS V USING(vlan_id, device_id) LEFT JOIN ipv4_mac AS M USING (mac_address, device_id) WHERE 1 AND F.mac_address LIKE 'dc41xxxx' GROUP BY device_id, port_id, mac_address, vlan, hostname, ifAlias, ifAdminStatus, ifDescr, ifOperStatus, ifInErrors, ifOutErrors ORDER BY device asc LIMIT 0,50
While my patched one looks like this:
SELECT * FROM (SELECT F.port_id AS port_id, F.device_id, ifInErrors, ifOutErrors, ifOperStatus, ifAdminStatus, ifAlias AS interface, ifDescr, mac_address, V.vlan_vlan AS vlan, hostname, hostname AS device , group_concat(M.ipv4_address SEPARATOR ', ') AS ipv4_address FROM ports_fdb AS F LEFT JOIN devices AS D USING(device_id) LEFT JOIN ports AS P USING(port_id, device_id) LEFT JOIN vlans AS V USING(vlan_id, device_id) LEFT JOIN ipv4_mac AS M USING (mac_address) GROUP BY F.port_id HAVING COUNT(F.port_id) = 1 ) AS t WHERE 1 AND t.mac_address LIKE 'dc41xxxx' GROUP BY device_id, port_id, mac_address, vlan, hostname, interface, ifAdminStatus, ifDescr, ifOperStatus, ifInErrors, ifOutErrors ORDER BY device asc LIMIT 0,50
In essence I have added a sub query to be able to to a ‘group by’ to filter out port_id’s that have more than one mac address associated with them.
While I was at it I also added the possibility to search by ip directly on the fdb search page, so you dont have to first search ip via arp search to get the mac address, and then search with mac address on fdb search.
OK - long post. Is this something that anyone else is interested in? Maybe this would break some other use case? Also: my sql fu is bad and this is a quick hack so there are probably better ways of achieving this.
