Better output from FDB search

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.

2 Likes

Can you submit your changes in git hub as a PR?

sure I can, I understand that makes it easier to examine the changes. I was just a bit hesitant since I’m not sure my use case fits all and the changes are not production ready, there are things left to work out. But I’ll post a PR and then we can take it from there.

1 Like

I know I have a use for this, as I have had to sift through results exactly as you described. If this doesn’t break anything else, I think it would be a welcome enhancement.

Created PR https://github.com/librenms/librenms/pull/8251

1 Like

This a killer feature! I am currently using another tool accomplish this (Nedi.ch) but I would prefer to use LibreNMS for everything.

One possible improvement would be to list the port with the fewest MAC addresses, rather that the ports with a single MAC address. This is because people often have IP phones with a computer behind it, which will show up as two MAC addresses. It is also common to use small 5-port non-managed switches in places were it is hard to pull more cabling.

Thanks for your contribution!

Oops, I see you already fixed this in the Github pull request. Thanks!

its already been merged into the updates also.

First of all, kudos for this function, really likes it.
Second of all, sorry for hijacking this thread!

The only thing I’m missing is support for Cisco ASR 920, it’s running IOS-XE but it’s not like a Catalyst…
I think it should be easy for someone with insight in this function to get ASR 920 router/switch supported.

So my feature request is support for ASR 920! :slight_smile:
We got almost 500 of those routers/switches in our network.

Dose the device show as “generic OS” in LibreNMS?

No, Cisco IOS-XE same as a Catalyst 4500.

4500:
Cisco IOS-XE
03.07.00.E RELEASE SOFTWARE (fc4)

ASR 920:
Cisco IOS-XE
16.6.3

You may want to start your own thread on this…