Custom SQL Alert not working

[dlesel]

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

[dlesel]

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;

[murrant]

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

[dlesel]

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 }}
@endif
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’] }}
@endforeach
@endif