Average CPU usage for multi-core servers

Hi guys,
I want to resurrect this topic as proposed solution does not work anymore (if worked at all).
The problem is that the suggested SQL query is invalid when preset PHP settings are used, because of strict mode enabled by default in config/database.php

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'librenms'),
            'username' => env('DB_USERNAME', 'librenms'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,

This query

SELECT *,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 and devices.os = “linux” and HAVING AVG(processors.processor_usage) > 90

works from terminal or editor, but LibreNMS complains :
SQLSTATE[42000]: Syntax error or access violation: 1055 ‘librenms.processors.processor_descr’ isn’t in GROUP BY

To make LibreNMS happy, all fields from processor table must be included (because of mode strict is set to true), but then query stops making sense because results are not grouped.

Can I disable mode strict or is there a better workaround?

Have you tried it with a macro?

lnms config:set alert.macros.rule.processors_usage_avg_perc "(SELECT AVG(%processors.processor_usage))"

1 Like

Thanks for the tip! I did not know I can define a macro and refer to it.