Im looking to write an alert for increase of latency above 20% based on the average latency to the device for the last 60/30 days.
Use case: BGP changes, ISP routes traffic all over the place, down a bad path.
I have the query working in SQL directly and returning the values I need for the alert rule.
The problem I have is that the Override SQL feature only replaces the first iteration of devices.device_id = ? with the $device_id (1012) but as this is a query with sub SELECTs these remain as devices.device_id = ? instead of devices.device_id = 1012.
/opt/librenms # ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS | 1.66
DB Schema | 2021_03_17_160729_service_templates_cleanup (201)
PHP | 7.3.21
Python | 3.8.5
MySQL | 5.7.33-log
RRDTool | 1.7.2
SNMP | NET-SNMP 5.8
====================================
[OK] Installed from the official Docker image; no Composer required
[OK] Database connection successful
SQL:
SELECT * FROM(
(
SELECT inserted, hostname, last_polled, last_ping, type, icon, last_ping_timetaken, avg
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id ) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 3 MINUTE)AND NOW())
AND
(
SELECT AVG(avg)
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id ) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 12 MINUTE) AND NOW()))
>
(
SELECT AVG(avg) * 1.2
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()))) AS avgTable,
(
SELECT AVG(avg) * 1.2 AS avg_60_day_plus_20_perc
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW())) AS avg_60_day_plus_20_percTable)
;
Librenms Overide SQL:
SELECT * FROM(
(
SELECT inserted, hostname, last_polled, last_ping, type, icon, last_ping_timetaken, avg
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id ) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 3 MINUTE)AND NOW())
AND
(
SELECT AVG(avg)
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id ) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 12 MINUTE) AND NOW()))
>
(
SELECT AVG(avg) * 1.2
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()))) AS avgTable,
(
SELECT AVG(avg) * 1.2 AS avg_60_day_plus_20_perc
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id) AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW())) AS avg_60_day_plus_20_percTable)
/opt/librenms # ./poller.php -h 1012 -d -v
Status: NOCHG
Rule #204 (High Latency - Last 12 Minutes > Base AVG 30 Days + 20%):
SQLSTATE[HY093]: Invalid parameter number (
(SQL: SELECT * FROM( (
SELECT inserted, hostname, last_polled, last_ping, type, icon, last_ping_timetaken, avg
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id ) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 3 MINUTE)AND NOW()) AND (
SELECT AVG(avg)
FROM devices, device_perf WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id ) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 12 MINUTE) AND NOW())) > (
SELECT AVG(avg) * 1.2
FROM devices, device_perf WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()))) AS avgTable, (
SELECT AVG(avg) * 1.2 AS avg_60_day_plus_20_perc
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id)
AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW())) AS avg_60_day_plus_20_percTable))
(SQL: SELECT * FROM( (
SELECT inserted, hostname, last_polled, last_ping, type, icon, last_ping_timetaken, avg
FROM devices, device_perf
WHERE (devices.device_id = 1012 AND devices.device_id = device_perf.device_id ) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 3 MINUTE)AND NOW()) AND (
SELECT AVG(avg)
FROM devices, device_perf WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id ) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 12 MINUTE) AND NOW())) > (
SELECT AVG(avg) * 1.2
FROM devices, device_perf WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id) AND
(timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()))) AS avgTable, (
SELECT AVG(avg) * 1.2 AS avg_60_day_plus_20_perc
FROM devices, device_perf
WHERE (devices.device_id = ? AND devices.device_id = device_perf.device_id)
AND (timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW())) AS avg_60_day_plus_20_percTable))
#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(75): dbFetchRows('SELECT * FROM( ...', Array)
#1 /opt/librenms/poller.php(156): LibreNMS\Alert\AlertRules->runRules(1012)
#2 {main}
Does anyone have any ideas where device_id = ? gets replaced? I assume its replaced by one of the alert php files at some point?
Am I missing something?
Is there a way to set the device_id in the query once and re-use the value?