Mysql Stored Procedure to calculate Availability Report

[dzapata]

Hi everyone,

I wrote this stored procedure in MySQL to calculate the availability report for a trigger. You can use this, for example, to generate cake bars for specific range times.

I´m trying create the stored procedure to calculate the availability report for IT Services, but it is very hard . If anyone has that stored procedure, please share it with us.

How to call the stored procedure

CALL `Database Name`.`CalculaDisponibilidad`(TriggerID,start date, end date)

Example:
CALL `zabbix`.`CalculaDisponibilidad`(13029, UNIX_TIMESTAMP('2013-05-31 16:25:00'), UNIX_TIMESTAMP('2013-09-02 15:13:00'));

This is the stored procedure:

DELIMITER $$
CREATE DEFINER=`dzapata`@`%` PROCEDURE `CalculaDisponibilidad`(IN id_trigger BIGINT, IN period_start BIGINT, IN period_end BIGINT)
BEGIN
DECLARE VAR_EVENTID BIGINT;
DECLARE VAR_CLOCK BIGINT;
DECLARE VAR_VALUE BIGINT;
DECLARE DIFF BIGINT;
DECLARE VAR_TIME BIGINT;
DECLARE STATE BIGINT;
DECLARE STATE2 BIGINT;
DECLARE FALSE_TIME BIGINT;
DECLARE TRUE_TIME BIGINT;
DECLARE UNKNOWN_TIME BIGINT;
DECLARE finished INTEGER DEFAULT 0;
DECLARE CANTIDAD_REGISTROS INT;
DECLARE TOTAL_TIME BIGINT;
DECLARE PERCENT_TRUE DECIMAL(30,5);
DECLARE PERCENT_FALSE DECIMAL(30,5);
DECLARE PERCENT_UNKNOWN DECIMAL(30,5);
DECLARE VAR_TIEMPO_MINIMO BIGINT;
DECLARE VAR_TIEMPO_MAXIMO BIGINT;
DECLARE START_VALUE INT;


DECLARE cur_inicial CURSOR for
SELECT e.value
FROM zabbix.events e WHERE e.objectid=id_trigger AND e.object=0 AND e.clock < period_start ORDER BY e.eventid DESC LIMIT 0,1;


DECLARE cur0 CURSOR FOR
SELECT COUNT(e.eventid) AS cnt, MIN(e.clock) AS min_clock, MAX(e.clock) AS max_clock
FROM zabbix.events e
WHERE e.objectid= id_trigger AND e.object=0 AND clock >= period_start AND clock <= period_end;


DECLARE cur1 CURSOR FOR
SELECT eventid,clock,value
FROM zabbix.events
WHERE
objectid = id_trigger AND object = 0
AND clock BETWEEN period_start AND period_end
ORDER BY eventid;

DECLARE cur2 CURSOR FOR
SELECT value FROM zabbix.triggers WHERE triggerid = id_trigger;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

SET START_VALUE = -1;

OPEN cur_inicial;
FETCH cur_inicial INTO START_VALUE;
CLOSE cur_inicial;

IF START_VALUE IS NULL THEN
SET START_VALUE = -1;
ELSE
SET VAR_TIME = period_start;
END IF;


OPEN cur0;
FETCH cur0 INTO CANTIDAD_REGISTROS, VAR_TIEMPO_MINIMO, VAR_TIEMPO_MAXIMO;
CLOSE cur0;

IF CANTIDAD_REGISTROS > 0 then
IF START_VALUE = -1 THEN
SET VAR_TIME = VAR_TIEMPO_MINIMO;
END IF;
ELSE
SET VAR_TIME = 86400;
END IF;


OPEN cur2;
FETCH cur2 INTO STATE2;
CLOSE cur2;


SET STATE = START_VALUE;
SET CANTIDAD_REGISTROS = 0;
SET FALSE_TIME = 0;
SET TRUE_TIME = 0;
SET UNKNOWN_TIME = 0;


OPEN cur1;

get_email: LOOP
FETCH cur1 INTO VAR_EVENTID, VAR_CLOCK, VAR_VALUE;
IF (finished=1) THEN
LEAVE get_email;
END IF;

SET DIFF = VAR_CLOCK - VAR_TIME;
SET VAR_TIME = VAR_CLOCK;

IF STATE = -1 THEN
SET STATE = VAR_VALUE;
IF STATE = 0 THEN
SET FALSE_TIME = FALSE_TIME + DIFF;
END IF;
IF STATE = 1 THEN
SET TRUE_TIME = TRUE_TIME + DIFF;
END IF;
IF STATE = 2 THEN
SET UNKNOWN_TIME = UNKNOWN_TIME + DIFF;
END IF;

ELSEIF STATE = 0 THEN
SET FALSE_TIME = FALSE_TIME + DIFF;
SET STATE = VAR_VALUE;

ELSEIF STATE = 1 THEN

SET TRUE_TIME = TRUE_TIME + DIFF;
SET STATE = VAR_VALUE;
ELSEIF STATE = 2 THEN
SET UNKNOWN_TIME = UNKNOWN_TIME + DIFF;
SET STATE = VAR_VALUE;
END if;
SET CANTIDAD_REGISTROS = CANTIDAD_REGISTROS + 1;
END LOOP get_email;

CLOSE cur1;

IF CANTIDAD_REGISTROS = 0 THEN
SET STATE = STATE2;
END IF;





IF STATE = 0 THEN
SET FALSE_TIME = FALSE_TIME + period_end - VAR_TIME;
END IF;

IF STATE = 1 THEN
SET TRUE_TIME = TRUE_TIME + period_end - VAR_TIME;
END IF;

IF STATE = 2 THEN
SET UNKNOWN_TIME = UNKNOWN_TIME + period_end - VAR_TIME;
END IF;

SET TOTAL_TIME = TRUE_TIME + FALSE_TIME + UNKNOWN_TIME;

IF TOTAL_TIME = 0 THEN
SET TRUE_TIME = 0;
SET FALSE_TIME = 0;
SET UNKNOWN_TIME = 0;
SET PERCENT_TRUE = 0;
SET PERCENT_UNKNOWN = 100;
ELSE
SET PERCENT_TRUE = (100 * TRUE_TIME) / TOTAL_TIME;
SET PERCENT_FALSE = (100 * FALSE_TIME) / TOTAL_TIME;
SET PERCENT_UNKNOWN = (100 * UNKNOWN_TIME) / TOTAL_TIME;
END IF;

SELECT TOTAL_TIME AS Tiempo_Total, TRUE_TIME AS Tiempo_problema, PERCENT_TRUE AS porcentaje_problema,
FALSE_TIME AS Tiempo_ok, PERCENT_FALSE as porcentaje_ok,
UNKNOWN_TIME as tiempo_desconocido, PERCENT_UNKNOWN as porcentaje_desconocido;

END$$
DELIMITER ;

Regards!!!!

[wrocha]

Thank you for sharing this!! It helped me a lot!!

[fgo.oliver]

my version is still 3, will work or need changes on procedure?

[cflannigan]

Hey.. This looks promising but it does not return valid information.. I know its old so things would have changed, but does anyone have an updated version by any chance??