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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
DROP FUNCTION IF EXISTS idoreports_get_sla_ok_percent(BIGINT, TIMESTAMPTZ, TIMESTAMPTZ, INT);
CREATE OR REPLACE FUNCTION idoreports_get_sla_ok_percent(
id BIGINT,
starttime TIMESTAMP WITHOUT TIME ZONE,
endtime TIMESTAMP WITHOUT TIME ZONE,
sla_id INTEGER DEFAULT NULL
) RETURNS float
LANGUAGE SQL
AS
$$
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, starttime) AS state_time_,
state
FROM
icinga_statehistory,
crit
WHERE
object_id = id
AND state_time < starttime
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, starttime) AS state_time_,
state
FROM
icinga_statehistory,
crit
WHERE
object_id = id
AND state_time < starttime
ORDER BY
state_time DESC
LIMIT 1
)
) ranked
ORDER BY
prio
LIMIT 1
),
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 >= starttime
AND state_time <= endtime
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, endtime) AS state_time,
state
FROM
(
(
SELECT
state_time,
state,
crit.value AS crit_value
FROM
icinga_statehistory,
crit
WHERE
object_id = id
AND state_time > endtime
AND state_type = 1
ORDER BY
state_time
LIMIT 1
)
UNION ALL
(
SELECT
status_update_time,
current_state,
crit.value AS 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 AS crit_value
FROM
icinga_servicestatus,
crit
WHERE
service_object_id = id
AND state_type = 1
)
) AS after_searched_period
ORDER BY
state_time
LIMIT 1
)
),
allevents AS (
TABLE before
UNION ALL
TABLE all_hard_events
UNION ALL
TABLE after
),
downtimes AS (
(
SELECT
tsrange(actual_start_time, actual_end_time) AS downtime
FROM
icinga_downtimehistory
WHERE
object_id = id
)
UNION ALL
(
SELECT
tsrange(start_time, end_time) AS downtime
FROM
icinga_outofsla_periods
WHERE
timeperiod_object_id = sla_id
)
),
enriched AS (
SELECT
down,
tsrange(state_time, COALESCE(lead(state_time) OVER w, endtime), '(]') 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 WINDOW w AS (ORDER BY state_time)
),
relevant AS (
SELECT
down,
timeframe * tsrange(starttime, endtime, '(]') AS timeframe
FROM
enriched
WHERE
timeframe && tsrange(starttime, endtime, '(]')
),
covered AS (
SELECT
upper(covered_by_downtime) - lower(covered_by_downtime) AS dauer
FROM (
SELECT
timeframe * downtime AS covered_by_downtime
FROM
relevant
LEFT JOIN downtimes ON timeframe && downtime
WHERE
down
) AS foo
),
relevant_down AS (
SELECT *,
upper(timeframe) - lower(timeframe) AS dauer
FROM
relevant
WHERE
down
),
final_result AS (
SELECT
sum(dauer) - (
SELECT sum(dauer) FROM covered
) AS total_downtime,
endtime - starttime AS considered,
COALESCE(extract('epoch' from sum(dauer)), 0) AS down_secs,
extract('epoch' from endtime - starttime) AS considered_secs
FROM
relevant_down
)
SELECT
100.0 - down_secs / considered_secs * 100.0 AS availability
FROM
final_result ;
$$;
|
