Adding upstream version 0.10.0.upstream/0.10.0upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

19 files changed, 357 insertions, 0 deletions

diff --git a/schema/postgresql/t/00-create-db.sql b/schema/postgresql/t/00-create-db.sql
new file mode 100644
index 0000000..5e0e78a
--- /dev/null
+++ b/schema/postgresql/t/00-create-db.sql
@@ -0,0 +1,4 @@
+--create database icinga2;
+CREATE EXTENSION IF NOT EXISTS pgtap;
+SELECT plan(1);
+SELECT is(count(*) , 1::bigint,'Extension pg_tap installed') FROM pg_extension WHERE extname = 'pgtap';
diff --git a/schema/postgresql/t/01-install.t b/schema/postgresql/t/01-install.t
new file mode 100644
index 0000000..03ae35d
--- /dev/null
+++ b/schema/postgresql/t/01-install.t
@@ -0,0 +1,2 @@
+SELECT plan(1);
+\i ../install_all.psql
diff --git a/schema/postgresql/t/01-statehistory.sql b/schema/postgresql/t/01-statehistory.sql
new file mode 100644
index 0000000..1a1a5e2
--- /dev/null
+++ b/schema/postgresql/t/01-statehistory.sql
@@ -0,0 +1,7 @@
+SELECT plan(0);
+CREATE TABLE icinga_statehistory (
+    state_time timestamp WITHOUT time zone,
+    object_id numeric DEFAULT '0'::numeric,
+    state smallint DEFAULT '0'::smallint,
+    state_type smallint DEFAULT '0'::smallint
+);
diff --git a/schema/postgresql/t/02-icinga_objects.sql b/schema/postgresql/t/02-icinga_objects.sql
new file mode 100644
index 0000000..74d622b
--- /dev/null
+++ b/schema/postgresql/t/02-icinga_objects.sql
@@ -0,0 +1,5 @@
+SELECT plan(0);
+CREATE TABLE icinga_objects (
+    object_id numeric DEFAULT '0'::numeric,
+    objecttype_id smallint DEFAULT '1'::smallint
+);
diff --git a/schema/postgresql/t/02-servicestatus.sql b/schema/postgresql/t/02-servicestatus.sql
new file mode 100644
index 0000000..d94bc0c
--- /dev/null
+++ b/schema/postgresql/t/02-servicestatus.sql
@@ -0,0 +1,7 @@
+SELECT plan(0);
+CREATE TABLE icinga_servicestatus (
+    service_object_id numeric DEFAULT '0'::numeric,
+    status_update_time timestamp WITHOUT time zone,
+    current_state smallint DEFAULT '0'::smallint,
+    state_type smallint DEFAULT '0'::smallint
+);
diff --git a/schema/postgresql/t/03-hoststatus.sql b/schema/postgresql/t/03-hoststatus.sql
new file mode 100644
index 0000000..3f6b4e8
--- /dev/null
+++ b/schema/postgresql/t/03-hoststatus.sql
@@ -0,0 +1,7 @@
+SELECT plan(0);
+CREATE TABLE icinga_hoststatus (
+    host_object_id numeric DEFAULT '0'::numeric,
+    status_update_time timestamp WITHOUT time zone,
+    current_state smallint DEFAULT '0'::smallint,
+    state_type smallint DEFAULT '0'::smallint
+);
diff --git a/schema/postgresql/t/04-icinga_downtimehistory.sql b/schema/postgresql/t/04-icinga_downtimehistory.sql
new file mode 100644
index 0000000..7a9b8b6
--- /dev/null
+++ b/schema/postgresql/t/04-icinga_downtimehistory.sql
@@ -0,0 +1,10 @@
+SELECT plan(0);
+CREATE TABLE icinga_downtimehistory (
+    object_id numeric DEFAULT '0'::numeric,
+    entry_time timestamp WITHOUT time zone,
+    scheduled_start_time timestamp WITHOUT time zone,
+    scheduled_end_time timestamp WITHOUT time zone,
+    was_started smallint DEFAULT '0'::smallint,
+    actual_start_time timestamp WITHOUT time zone,
+    actual_end_time timestamp WITHOUT time zone
+);
diff --git a/schema/postgresql/t/04-icinga_outofsla_periods.sql b/schema/postgresql/t/04-icinga_outofsla_periods.sql
new file mode 100644
index 0000000..13bd13b
--- /dev/null
+++ b/schema/postgresql/t/04-icinga_outofsla_periods.sql
@@ -0,0 +1,6 @@
+SELECT plan(0);
+CREATE TABLE icinga_outofsla_periods (
+    timeperiod_object_id numeric NOT NULL,
+    start_time timestamp WITHOUT time zone NOT NULL,
+    end_time timestamp WITHOUT time zone NOT NULL
+);
diff --git a/schema/postgresql/t/05-fill-downtimes.sql b/schema/postgresql/t/05-fill-downtimes.sql
new file mode 100644
index 0000000..f0c77f4
--- /dev/null
+++ b/schema/postgresql/t/05-fill-downtimes.sql
@@ -0,0 +1,5 @@
+-- Objects get a number and a type (1=host, 2=server)
+SELECT plan(1);
+INSERT INTO icinga_downtimehistory (object_id,actual_start_time,actual_end_time) VALUES (7,'2019-04-15 11:45:00','2019-04-15 11:50:00');
+INSERT INTO icinga_downtimehistory (object_id,actual_start_time,actual_end_time) VALUES (7,'2019-04-15 12:00:00','2019-04-15 12:05:00');
+SELECT is(count(*), 2::bigint, 'icinga_downtimehistory has correct # of rows') FROM icinga_downtimehistory;
diff --git a/schema/postgresql/t/05-fill-hoststatus.sql b/schema/postgresql/t/05-fill-hoststatus.sql
new file mode 100644
index 0000000..caca8fb
--- /dev/null
+++ b/schema/postgresql/t/05-fill-hoststatus.sql
@@ -0,0 +1,17 @@
+-- Rows are: hostid,timestamp, status (0 up, 1 down), state_type (always use 1)
+SELECT plan(1);
+COPY icinga_hoststatus FROM STDIN;
+1	2019-02-10 12:00:00+01	0	1
+1	2019-03-10 15:00:00+01	1	1
+3	2019-03-10 16:15:00+01	1	1
+4	2020-03-01 00:00:00+01	1	1
+5	2020-04-01 14:00:00+01	0	1
+6	2019-04-01 13:51:17+01	0	1
+7	2019-04-14 11:00:00+01	0	1
+7	2019-04-15 11:00:00+01	1	1
+7	2019-04-15 12:52:00+01	0	1
+7	2019-04-15 12:55:00+01	1	1
+7	2019-04-15 12:57:00+01	0	1
+\.
+
+SELECT is(count(*), 11::bigint, 'icinga_hoststatus has correct # of rows') FROM icinga_hoststatus;
diff --git a/schema/postgresql/t/05-fill-icinga_objects.sql b/schema/postgresql/t/05-fill-icinga_objects.sql
new file mode 100644
index 0000000..1b11eef
--- /dev/null
+++ b/schema/postgresql/t/05-fill-icinga_objects.sql
@@ -0,0 +1,10 @@
+-- Objects get a number and a type (1=host, 2=server)
+SELECT plan(1);
+INSERT INTO icinga_objects VALUES (1,1);
+INSERT INTO icinga_objects VALUES (2,2);
+INSERT INTO icinga_objects VALUES (3,1);
+INSERT INTO icinga_objects VALUES (4,1);
+INSERT INTO icinga_objects VALUES (5,1);
+INSERT INTO icinga_objects VALUES (6,1);
+INSERT INTO icinga_objects VALUES (7,1);
+SELECT is(count(*), 7::bigint, 'icinga_objects has correct # of rows') FROM icinga_objects;
diff --git a/schema/postgresql/t/05-fill-servicestatus.sql b/schema/postgresql/t/05-fill-servicestatus.sql
new file mode 100644
index 0000000..5ae413f
--- /dev/null
+++ b/schema/postgresql/t/05-fill-servicestatus.sql
@@ -0,0 +1,7 @@
+SELECT plan(1);
+COPY icinga_servicestatus FROM STDIN;
+2	2019-02-10 12:00:00+01	0	1
+2	2019-03-10 15:00:00+01	2	1
+\.
+SELECT is(count(*), 2::bigint, 'icinga_servicestatus has 2 rows') FROM icinga_servicestatus;
+
diff --git a/schema/postgresql/t/05-fill-statehistory.sql b/schema/postgresql/t/05-fill-statehistory.sql
new file mode 100644
index 0000000..f66afbd
--- /dev/null
+++ b/schema/postgresql/t/05-fill-statehistory.sql
@@ -0,0 +1,28 @@
+-- Rows are: state_time, object_id, state, state_type
+SELECT plan(1);
+COPY icinga_statehistory FROM STDIN;
+2019-02-01 00:00:00+01	1	0	1
+2019-02-05 11:00:00+01	1	3	0
+2019-02-05 12:00:00+01	1	3	1
+2019-02-05 13:00:00+01	1	0	0
+2019-02-05 14:00:00+01	1	0	1
+2019-03-01 00:00:00+01	1	0	1
+2019-03-05 11:00:00+01	1	3	0
+2019-03-05 12:00:00+01	1	3	1
+2019-02-01 00:00:00+01	2	0	1
+2019-02-05 11:00:00+01	2	3	0
+2019-02-05 12:00:00+01	2	3	1
+2019-02-05 13:00:00+01	2	0	0
+2019-02-05 14:00:00+01	2	0	1
+2019-03-01 00:00:00+01	2	0	1
+2019-03-05 11:00:00+01	2	3	0
+2019-03-05 12:00:00+01	2	3	1
+2019-03-10 16:05:00+01	3	1	0
+2019-03-10 16:10:00+01	3	1	1
+2020-03-01 14:00:00+01	4	0	1
+2020-04-01 00:00:00+01	5	1	1
+2019-03-31 21:50:48+01	6	1	1
+2019-03-27 15:15:42+01	6	0	1
+2019-03-27 01:00:00+01	7	0	1
+\.
+SELECT is(count(*), 23::bigint, 'icinga_statehistory has correct # of rows rows') FROM icinga_statehistory;
diff --git a/schema/postgresql/t/06-get_sla_ok_percent.sql b/schema/postgresql/t/06-get_sla_ok_percent.sql
new file mode 100644
index 0000000..4ce9e39
--- /dev/null
+++ b/schema/postgresql/t/06-get_sla_ok_percent.sql
@@ -0,0 +1,3 @@
+SELECT plan(1);
+\i get_sla_ok_percent.sql
+SELECT is(COUNT(*),1::bigint) FROM pg_catalog.pg_proc WHERE proname = 'idoreports_get_sla_ok_percent';
diff --git a/schema/postgresql/t/07-test-func.sql b/schema/postgresql/t/07-test-func.sql
new file mode 100644
index 0000000..5c370e3
--- /dev/null
+++ b/schema/postgresql/t/07-test-func.sql
@@ -0,0 +1,33 @@
+SELECT plan(27);
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 12:00', '2019-02-05 14:00')::float , 0.0::float,'Host 1 was down 2 out of 2 hours');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 10:00', '2019-02-05 14:00')::float , 50.0::float,'Host 1 was down 2 out of 4 hours');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 10:00', '2019-02-05 18:00')::float , 75.0::float,'Host 1 was down 2 out of 8 hours');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-04 10:00', '2019-02-04 18:00')::float , 100.0::float,'Host 1 was not down before 02/05 12:00');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-06 10:00', '2019-02-08 18:00')::float , 100.0::float,'Host 1 was not down after 02/05 14:00');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-04 13:00', '2019-02-05 13:00')::float , 95.83333333333333::float,'Host 1 was down for the last hour of checked timeframe');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 13:00', '2019-02-06 13:00')::float , 95.83333333333333::float,'Host 1 was down for the first hour of checked timeframe');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 11:00', '2019-03-05 13:00')::float , 50.0::float,'Host 1 was down 1 out of 2 hours');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 12:00', '2019-03-05 13:00')::float , 0.0::float,'Host 1 was down during that period');
+SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 13:00', '2019-03-05 14:00')::float , 0.0::float,'Host 1 was down during that period');
+
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 12:00', '2019-02-05 14:00')::float , 0.0::float,'Service 2 was down 2 out of 2 hours');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 10:00', '2019-02-05 14:00')::float , 50.0::float,'Service 2 was down 2 out of 4 hours');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 10:00', '2019-02-05 18:00')::float , 75.0::float,'Service 2 was down 2 out of 8 hours');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-04 10:00', '2019-02-04 18:00')::float , 100.0::float,'Service 2 was not down before 02/05 12:00');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-06 10:00', '2019-02-08 18:00')::float , 100.0::float,'Service 2 was not down after 02/05 14:00');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-04 13:00', '2019-02-05 13:00')::float , 95.83333333333333::float,'Service 2 was down for the last hour of checked timeframe');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 13:00', '2019-02-06 13:00')::float , 95.83333333333333::float,'Service 2 was down for the first hour of checked timeframe');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 11:00', '2019-03-05 13:00')::float , 50.0::float,'Service 2 was down 1 out of 2 hours');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 12:00', '2019-03-05 13:00')::float , 0.0::float,'Service 2 was down during that period');
+SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 13:00', '2019-03-05 14:00')::float , 0.0::float,'Service 2 was down during that period');
+
+SELECT is(idoreports_get_sla_ok_percent(3,'2019-03-10 17:00', '2019-03-11 00:00')::float , 0.0::float,'Host 3 was considered down for the rest of the day');
+
+SELECT is(idoreports_get_sla_ok_percent(4,'2020-03-01 12:00', '2020-03-01 16:00')::float , 50.0::float,'Host 4 was considered down for 2 hours in a 4 hours time range starting with UP');
+SELECT is(idoreports_get_sla_ok_percent(5,'2020-04-01 12:00', '2020-04-01 16:00')::float , 50.0::float,'Host 5 was considered down for 2 hours in a 4 hours time range starting with DOWN');
+
+SELECT is(idoreports_get_sla_ok_percent(6,'2019-04-01 11:43:16','2019-04-01 15:43:16')::float , 46.65972222222222::float,'Host 6 was down until recently');
+
+SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 10:00:00','2019-04-15 15:00:00')::float , 95.0::float,'Host 7 had a planned downtime but went down before that started');
+SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 11:45:01','2019-04-15 11:49:59')::float , 100.0::float,'Host 7 had a planned downtime');
+SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 00:00:00','2019-04-16 00:00:00')::float , 80.0::float,'Host 7 had two planned downtimes which were correctly assessed');
diff --git a/schema/postgresql/t/README.md b/schema/postgresql/t/README.md
new file mode 100644
index 0000000..5467be9
--- /dev/null
+++ b/schema/postgresql/t/README.md
@@ -0,0 +1,18 @@
+Tests for the idoreports_get_sla_ok_percent() function
+======================================================
+
+These are pg_tap tests. You need pg_tap installed for the PG version you want to test on, e.g. "postgresql-12-pgtap" for Debian/Ubuntu. 
+
+I used these to find the cause for some seamingly strange NULL results.
+Which were basically due to a badly chosen ALIAS ("state_time" on line 20, which is now "state_time_").
+
+I run these tests on an Ubuntu/Debian system with "pg_virtualenv" like this:
+
+```
+pg_virtualenv -s pg_prove t/0*.sql
+```
+or simply
+```
+t/testme.sh
+```
+
diff --git a/schema/postgresql/t/get_sla_ok_percent.sql b/schema/postgresql/t/get_sla_ok_percent.sql
new file mode 120000
index 0000000..69aa266
--- /dev/null
+++ b/schema/postgresql/t/get_sla_ok_percent.sql
@@ -0,0 +1 @@
+../get_sla_ok_percent.sql
\ No newline at end of file
diff --git a/schema/postgresql/t/plain_sql_zum_debuggen.sql b/schema/postgresql/t/plain_sql_zum_debuggen.sql
new file mode 100644
index 0000000..7adf80e
--- /dev/null
+++ b/schema/postgresql/t/plain_sql_zum_debuggen.sql
@@ -0,0 +1,185 @@
+--SELECT is(idoreports_get_sla_ok_percent(4,'2020-03-01 12:00', '2020-03-01 16:00')::float , 50.0::float,'Host 4 was considered down for 2 hours in a 4 hours time range starting with UP');
+--SELECT is(idoreports_get_sla_ok_percent(5,'2020-04-01 12:00', '2020-04-01 16:00')::float , 50.0::float,'Host 5 was considered down for 2 hours in a 4 hours time range starting with DOWN');
+\set id 5
+\set start '2020-04-01 12:00'
+\set end '2020-04-01 16:00'
+\set sla_id null 
+
+--'2019-02-19 00:00:00','2019-02-20 10:00:00'
+--12347
+
+WITH crit AS (
+	SELECT CASE objecttype_id
+		WHEN 1 THEN 0
+		WHEN 2 THEN 1
+		END
+	AS value
+	FROM icinga_objects 
+	WHERE object_id = :id
+),
+before AS (
+	-- low border, last event before the range we are looking for:
+	SELECT down, state_time_ AS state_time,state FROM (
+	(SELECT 1 AS prio
+		,state > crit.value AS down
+		,GREATEST(state_time,:'start') AS state_time_
+		,state
+	FROM icinga_statehistory,crit
+	WHERE 
+		object_id = :id
+	   AND	state_time < :'start'
+	   AND  state_type = 1
+	ORDER BY state_time DESC
+	LIMIT 1)
+	UNION ALL
+	(SELECT 2 AS prio
+		,state > crit.value AS down
+		,GREATEST(state_time,:'start') AS state_time_
+		,state
+	FROM icinga_statehistory,crit 
+	WHERE 
+		object_id = :id
+	   AND	state_time < :'start'
+	ORDER BY state_time DESC
+	LIMIT 1)
+
+	) ranked ORDER BY prio 
+	LIMIT 1
+)  SELECT * FROM before;
+,all_hard_events AS (
+	-- the actual range we're looking for:
+	SELECT state > crit.value AS down
+		,state_time
+		,state
+	FROM icinga_statehistory,crit
+	WHERE 
+		object_id = :id
+	AND	state_time >= :'start'
+	AND 	state_time <= :'end'
+	AND 	state_type = 1
+),
+
+after AS (
+	-- the "younger" of the current host/service state and the first recorded event
+	(SELECT state > crit_value AS down
+		,LEAST(state_time,:'end') AS state_time
+		,state
+		
+		 FROM (
+		(SELECT state_time
+			,state
+			,crit.value crit_value
+		FROM icinga_statehistory,crit
+		WHERE 
+			object_id = :id
+		AND	state_time > :'end'
+		AND     state_type = 1
+		ORDER BY state_time ASC
+		LIMIT 1)
+
+		UNION ALL
+
+		SELECT status_update_time
+			,current_state
+			,crit.value crit_value
+		FROM icinga_hoststatus,crit
+		WHERE host_object_id = :id
+		AND     state_type = 1
+
+		UNION ALL
+
+		SELECT status_update_time
+			,current_state
+			,crit.value crit_value
+		FROM icinga_servicestatus,crit
+		WHERE service_object_id = :id
+		AND   state_type = 1
+	) AS after_searched_period 
+	ORDER BY state_time ASC LIMIT 1)
+)
+, allevents AS (
+	TABLE before 
+	UNION ALL
+	TABLE all_hard_events
+	UNION ALL
+	TABLE after
+) --SELECT * FROM allevents; 
+, downtimes AS (
+	SELECT tsrange(
+			--GREATEST(actual_start_time, :'start')
+		      --, LEAST(actual_end_time, :'end')
+			actual_start_time
+		      , actual_end_time
+		) AS downtime
+	FROM icinga_downtimehistory
+        WHERE object_id = :id
+--          AND actual_start_time <= :'end'
+--          AND COALESCE(actual_end_time,:'start') >= :'start'
+
+	UNION ALL
+
+	SELECT tsrange(
+			--GREATEST(start_time, :'start')
+		      --, LEAST(end_time, :'end')
+			start_time
+		      , end_time
+		) AS downtime
+	FROM icinga_outofsla_periods
+        WHERE timeperiod_object_id = :sla_id
+
+) --SELECT * FROM allevents;
+, enriched AS (
+	SELECT down
+	,tsrange(state_time, COALESCE(lead(state_time) OVER w, :'end'),'(]') AS timeframe
+		--,lead(state_time) OVER w - state_time AS dauer
+	FROM (
+		SELECT state > crit.value AS down
+		       , lead(state,1,state) OVER w > crit.value AS next_down
+		       , lag(state,1,state) OVER w > crit.value AS prev_down
+		       , state_time
+		       , state
+		FROM allevents,crit
+		WINDOW w AS (ORDER BY state_time)
+	) alle
+	--WHERE down != next_down OR down != prev_down
+	WINDOW w AS (ORDER BY state_time)
+) 
+, relevant AS (
+    SELECT down 
+    	,timeframe * tsrange(:'start',:'end','(]') AS timeframe
+	FROM enriched 
+    WHERE timeframe && tsrange(:'start',:'end','(]')
+) SELECT * FROM relevant;
+
+, relevant_down AS (
+	SELECT timeframe 
+		,down
+		,timeframe * downtime AS covered 
+		,COALESCE(
+			timeframe - downtime
+		       ,timeframe
+		) AS not_covered
+	FROM relevant
+	LEFT JOIN downtimes 
+	  ON timeframe && downtime
+	WHERE down
+) -- SELECT * FROM relevant_down;
+
+, effective_downtimes AS (
+	SELECT not_covered
+		, upper(not_covered) - lower(not_covered) AS dauer
+	FROM relevant_down
+) --SELECT * FROM effective_downtimes;
+
+, final_result AS (
+	SELECT sum(dauer) AS total_downtime
+		, timestamp :'end' - timestamp :'start' AS considered
+		, COALESCE(extract ('epoch' from sum(dauer)),0) AS down_secs
+		, extract ('epoch' from timestamp :'end' - timestamp  :'start' ) AS considered_secs
+	FROM effective_downtimes
+) --SELECT * FROM final_result;
+
+SELECT :'start' AS starttime, :'end' AS endtime,*
+, 100.0 - down_secs / considered_secs * 100.0 AS availability
+FROM final_result
+;
diff --git a/schema/postgresql/t/testme.sh b/schema/postgresql/t/testme.sh
new file mode 100755
index 0000000..ce61abc
--- /dev/null
+++ b/schema/postgresql/t/testme.sh
@@ -0,0 +1,2 @@
+#!/bin/sh
+pg_virtualenv -s pg_prove $(dirname $0)/0*.sql