Skip to content

Using LibreNMS for server OS monitoring, part 3: The aggregate CPU alert hack

Reading time: 2 minutes

Important! This fix is not applicable to later versions of LibreNMS. Please refer to the article comments below. (Updated February 2019)

At the moment, LibreNMS only allows for triggering reliable alerts on single CPU devices. If a system has multiple CPU’s, and you set up an alert rule using processors.processor_usage, it will not trigger on average CPU utilization, but rather trigger if any one CPU matches the set criteria. This is a problem when you’re monitoring multi-core servers. To work around it, I’ve made this hack.

When you create an alert rule it basically generates and stores an SQL statement in the database, which is checked every time LibreNMS polls a device. But the GUI does not allow for complex statements, such as calculating an average value. So my solution is to simply create a more complex statement and insert it directly into the database.

While this approach seems to work as intended, the downside is that you’re unable to edit the rule from the GUI after inserting it. So once you’ve inserted it, you’ll have to delete it and insert a new one if you want to make adjustments to it. If you make any changes to it from the GUI, the rule will break upon saving.

I’ve created a statement generator which puts together the SQL statement. And if you’re unfamiliar with editing SQL databases, here’s a step by step guide on how to apply this rule to your LibreNMS installation.

Prerequisites

This assumes that you have command line access to your server, and know the MySQL database username and password. You can find it in the top of your config.php file, or by running this command:

cat /opt/librenms/config.php | grep db_

Generate a SQL statement

Generate a custom statement using this generator.LibreNMS screenshot

Log in to the MySQL/MariaDB server

And enter the database password when prompted.

mysql -h <hostname> -u <username> -p

Insert the SQL statement

Copy and paste the SQL statement from the generator into the MySQL command line prompt, and press enter. If you’re successful, the output message will be “Query OK, 1 row affected”.
Exit the MySQL CLI by entering “exit”.

All done!

Your new rule should now be listed in the Alert Rules list.
LibreNMS screenshot

If you only want this rule to apply to devices matching a certain criteria, you can use Rule Mapping to set up additional rules.

11 Comments

  1. Syn2 Syn2

    Genius. Thank you.

  2. Daniel Daniel

    Thanks!

  3. Kevin Krumm Kevin Krumm

    Thank you – You should really submit this LibreNMS GitHub repo.

  4. J Ty J Ty

    Hi,

    There’s no device_id column in the alert_rules table, error shown below after I copied and pasted the output from https://sindrelindstad.com/projects/tools/alertgen

    ERROR 1054 (42S22): Unknown column ‘device_id’ in ‘field list’

    Is this still applicable with the current version/schema?

    • Chris Dunkinson Chris Dunkinson

      It looks like the “device_id” column is now called “id”

      I modified the INSERT statement to use “id” as the column name and ’38’ as the value based on us already having alert rules with an ID up to ’37’:

      INSERT INTO librenms.alert_rules (id, rule, severity, extra, disabled, name, query) VALUES (’38’,

      It seems to have worked ok.

      • sindre sindre

        Thanks for the tip! I have now updated the column name to “id”.

  5. Your query select wrong CPU! Here is my edit of default rule with your idea:

    SELECT * FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) HAVING ROUND(AVG(processors.processor_usage)) > 85 AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;

  6. Patrick Patrick

    Im getting an error in my librenms log after ctreating the alert in the mysql\mariadb

    [2019-02-17 06:26:53] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘L:\\ Label:Logs Serial Number 906bf7dc”” AND storage.storage_descr != “”/vmfs/vo’ at line 1 (SQL: SELECT * FROM devices,storage WHERE (devices.device_id = 100 AND devices.device_id = storage.device_id) AND devices.type = “Server” AND storage.storage_descr NOT LIKE ‘%memory%’ AND storage.storage_perc >= 90 AND storage.storage_descr != “”L:\\ Label:Logs Serial Number 906bf7dc”” AND storage.storage_descr != “”/vmfs/volumes/55916302-0f9fa955-4b78-8cdcd4ae1b40″”) (SQL: SELECT * FROM devices,storage WHERE (devices.device_id = 100 AND devices.device_id = storage.device_id) AND devices.type = “Server” AND storage.storage_descr NOT LIKE ‘%memory%’ AND storage.storage_perc >= 90 AND storage.storage_descr != “”L:\\ Label:Logs Serial Number 906bf7dc”” AND storage.storage_descr != “”/vmfs/volumes/55916302-0f9fa955-4b78-8cdcd4ae1b40″”)
    /opt/librenms/includes/alerts.inc.php:186

    This was the generated code

    INSERT INTO librenms.alert_rules (id, rule, severity, extra, disabled, name, query) VALUES (‘-1’, ‘Do not edit this rule!’, ‘critical’, ‘{“mute”:false,”count”:”1″,”delay”:1800,”invert”:false,”interval”:300}’, 0, ‘Aggregate CPU utilization over 90%’, ‘SELECT * FROM librenms.devices WHERE devices.status = 1 AND devices.device_id = (SELECT device_id FROM librenms.processors WHERE (SELECT ROUND(AVG(processor_usage)) FROM librenms.processors WHERE (processors.device_id = ?) LIMIT 1) > 90 LIMIT 1)’);

    • Patrick Patrick

      Nevermind Im looking at the wrong part of the log doing too many things at once. Please remove the post. Thanks

  7. pk pk

    Im seeing this error in the librenms.log after adding the alert to the database. It comes up multiple times with different device IDs
    [2019-02-22 07:11:02] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘edit this rule!)’ at line 1 (SQL: SELECT * FROM devices WHERE (devices.device_id = 22) && (Do not edit this rule!)) (SQL: SELECT * FROM devices WHERE (devices.device_id = 22) && (Do not edit this rule!))
    /opt/librenms/includes/alerts.inc.php:186

Leave a Reply to sindre Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.