I have created the following rule that does not seem to work:
syslog.timestamp >= macros.past_5m AND syslog.msg REGEXP ".*authentication failure.*"
And put backticks around macro.past_5m
to have the GUI correctly parse it as suggested here
The SQL query is the following:
SELECT * FROM devices,syslog WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND syslog.msg REGEXP ".*authentication failure.*"
And the result I get:
MariaDB [librenms]> SELECT * FROM devices,syslog WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND syslog.msg REGEXP ".*authentication failure.*";
ERROR 1064 (42000): 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 '? AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB...' at line 1
/opt/librenms $ ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS | 21.9.1
DB Schema | 2021_25_01_0129_isis_adjacencies_nullable (217)
PHP | 7.4.24
Python | 3.9.5
MySQL | 10.6.4-MariaDB-1:10.6.4+maria~focal
RRDTool | 1.7.2
SNMP | NET-SNMP 5.9
====================================
[OK] Installed from the official Docker image; no Composer required
[OK] Database connection successful
[OK] Database schema correct
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
[WARN] Updates are managed through the official Docker image
/opt/librenms $