diff options
Diffstat (limited to 'schema/mysql/upgrades/1.0.0.sql')
| -rw-r--r-- | schema/mysql/upgrades/1.0.0.sql | 291 |
1 files changed, 291 insertions, 0 deletions
diff --git a/schema/mysql/upgrades/1.0.0.sql b/schema/mysql/upgrades/1.0.0.sql new file mode 100644 index 0000000..16bb45e --- /dev/null +++ b/schema/mysql/upgrades/1.0.0.sql @@ -0,0 +1,291 @@ +DROP FUNCTION IF EXISTS get_sla_ok_percent; +DELIMITER // +CREATE FUNCTION get_sla_ok_percent( + in_host_id binary(20), + in_service_id binary(20), + in_start_time bigint unsigned, + in_end_time bigint unsigned +) +RETURNS decimal(7, 4) +READS SQL DATA +BEGIN + DECLARE result decimal(7, 4); + DECLARE row_event_time bigint unsigned; + DECLARE row_event_type enum('state_change', 'downtime_start', 'downtime_end', 'end'); + DECLARE row_event_prio int; + DECLARE row_hard_state tinyint unsigned; + DECLARE row_previous_hard_state tinyint unsigned; + DECLARE last_event_time bigint unsigned; + DECLARE last_hard_state tinyint unsigned; + DECLARE active_downtimes int unsigned; + DECLARE problem_time bigint unsigned; + DECLARE total_time bigint unsigned; + DECLARE done int; + DECLARE cur CURSOR FOR + ( + -- all downtime_start events before the end of the SLA interval + -- for downtimes that overlap the SLA interval in any way + SELECT + GREATEST(downtime_start, in_start_time) AS event_time, + 'downtime_start' AS event_type, + 1 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + FROM sla_history_downtime d + WHERE d.host_id = in_host_id + AND ((in_service_id IS NULL AND d.service_id IS NULL) OR d.service_id = in_service_id) + AND d.downtime_start < in_end_time + AND d.downtime_end >= in_start_time + ) UNION ALL ( + -- all downtime_end events before the end of the SLA interval + -- for downtimes that overlap the SLA interval in any way + SELECT + downtime_end AS event_time, + 'downtime_end' AS event_type, + 2 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + FROM sla_history_downtime d + WHERE d.host_id = in_host_id + AND ((in_service_id IS NULL AND d.service_id IS NULL) OR d.service_id = in_service_id) + AND d.downtime_start < in_end_time + AND d.downtime_end >= in_start_time + AND d.downtime_end < in_end_time + ) UNION ALL ( + -- all state events strictly in interval + SELECT + event_time, + 'state_change' AS event_type, + 0 AS event_prio, + hard_state, + previous_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time > in_start_time + AND s.event_time < in_end_time + ) UNION ALL ( + -- end event to keep loop simple, values are not used + SELECT + in_end_time AS event_time, + 'end' AS event_type, + 3 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + ) + ORDER BY event_time, event_prio; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; + + IF in_end_time <= in_start_time THEN + SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'end time must be greater than start time'; + END IF; + + -- Use the latest event at or before the beginning of the SLA interval as the initial state. + SELECT hard_state INTO last_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time <= in_start_time + ORDER BY s.event_time DESC + LIMIT 1; + + -- If this does not exist, use the previous state from the first event after the beginning of the SLA interval. + IF last_hard_state IS NULL THEN + SELECT previous_hard_state INTO last_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time > in_start_time + ORDER BY s.event_time ASC + LIMIT 1; + END IF; + + -- If this also does not exist, use the current host/service state. + IF last_hard_state IS NULL THEN + IF in_service_id IS NULL THEN + SELECT hard_state INTO last_hard_state + FROM host_state s + WHERE s.host_id = in_host_id; + ELSE + SELECT hard_state INTO last_hard_state + FROM service_state s + WHERE s.host_id = in_host_id + AND s.service_id = in_service_id; + END IF; + END IF; + + IF last_hard_state IS NULL THEN + SET last_hard_state = 0; + END IF; + + SET problem_time = 0; + SET total_time = in_end_time - in_start_time; + SET last_event_time = in_start_time; + SET active_downtimes = 0; + + SET done = 0; + OPEN cur; + read_loop: LOOP + FETCH cur INTO row_event_time, row_event_type, row_event_prio, row_hard_state, row_previous_hard_state; + IF done THEN + LEAVE read_loop; + END IF; + + IF row_previous_hard_state = 99 THEN + SET total_time = total_time - (row_event_time - last_event_time); + ELSEIF ((in_service_id IS NULL AND last_hard_state > 0) OR (in_service_id IS NOT NULL AND last_hard_state > 1)) + AND last_hard_state != 99 + AND active_downtimes = 0 + THEN + SET problem_time = problem_time + row_event_time - last_event_time; + END IF; + + SET last_event_time = row_event_time; + IF row_event_type = 'state_change' THEN + SET last_hard_state = row_hard_state; + ELSEIF row_event_type = 'downtime_start' THEN + SET active_downtimes = active_downtimes + 1; + ELSEIF row_event_type = 'downtime_end' THEN + SET active_downtimes = active_downtimes - 1; + END IF; + END LOOP; + CLOSE cur; + + SET result = 100 * (total_time - problem_time) / total_time; + RETURN result; +END// +DELIMITER ; + +ALTER TABLE hostgroup + DROP INDEX idx_hostroup_name, + ADD INDEX idx_hostgroup_name (name) COMMENT 'Host/service/host group list filtered by host group name'; + +ALTER TABLE notification_history + MODIFY `text` longtext NOT NULL; + +ALTER TABLE host_state + ADD COLUMN previous_soft_state tinyint unsigned NOT NULL AFTER hard_state, + CHANGE attempt check_attempt tinyint unsigned NOT NULL, + CHANGE timeout check_timeout int unsigned DEFAULT NULL; + +ALTER TABLE service_state + ADD COLUMN previous_soft_state tinyint unsigned NOT NULL AFTER hard_state, + CHANGE attempt check_attempt tinyint unsigned NOT NULL, + CHANGE timeout check_timeout int unsigned DEFAULT NULL; + +ALTER TABLE checkcommand_argument + ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if; + +ALTER TABLE eventcommand_argument + ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if; + +ALTER TABLE notificationcommand_argument + ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if; + +ALTER TABLE notification_history + ADD INDEX idx_notification_history_env_send_time (environment_id, send_time) COMMENT 'Filter for history retention'; + +ALTER TABLE acknowledgement_history + ADD INDEX idx_acknowledgement_history_env_clear_time (environment_id, clear_time) COMMENT 'Filter for history retention'; + +ALTER TABLE comment_history + ADD INDEX idx_comment_history_env_remove_time (environment_id, remove_time) COMMENT 'Filter for history retention'; + +ALTER TABLE downtime_history + ADD INDEX idx_downtime_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention'; + +ALTER TABLE flapping_history + ADD INDEX idx_flapping_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention'; + +ALTER TABLE state_history + ADD INDEX idx_state_history_env_event_time (environment_id, event_time) COMMENT 'Filter for history retention', + CHANGE attempt check_attempt tinyint unsigned NOT NULL; + +ALTER TABLE icon_image + DROP PRIMARY KEY, + MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + icon_image)', + ADD PRIMARY KEY (id); + +ALTER TABLE action_url + DROP PRIMARY KEY, + MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + action_url)', + ADD PRIMARY KEY (id); + +ALTER TABLE notes_url + DROP PRIMARY KEY, + MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notes_url)', + ADD PRIMARY KEY (id); + +ALTER TABLE customvar + MODIFY name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL; + +ALTER TABLE customvar_flat + MODIFY flatname varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Path converted with `.` and `[ ]`'; + +ALTER TABLE host + CHANGE checkcommand checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name', + CHANGE check_timeperiod + check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name', + CHANGE eventcommand eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name', + CHANGE zone zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name', + CHANGE command_endpoint command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name'; + +ALTER TABLE service + CHANGE checkcommand checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name', + CHANGE check_timeperiod + check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name', + CHANGE eventcommand eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name', + CHANGE zone zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name', + CHANGE command_endpoint command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name'; + +CREATE TABLE sla_history_state ( + id binary(20) NOT NULL COMMENT 'state_history.id (may reference already deleted rows)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + event_time bigint unsigned NOT NULL COMMENT 'unix timestamp the event occurred', + hard_state TINYINT UNSIGNED NOT NULL COMMENT 'hard state after this event', + previous_hard_state TINYINT UNSIGNED NOT NULL COMMENT 'hard state before this event', + + PRIMARY KEY (id), + + INDEX idx_sla_history_state_event (host_id, service_id, event_time) COMMENT 'Filter for calculating the sla reports', + INDEX idx_sla_history_state_env_event_time (environment_id, event_time) COMMENT 'Filter for sla history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +INSERT INTO sla_history_state + (id, environment_id, endpoint_id, object_type, host_id, service_id, event_time, hard_state, previous_hard_state) + SELECT id, environment_id, endpoint_id, object_type, host_id, service_id, event_time, hard_state, previous_hard_state + FROM state_history + WHERE state_type = 'hard' + ON DUPLICATE KEY UPDATE sla_history_state.id = sla_history_state.id; + +CREATE TABLE sla_history_downtime ( + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + downtime_id binary(20) NOT NULL COMMENT 'downtime.id (may reference already deleted rows)', + downtime_start BIGINT UNSIGNED NOT NULL COMMENT 'start time of the downtime', + downtime_end BIGINT UNSIGNED NOT NULL COMMENT 'end time of the downtime', + + PRIMARY KEY (downtime_id), + + INDEX idx_sla_history_downtime_event (host_id, service_id, downtime_start, downtime_end) COMMENT 'Filter for calculating the sla reports', + INDEX idx_sla_history_downtime_env_downtime_end (environment_id, downtime_end) COMMENT 'Filter for sla history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +INSERT INTO sla_history_downtime + (environment_id, endpoint_id, object_type, host_id, service_id, downtime_id, downtime_start, downtime_end) + SELECT environment_id, endpoint_id, object_type, host_id, service_id, downtime_id, + start_time AS downtime_start, IF(has_been_cancelled = 'y', cancel_time, end_time) AS downtime_end + FROM downtime_history + ON DUPLICATE KEY UPDATE sla_history_downtime.downtime_id = sla_history_downtime.downtime_id; + +INSERT INTO icingadb_schema (version, TIMESTAMP) + VALUES (3, CURRENT_TIMESTAMP() * 1000); |