Custom SQL Alert not working

To reproduce create rule form collection “Processor usage over 85%”.

  1. Change 85% to 1%
  2. Wait 5 min and Dashboard shows servers with CPU usage over 1%
  3. Edit rule and go to "Advance ". Chane “processors.processor_usage > 1” to “processors.processor_usage > 90”.
    Override SQL = ON
  4. Wait 5 min. Dashboard still show servers with CPU usage over 1%.
  5. Edit rule and go to "Advance ". Override SQL =OFF.
    Go to “Main”
    Change persantage to 85.
    Wait 5 min. Servres with CPU usage less than 85% will disapeare.

Actually, Rules - LibreNMS Docs
example CPUs over 10% not working.


Component Version
LibreNMS 23.9.1-122-g651a437e5 (2023-10-24T15:00:56-04:00)
DB Schema 2023_10_20_075853_cisco_asa_add_default_limits (268)
PHP 8.1.11
Python 3.6.8
Database MariaDB 10.6.10-MariaDB
RRDTool 1.7.0
SNMP 5.8

[OK] Composer Version: 2.6.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database Schema is current
[OK] SQL Server meets minimum requirements
[OK] lower_case_table_names is enabled
[OK] MySQL engine is optimal
[OK] Database and column collations are correct
[OK] Database schema correct
[OK] MySQl and PHP time match
[OK] Active pollers found
[OK] Dispatcher Service not detected
[OK] Locks are functional
[OK] Python poller wrapper is polling
[OK] Redis is unavailable
[OK] rrd_dir is writable
[OK] rrdtool version ok

Average rule for all CPUs over 10% (Rules - LibreNMS Docs) should be:
SELECT devices.device_id,ROUND(AVG(processors.processor_usage)) as cpu_avg FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 GROUP BY devices.device_id HAVING AVG(processors.processor_usage) > 10;

That does work, but might limit the data available to your alert template.

We do not need too much data in alert mail.
We must have server name and maybe Processor Average percentage number.
Alert template should be customize.
This template tested and it works:

{{ $alert->title }}
Severity: {{ $alert->severity }}
@if ($alert->state == 0) Time elapsed: {{ $alert->elapsed }}
Timestamp: {{ $alert->timestamp }}
Unique-ID: {{ $alert->uid }}
Rule: @if ($alert->name) {{ $alert->name }} @else {{ $alert->rule }} @endif
@if ($alert->faults) Faults:
@foreach ($alert->faults as $key => $value)
Processors Average: {{ $value[‘cpu_avg’] }}