Ipv4_addresses.ipv4_address NOT LIKE doesn't work

Help
1

It seem there is a bug with the device-groups. Rules with “doesn’t begint with” doesn’t work as expected.
e.g.: ipv4_addresses.ipv4_address NOT LIKE ‘10.0.1.%’
members will be 10.0.1.10 and 10.0.1.100 as well

2

Doesn’t begin with/NOT LIKE probably isn’t going to work very well with L3 or other devices that have multiple IP addresses. For that query, the ipv4_addresses table joins to the ports table which joins to the devices table. Something like…

SELECT devices.device_id, devices.hostname, ports.ifDescr, ipv4_addresses.ipv4_address
FROM devices,ports,ipv4_addresses
  WHERE (devices.device_id = ports.device_id AND ports.port_id = ipv4_addresses.port_id) 
  AND ipv4_addresses.ipv4_address NOT LIKE '10.0.1.%'

So if HostA was a router with multiple SVI’s you might get the following data from the query. Only the Vlan101 row would be filtered out by the NOT LIKE query but the other ports would still remain causing the device to end up in the device group.

| devices.device_id | devices.hostname | ports.ifDescr | ipv4_addresses.ipv4_address |
| 10                | HostA            | Vlan10        | 192.168.10.10               |
| 10                | HostA            | Vlan11        | 192.168.11.10               |
| 10                | HostA            | Vlan12        | 192.168.12.10               |
| 10                | HostA            | Vlan13        | 192.168.13.10               |
| 10                | HostA            | Vlan101       | 10.0.1.10                   |

If there’s something unique about the port you expected the IP address to be on, like it’s always a loopback interface or the ifAlias always contains “Mgmt” or something you might be able to add that as an AND condition to filter the other ports out.

3

Thanks for your answer.
You’re right. They have loopback addresses…

4

ipv4_addresses.ipv4_address NOT LIKE ‘10.0.1.%’ AND ports.port_descr_descr != “lo”
The rule above filters out a lot of devices (which does neither have “lo” interface nor IP not like ‘10.0.1.%’) :face_with_raised_eyebrow:
e.g. 10.1.31.8
MariaDB [librenms]> SELECT devices.device_id, devices.hostname, ports.ifDescr, ipv4_addresses.ipv4_address FROM devices,ports,ipv4_addresses WHERE (devices.device_id = ports.device_id AND ports.port_id = ipv4_addresses.port_id) AND hostname = “10.1.31.8”;
±----------±----------±---------±-------------+
| device_id | hostname | ifDescr | ipv4_address |
±----------±----------±---------±-------------+
| 30 | 10.1.31.8 | LOOPBACK | 127.0.0.1 |
| 30 | 10.1.31.8 | eth0 | 10.1.31.8 |
±----------±----------±---------±-------------+
2 rows in set (0.000 sec)

I don’t understand :frowning:

5

I’m not really familiar with the ports.port_descr_descr field. Seems to be mostly NULL in my databases. Maybe try ports.ifDescr or ports.ifName?

6

Yes, try to see ifAlias, ifName and ifDescr, and check which one would allow you to find the right filter. But basically, because there is no way to identify 100% which IP address is the management one on a L3 device, you probably need some other filtering solution.