Alerts Blank - macros.device_down Broken - SQL Error syntax error or access violation: 1064

Help
1

One of outr installs stopped generating alerts recently after an update. This was around the 21st of Oct.

Component Version
LibreNMS 1.56-81-g259c5294a
DB Schema 2019_02_05_140857_remove_config_definition_from_db (146)
PHP 7.2.19-0ubuntu0.18.04.1
MySQL 10.1.41-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3
====================================

[OK] Composer Version: 1.9.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

We found the log full of the following error

2019-10-28 13:43:47] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 
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 ‘(devices.disabled = 0 && devices.ignore = 0)")") = 1 AND ((devices.sysObjectID R’ at line 1 (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1) (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(64): dbFetchRows(‘SELECT * FROM d…’, Array)

On investigation there appears to be a problem with the macros.device_down on one of our LibreNMS Installs;

On the non working system the Rule Builder has the following;

image
image735×208 27.3 KB

And adds the following SQL statement. The section added by the macros.device_down has double quotes it shouldn’t have e.g. (“(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)”)

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1 AND ((devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.43\.)") && (devices.os LIKE "%3Com%")) = 1 AND devices.type LIKE '%Network%'

On a working system we have the same rule but there are no spurious double quotes being added?

image
image737×209 27.4 KB

SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.43\.)") && (devices.os LIKE "%3Com%")) = 1 AND devices.type LIKE '%Network%'

The macros are built in and I think they are being pulled from /opt/librenms/misc/macros.json

I compared the file on each system and they are the same, they both have the following line.

non-working
1577351 -rw-r--r-- 1 librenms librenms 3313 Feb 15 2019 macros.json

"device_down": "(%devices.status = 0 && %macros.device)",

Working
793166 -rw-rw-r-- 1 librenms librenms 3313 Feb 14 2019 macros.json

"device_down": "(%devices.status = 0 && %macros.device)",

Why would the extra double quotes be added? How do I stop them adding it?

2

To add to this I have tested by removing the macros.devices_down statment from the rule and re-adding it.
The double quotes are still added incorrectly.

If I create a brand new rule I get the same result;

image
image1326×364 18.5 KB

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1

3

We have accidently posted this twice. Apologies. We will update the other thread.

4

Likely your database contains an incorrect definition for this macro. If you remove it, it will fall back to the one in macros.json