maya.public

Parameters

Name	Type	Mode
timetolive	interval	IN

Definition

/*
v20160725 To reset the alert counters so they only count alerts not older than timetolive
*/
DECLARE
-- lowerbound date;
BEGIN
SET temp_buffers = '64MB';
SET timezone = 'UTC';
SET statement_timeout = '20 s';
--lowerbound := now() - timetolive::interval;
CREATE TEMPORARY table t AS
SELECT  d.iddevice, ac.idclient,
        sum(CASE WHEN ac.leveltype = 2 THEN 1 ELSE 0 END) AS totalalerts,
        sum(CASE WHEN ac.leveltype = 1 THEN 1 ELSE 0 END) AS totalnotificaciones,
        sum(CASE WHEN ac.leveltype = 3 THEN 1 ELSE 0 END) AS totalmaintenance
FROM device d JOIN alert_recent a ON a.iddevice = d.iddevice
JOIN alertconf ac ON ac.idalertconf = a.idalertconf
JOIN unit u ON u.idunit = d.idunit
WHERE a.enabled = true AND u.enabled = true AND d.enabled = true
GROUP BY 1,2
;

WITH  q as (UPDATE mvw_device_alert da SET totalalerts = t.totalalerts, totalnotificaciones = t.totalnotificaciones,totalmaintenance = t.totalmaintenance  FROM t WHERE t.idclient = da.idclient AND t.iddevice = da.iddevice RETURNING t.*), q1 as (INSERT INTO mvw_device_alert SELECT * FROM t EXCEPT select * from q RETURNING *) DELETE FROM mvw_device_alert da USING t where da.iddevice not in (select coalesce(iddevice,0) from t);
END;

ot_rw_alerts_resetcounters(interval)

Parameters

Definition