Dynamic group based on group membership

Help
1

Trying to create a device group based on membership of other groups.

For example, i have a large group “EOL devices” and a large group “Customer X”. How can i create a dynamic group listing only devices that are member of both?

What i have tried

(device_groups.id LIKE '%54%' AND device_groups.id LIKE '%30%') # zero results
(device_groups.id = 54 AND device_groups.id = 30) # zero results
(device_groups.id IN 54 AND device_groups.id IN 30) # zero results
(device_groups.pattern LIKE '%54%' AND device_groups.pattern LIKE '%30%') # zero results

anyone more familiar with the SQL structure of LibreNMS who would like to point me in the right direction?

LibreNMS 24.8.1 / MariaDB 10.11.6 / php 8.2.20 / debian stable

2

For me this works fine:

grafik
grafik988×480 21.7 KB

I think the logical “AND” is wrong in your queries. Your description sounds more like you’ll need an “OR” there.

3

device_groups.id = 54 OR device_groups.id = 30

Gives me more 265 devices, group 54 is only 63 devices, group 30 229.

I think OR is wrong for my situation. I want all devices that are EoL AND from Customer X. Not all EoL devices plus all Customer X Devices.

4

Ah, okay, sorry! I apparenty did not read carefully enough. :wink:

I think you then would need to use the table “device_group_device”, which unfortunately is not available in the select-box for some reasons.
Also, since there are multiple lines for devices that are part in more than one group, you’ll need something like a LEFT JOIN query, which also cannot created in the UI at the moment.
Sounds like something for a feature request to me.

1 Like
5

I tried a bit around and this SQL-query should deliver what you want. Pardon for the ugly syntax, I’m far from being a SQL-pro. :wink:

SELECT d1.device_id FROM device_group_device d1, device_group_device d2 WHERE d1.device_group_id
= 54 AND d2.device_group_id = 30 AND d1.device_id = d2.device_id;

Edit: Improved query a bit.

The question is: Why is the table “device_group_device” not available in the drop-down menu? Maybe some dev could answer this?

6

I created a feature-request for the ability to use custom-SQL also for dynamic groups: Allow custom SQL-queries also in dynamic groups
I believe this could be very handy for a lot of other usecases, too.

7

Thank you very much @ChrisK928 !