⚠️ Update: This fix is not applicable to later versions of LibreNMS. Please refer to the article comments below. (Updated February 2019)

Introduction

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.processorusage, 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.

Instructions

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.


Comments:

Please note: These comments are exports from an older platform, and are no longer active.

Daniel - Jun 5, 2017

Thanks!

Syn2 - Jun 4, 2017

Genius. Thank you.

laf - Sep 3, 2018

You can now put custom SQL queries into alert rules directly to achieve this. Please update and edit an existing rule -> Advanced. Docs have been updated as well. https://docs.librenms.org/#Alerting/Rules/#advanced

Patrick - Feb 4, 2019

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 - Feb 4, 2019

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

J Ty - Aug 5, 2018

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?

Kevin Krumm - Dec 3, 2017

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

Ilya - Jan 4, 2019

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;

sindre - Sep 1, 2018

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

Chris Dunkinson - Sep 1, 2018

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.

Adam - Aug 4, 2020

This still works in 2020, hovewer I have on question - do you know how to add “transports” to this rule, so I can get a notification whenever rule is applied?

pk - Feb 5, 2019

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

librenms - Dec 3, 2020

I received this error: ERROR 1364 (HY000): Field ‘builder’ doesn’t have a default value Can help me?

librenms - Jan 3, 2021

How did you get it to work?