Hi,
We have our own macros defined in config.php that we use in our alerts. The problem is that alerts using these macros break from time to time because librenms stops translating them into real sql…
Example macro in config.php:
$config[‘alert’][‘macros’][‘rule’][‘pnap_ifInRate_invalid’] = ‘((%ports.ifInOctets_rate*8) > %ports.ifSpeed)’;
When Alerts are working fine, the query in alert_rules table looks like this. (note the SQL condition after the last “AND” Operator)
query: SELECT * FROM devices,ports WHERE (devices.device_id = ? AND devices.device_id = ports.device_id) AND (((ports.ifInOctets_rate*8) / ports.ifSpeed)100) >= 90 AND (ports.ifOperStatus = “up” && ports.ifAdminStatus = “up” && (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0)) = 1 AND (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0) = 1 AND ((ports.ifInOctets_rate8) > ports.ifSpeed) = 0
When alerts stop working, The query in alert_rules table looks like this (note the SQL condition after the last “AND” Operator)
As you can see the macro is not translated in sql syntax
query: SELECT * FROM devices,ports WHERE (devices.device_id = ? AND devices.device_id = ports.device_id) AND (((ports.ifInOctets_rate*8) / ports.ifSpeed)*100) >= 90 AND (ports.ifOperStatus = “up” && ports.ifAdminStatus = “up” && (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0)) = 1 AND (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0) = 1 AND (macros.pnap_ifInRate_invalid) = 0
When I do a capture debug alerts from the console I get the following error.
SQL Error! SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘macros.pnap_ifOutRate_invalid’ in ‘where clause’ (SQL: SELECT * FROM devices,ports WHERE (devices.device_id = 171 AND devices.device_id = ports.device_id) AND (ports.ifOperStatus = “up” && ports.ifAdminStatus = “up” && (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0)) = 1 AND (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0) = 1 AND (((ports.ifOutOctets_rate*8) / ports.ifSpeed)100) >= 90 AND (macros.pnap_ifOutRate_invalid) = 0) (SQL: SELECT * FROM devices,ports WHERE (devices.device_id = 171 AND devices.device_id = ports.device_id) AND (ports.ifOperStatus = “up” && ports.ifAdminStatus = “up” && (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0)) = 1 AND (ports.deleted = 0 && ports.ignore = 0 && ports.disabled = 0) = 1 AND (((ports.ifOutOctets_rate8) / ports.ifSpeed)*100) >= 90 AND (macros.pnap_ifOutRate_invalid) = 0)
/opt/librenms/html/includes/output/query.inc.php:50
/opt/librenms/html/ajax_output.php:35
running ./daily.sh fixes the issue temporarily only to break again after some time. Any ideas on how to fix this permanently?
Thanks