Custom SQL Alert stopped working after October 5, 2023.
This is the SQL query to monitor average CPU time:
SELECT * FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) HAVING ROUND(AVG(processors.processor_usage)) > 90 AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;
After October 5, 2023 getting errors in librenms.log:
SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field ‘status’ is used in HAVING clause (Connection: mysql, SQL: SELECT * FROM devices,processors WHERE (devices.device_id = 107 AND devices.device_id = processors.device_id) HAVING ROUND(AVG(processors.processor_usage)) > 90 AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;) (Connection: dbFacile, SQL: SELECT * FROM devices,processors WHERE (devices.device_id = 107 AND devices.device_id = processors.device_id) HAVING ROUND(AVG(processors.processor_usage)) > 90 AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(72): dbFetchRows()
#1 /opt/librenms/app/Actions/Alerts/RunAlertRulesAction.php(53): LibreNMS\Alert\AlertRules->runRules()
#2 /opt/librenms/app/Action.php(39): App\Actions\Alerts\RunAlertRulesAction->execute()
#3 /opt/librenms/app/Listeners/CheckAlerts.php(32): App\Action::execute()
#4 /opt/librenms/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(441): App\Listeners\CheckAlerts->handle()
#5 /opt/librenms/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(249): Illuminate\Events\Dispatcher->Illuminate\Events{closure}()
#6 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/helpers.php(433): Illuminate\Events\Dispatcher->dispatch()
#7 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Events/Dispatchable.php(14): event()
#8 /opt/librenms/LibreNMS/Poller.php(134): App\Events\DevicePolled::dispatch()
#9 /opt/librenms/app/Console/Commands/DevicePoll.php(44): LibreNMS\Poller->poll()
#10 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): App\Console\Commands\DevicePoll->handle()
#11 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container{closure}()
#12 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure()
#13 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(37): Illuminate\Container\BoundMethod::callBoundMethod()
#14 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\Container\BoundMethod::call()
#15 /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php(208): Illuminate\Container\Container->call()
#16 /opt/librenms/vendor/symfony/console/Command/Command.php(326): Illuminate\Console\Command->execute()
#17 /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php(178): Symfony\Component\Console\Command\Command->run()
#18 /opt/librenms/vendor/symfony/console/Application.php(1081): Illuminate\Console\Command->run()
#19 /opt/librenms/vendor/symfony/console/Application.php(320): Symfony\Component\Console\Application->doRunCommand()
#20 /opt/librenms/vendor/symfony/console/Application.php(174): Symfony\Component\Console\Application->doRun()
#21 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\Component\Console\Application->run()
#22 /opt/librenms/app/Console/Kernel.php(66): Illuminate\Foundation\Console\Kernel->handle()
#23 /opt/librenms/lnms(40): App\Console\Kernel->handle()
#24 {main}
INFO: device:poll balto (107) polled in 5.890s
[librenms@toc-libremontst ~]$ ./validate.php
| Component | Version |
|---|---|
| LibreNMS | 23.9.1-74-g5745fcb1f (2023-10-11T11:21:12-04:00) |
| DB Schema | 2023_10_07_170735_increase_processes_cputime_length (261) |
| 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
