Alert rule with SQL Macro doesn't fire

Help
1

Hi,

I am trying to set up an alert rule that alarms when a specific process count (postgres) is higher than a specified value. As I couldn’t find anything that would alert on aggregate processes I tried to set up an SQL macro to look at the processes table for a specific device and command pattern.

I have tried the following combinations in config.php / the alert rule:

$config[‘alert’][‘macros’][‘rule’][‘postgres_count_dba’] = “select count(*) from processes where device_id = 12 and substring(lower(command),1,8) = ‘postgres’;”;

with an alert rule condition: %macros.postgres_count_dba > “10” &&

and

$config[‘alert’][‘macros’][‘rule’][‘postgres_count_dba’] = “select case when count(*) > 10 then true else false end from processes where device_id = 12 and substring(lower(command),1,8) = ‘postgres’;”;

with a condition of: %macros.postgres_count_dba = “1” &&

Neither options match and when I change the condition to something that always matches true the alert fires, and when I put desired condition back in the alert restores.

Could someone please let me know what I’m doing wrong?

Thanks,

Neil.

Validate.php output:

root@frnms1:/opt/librenms# ./validate.php

Component Version
LibreNMS 88a490a969a755c7d33deafb365c74e54768bc7e
DB Schema 192
PHP 5.5.9-1ubuntu4.17
MySQL 5.5.49-0ubuntu0.14.04.1
RRDTool 1.4.7
SNMP NET-SNMP 5.7.2

==========================================================

[OK] Database connection successful
[OK] Database schema correct
[WARN] Your local git contains modified files, this could prevent automatic updates.
Modified files:
html/pages/device/apps/freeswitch.inc.php
includes/polling/applications/freeswitch.inc.php

2

I’m not exactly sure about your use-case here, but I think this would be easier with a service check. At least I monitor some processes that way and have them graphed and alerted via LibreNMS.

See: http://docs.librenms.org/Extensions/Services/

3

Hi,

Thanks for the tip. I initially thought that as the data was right there in the DB and the macros framework seemed to support SQL that it would be the path of least resistance, but now looking at the services extension I could use that for some other stuff too to the increased setup time should pay rewards in the long run.

Cheers,

Neil.

4

You can’t currently run full SQL queries in rules / macros.

We’re contemplating adding ‘advanced’ support where you can do this but until then I don’t see a way round this.

1 Like
5

Thanks for confirming the current limitations on SQL based macros.

Although I will ultimately implement this using the Services extension I was curious as to whether it would be possible in SQL and it does appear that by converting the SQL-Side of things to a deterministic function I get what I want. I would question whether that is recommended or supported officially.

In MySQL:

DELIMITER //
DROP FUNCTION IF EXISTS PROCESSCOUNT//
CREATE FUNCTION PROCESSCOUNT(deviceId int, processName char) RETURNS int
BEGIN
DECLARE processCount int;
SELECT count(*) INTO processCount FROM processes WHERE device_id = deviceId AND LOWER(command) LIKE CONCAT(processName,’%’);
RETURN processCount;
END;//

Then in config.php:

$config[‘alert’][‘macros’][‘rule’][‘process_count_postgres’] = “PROCESSCOUNT(device_id, ‘postgres’)”;

And in the rule:

%macros.process_count_postgres > “10”

This fires, then when setting the rule to something more sensible it does not, so all good.

Another thing I noticed on my travels is that macros are a bit misleading in that the macro is combined with the condition at the point where the rule is created, so altering the macro definition after creating the rule does not cascade through into rules that use the macro, you need to re-create the condition.

Thanks all,

Neil.

1 Like
6

That’s because we process the alert rule into a sql query which is done on editing the alert rule but also with daily.sh so whilst it wouldn’t have taken effect straight away - it would have within 24 hours.