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” &&
$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 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.
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.
DROP FUNCTION IF EXISTS PROCESSCOUNT//
CREATE FUNCTION PROCESSCOUNT(deviceId int, processName char) RETURNS int
DECLARE processCount int;
SELECT count(*) INTO processCount FROM processes WHERE device_id = deviceId AND LOWER(command) LIKE CONCAT(processName,’%’);
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.
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.