1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
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
;
|
