Override SQL Alert Rule - device_id = ? not replaced with value

Tags: #<Tag:0x00007f84b2388a40>

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?