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?
Ah, okay, sorry! I apparenty did not read carefully enough.
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.
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.
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?
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.