====================================
Component | Version
--------- | -------
LibreNMS | 1.38-7-gee2bb8e
DB Schema | 247
PHP | 7.2.2
MySQL | 5.5.56-MariaDB
RRDTool | 1.4.8
SNMP | NET-SNMP 5.7.2
====================================
I have 2 devices groups. Each with 1 condition. And they are both populated with devices…as expected.
https://pastebin.com/raw/Umpcw4Ca
Eventually over time, like about 2-3 minutes later the 1st group becomes empty
https://pastebin.com/raw/Rm6cf18z
I would suspect that these are SQL queries which can be dynamic. But if the data never changes how is it that the query results are false when they were true at one point?
#Group2 sql query and api output (no issues with this group)
MariaDB [librenms]> select device_id from devices where location like '%CORE%';
+-----------+
| device_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 62 |
| 190 |
+-----------+
{
"status": "ok",
"groups": [
{
"id": "14",
"name": "USA-HT_CORE",
"desc": "",
"pattern": "%devices.location ~ \"%CORE%\"",
"params": null
}
],
"message": "Found 1 device groups",
"count": 1
}
group1 issues with this group which eventually becomes null (still trying to figure out the sql query for this one…)
MariaDB [librenms]> select * from device_groups;
+----+------------+------+---------------------------------------------------+--------+
| id | name | desc | pattern | params |
+----+------------+------+---------------------------------------------------+--------+
| 17 | USA-HT_ALL | | %ipv6_addresses.ipv6_compressed ~ "xxxx:xxxx:9:%" | NULL |
+----+------------+------+---------------------------------------------------+--------+
1 row in set (0.00 sec)
MariaDB [librenms]> select * from device_group_device;
+-----------------+-----------+
| device_group_id | device_id |
+-----------------+-----------+
| 17 | 1 |
| 17 | 2 |
| 17 | 3 |
| 17 | 4 |
| 17 | 5 |
| 17 | 6 |
| 17 | 7 |
| 17 | 62 |
| 17 | 84 |
| 17 | 283 |
| 17 | 284 |
+-----------------+-----------+
MariaDB [librenms]> select * from device_group_device;
Empty set (0.00 sec)
Why do i see this behavior, 1st group members disappearing (using the “ipv6_addresses.ipv6_compressed” field)?
Any input would be appreciated.
thanks!
dave
Check the poller debug it will show the actual run SQL when device groups are updated.
@murrant thank you!
ill look into that and report back 
Ran:
./poller.php -d -v -h all
output (super condensed…):
### Start Device Groups ###
SQL[SELECT * FROM device_groups ORDER BY name
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6_addresses' && COLUMN_NAME = 'device_id'
SQL[SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6_addresses' && COLUMN_NAME LIKE "%\_id"
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6s' && COLUMN_NAME = 'device_id'
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6s' && COLUMN_NAME = 'device_id'
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ports' && COLUMN_NAME = 'device_id'
SQL[SELECT DISTINCT(devices.device_id) FROM devices,ipv6_addresses,ports WHERE (( ipv6_addresses.port_id = ports.port_id && ports.device_id = devices.device_id )) && devices.device_id='1' AND (ipv6_addresses.ipv6_compressed REGEXP "2607:9
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2607:9' at line 1 (SELECT DISTINCT(devices.device_id) FROM devices,ipv6_addresses,ports WHERE (( ipv6_addresses.port_id = ports.port_id && ports.device_id = devices.device_id )) && devices.device_id='1' AND (ipv6_addresses.ipv6_compressed REGEXP "2607:9)
SQL[SELECT `device_group_id` FROM `device_group_device` WHERE `device_id`='1'
Groups Added:
Groups Removed: 19
SQL[DELETE FROM `device_group_device` WHERE `device_id`='1' AND `device_group_id` IN (19)
### End Device Groups ###
### Start Device Groups ###
SQL[SELECT * FROM device_groups ORDER BY name
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6_addresses' && COLUMN_NAME = 'device_id'
SQL[SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6_addresses' && COLUMN_NAME LIKE "%\_id"
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6s' && COLUMN_NAME = 'device_id'
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ipv6s' && COLUMN_NAME = 'device_id'
SQL[SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ports' && COLUMN_NAME = 'device_id'
SQL[SELECT DISTINCT(devices.device_id) FROM devices,ipv6_addresses,ports WHERE (( ipv6_addresses.port_id = ports.port_id && ports.device_id = devices.device_id )) && devices.device_id='2' AND (ipv6_addresses.ipv6_compressed REGEXP "2607:9
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2607:9' at line 1 (SELECT DISTINCT(devices.device_id) FROM devices,ipv6_addresses,ports WHERE (( ipv6_addresses.port_id = ports.port_id && ports.device_id = devices.device_id )) && devices.device_id='2' AND (ipv6_addresses.ipv6_compressed REGEXP "2607:9)
SQL[SELECT `device_group_id` FROM `device_group_device` WHERE `device_id`='2'
Groups Added:
Groups Removed:
### End Device Groups ###
Idk if normal but this line ipv6_addresses.ipv6_compressed REGEXP "2607:9 looks wrong. Should read ipv6_addresses.ipv6_compressed REGEXP "2607:xxxx:9 which is what the API outputs
hope this is useful
I recall someone else reporting this issue a while ago. Unfortunately I don’t remember what the answer was.
Device groups will be ported to the new system at some stage. Think you’ll have to wait for that to happen.
Thank you @laf for replying
murrant had mentioned something about “porting” device groups on discord too but that was being done to the alerting portion.
Is there a PR i can keep an eye on?
is this related?
thanks!
No PR yet as it’s not actively being done.