summaryrefslogtreecommitdiffstats
path: root/schema/pgsql
diff options
context:
space:
mode:
Diffstat (limited to 'schema/pgsql')
-rw-r--r--schema/pgsql/schema.sql14
-rw-r--r--schema/pgsql/upgrades/1.2.0.sql153
-rw-r--r--schema/pgsql/upgrades/optional/1.2-0-history.sql3
3 files changed, 163 insertions, 7 deletions
diff --git a/schema/pgsql/schema.sql b/schema/pgsql/schema.sql
index 9027fac..708c914 100644
--- a/schema/pgsql/schema.sql
+++ b/schema/pgsql/schema.sql
@@ -166,7 +166,7 @@ BEGIN
END IF;
END LOOP;
- RETURN 100 * (total_time - problem_time) / total_time;
+ RETURN (100 * (total_time - problem_time)::decimal / total_time)::decimal(7, 4);
END;
$$;
@@ -405,7 +405,7 @@ CREATE TABLE host_state (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL,
severity smalluint NOT NULL,
output text DEFAULT NULL,
@@ -675,7 +675,7 @@ CREATE TABLE service_state (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL,
severity smalluint NOT NULL,
output text DEFAULT NULL,
@@ -1846,7 +1846,7 @@ CREATE TABLE state_history (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL, -- may be a tinyuint, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112
output text DEFAULT NULL,
long_output text DEFAULT NULL,
max_check_attempts uint NOT NULL,
@@ -2074,7 +2074,7 @@ ALTER TABLE history ALTER COLUMN comment_history_id SET STORAGE PLAIN;
ALTER TABLE history ALTER COLUMN flapping_history_id SET STORAGE PLAIN;
ALTER TABLE history ALTER COLUMN acknowledgement_history_id SET STORAGE PLAIN;
-CREATE INDEX idx_history_event_time ON history(event_time);
+CREATE INDEX idx_history_event_time_event_type ON history(event_time, event_type);
CREATE INDEX idx_history_acknowledgement ON history(acknowledgement_history_id);
CREATE INDEX idx_history_comment ON history(comment_history_id);
CREATE INDEX idx_history_downtime ON history(downtime_history_id);
@@ -2095,7 +2095,7 @@ COMMENT ON COLUMN history.comment_history_id IS 'comment_history.comment_id';
COMMENT ON COLUMN history.flapping_history_id IS 'flapping_history.id';
COMMENT ON COLUMN history.acknowledgement_history_id IS 'acknowledgement_history.id';
-COMMENT ON INDEX idx_history_event_time IS 'History filtered/ordered by event_time';
+COMMENT ON INDEX idx_history_event_time_event_type IS 'History filtered/ordered by event_time/event_type';
COMMENT ON INDEX idx_history_host_service_id IS 'Host/service history detail filter';
CREATE TABLE sla_history_state (
@@ -2181,4 +2181,4 @@ CREATE TABLE icingadb_schema (
ALTER SEQUENCE icingadb_schema_id_seq OWNED BY icingadb_schema.id;
INSERT INTO icingadb_schema (version, timestamp)
- VALUES (2, extract(epoch from now()) * 1000);
+ VALUES (3, extract(epoch from now()) * 1000);
diff --git a/schema/pgsql/upgrades/1.2.0.sql b/schema/pgsql/upgrades/1.2.0.sql
new file mode 100644
index 0000000..2203ffb
--- /dev/null
+++ b/schema/pgsql/upgrades/1.2.0.sql
@@ -0,0 +1,153 @@
+CREATE OR REPLACE FUNCTION get_sla_ok_percent(
+ in_host_id bytea20,
+ in_service_id bytea20,
+ in_start_time biguint,
+ in_end_time biguint
+)
+RETURNS decimal(7, 4)
+LANGUAGE plpgsql
+STABLE
+PARALLEL RESTRICTED
+AS $$
+DECLARE
+ last_event_time biguint := in_start_time;
+ last_hard_state tinyuint;
+ active_downtimes uint := 0;
+ problem_time biguint := 0;
+ total_time biguint;
+ row record;
+BEGIN
+ IF in_end_time <= in_start_time THEN
+ RAISE 'end time must be greater than start time';
+ END IF;
+
+ total_time := in_end_time - in_start_time;
+
+ -- 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
+ last_hard_state := 0;
+ END IF;
+
+ FOR row IN
+ (
+ -- 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::tinyuint AS hard_state,
+ NULL::tinyuint 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::tinyuint AS hard_state,
+ NULL::tinyuint 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::tinyuint AS hard_state,
+ NULL::tinyuint AS previous_hard_state
+ )
+ ORDER BY event_time, event_prio
+ LOOP
+ IF row.previous_hard_state = 99 THEN
+ 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
+ problem_time := problem_time + row.event_time - last_event_time;
+ END IF;
+
+ last_event_time := row.event_time;
+ IF row.event_type = 'state_change' THEN
+ last_hard_state := row.hard_state;
+ ELSEIF row.event_type = 'downtime_start' THEN
+ active_downtimes := active_downtimes + 1;
+ ELSEIF row.event_type = 'downtime_end' THEN
+ active_downtimes := active_downtimes - 1;
+ END IF;
+ END LOOP;
+
+ RETURN (100 * (total_time - problem_time)::decimal / total_time)::decimal(7, 4);
+END;
+$$;
+
+CREATE INDEX CONCURRENTLY idx_history_event_time_event_type ON history(event_time, event_type);
+COMMENT ON INDEX idx_history_event_time_event_type IS 'History filtered/ordered by event_time/event_type';
+
+DROP INDEX idx_history_event_time;
+
+ALTER TABLE host_state ALTER COLUMN check_attempt TYPE uint;
+
+ALTER TABLE service_state ALTER COLUMN check_attempt TYPE uint;
+
+COMMENT ON COLUMN state_history.check_attempt IS 'optional schema upgrade not applied yet, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112';
+
+INSERT INTO icingadb_schema (version, timestamp)
+ VALUES (3, extract(epoch from now()) * 1000);
diff --git a/schema/pgsql/upgrades/optional/1.2-0-history.sql b/schema/pgsql/upgrades/optional/1.2-0-history.sql
new file mode 100644
index 0000000..ea95765
--- /dev/null
+++ b/schema/pgsql/upgrades/optional/1.2-0-history.sql
@@ -0,0 +1,3 @@
+ALTER TABLE state_history ALTER COLUMN check_attempt TYPE uint;
+
+COMMENT ON COLUMN state_history.check_attempt IS NULL;
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-25 05:43:45 +0000