· 6 years ago · Mar 27, 2019, 02:20 PM
1CREATE DEFINER=`bmonitor`@`%` PROCEDURE `proc_bmonitor_report_qxi_generate`(IN begin_date DATE, IN end_date DATE)
2
3
4
5
6
7BEGIN
8 -- -------------------
9 -- Declare statements
10 -- -------------------
11
12 DECLARE row_avg_value DOUBLE(16, 4);
13 DECLARE row_group_id BIGINT;
14 DECLARE row_plan_id BIGINT;
15 DECLARE row_item_id INT(11) UNSIGNED;
16 DECLARE row_report_day DATE;
17 DECLARE done INT DEFAULT FALSE;
18 DECLARE cursor_items CURSOR FOR
19 SELECT
20 DISTINCT
21 bsubmon.monitor_id
22 FROM bm_qxi_report_sub_category_monitors bsubmon
23 INNER JOIN bm_qxi_report_sub_categories bsubcat ON bsubcat.id = bsubmon.sub_category_id AND bsubcat.active = 1
24 INNER JOIN bm_qxi_report_categories bcat ON bcat.id = bsubcat.category_id AND bcat.active = 1
25 INNER JOIN bm_v_item_with_enable_profile bitemprofile ON bitemprofile.id_item = bsubmon.monitor_id
26 INNER JOIN bm_threshold bthres ON bthres.id_item = bsubmon.monitor_id
27 ;
28 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
29
30 -- -------------------
31 -- Initialize variables
32 -- -------------------
33
34 SET @original_time_zone = (SELECT @@session.time_zone);
35 SET @my_current_date := begin_date;
36
37 -- -------------------
38 -- Create temp tables
39 -- -------------------
40
41 SET sql_notes = 0 ;
42
43 DROP TEMPORARY TABLE IF EXISTS tmp_bm_host_monitor_values;
44
45 SET sql_notes = 1 ;
46
47 CREATE TEMPORARY TABLE tmp_bm_host_monitor_values (
48 host_id INT(10) UNSIGNED NOT NULL,
49 monitor_id INT(11) UNSIGNED NOT NULL,
50 monitor_type VARCHAR(50) NOT NULL,
51 plan_id INT(11) UNSIGNED NOT NULL,
52 plan_name VARCHAR(50) NOT NULL,
53 qty_ok INT UNSIGNED NOT NULL DEFAULT 0,
54 qty_warning INT UNSIGNED NOT NULL DEFAULT 0,
55 qty_critical INT UNSIGNED NOT NULL DEFAULT 0,
56 avg_value DECIMAL(16,4) NOT NULL DEFAULT 0,
57 report_day DATE NOT NULL,
58
59 PRIMARY KEY (host_id, monitor_id, monitor_type, report_day)
60 );
61
62 -- While all these data is in the bm_host table, this temporary table is created to avoid blocking of the main table during the procedure execution
63 SET sql_notes = 0 ;
64
65 DROP TEMPORARY TABLE IF EXISTS tmp_bm_host;
66
67 SET sql_notes = 1 ;
68
69 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_bm_host (
70 id INT(11) UNSIGNED NOT NULL,
71 group_id INT(10) UNSIGNED NOT NULL,
72 profile_id INT(11) UNSIGNED NOT NULL,
73 plan_id INT(11) UNSIGNED NOT NULL,
74 PRIMARY KEY (id),
75 INDEX idx_bm_tmp_hosts_01 (plan_id ASC));
76
77 -- -------------------
78 -- Initialize statements
79 -- -------------------
80
81 INSERT INTO tmp_bm_host
82 SELECT bhost.id_host AS id,
83 bhost.groupid AS group_id,
84 bhost.id_profile AS profile_id,
85 bhost.id_plan AS plan_id
86 FROM bm_host bhost;
87
88 SET @update_tmp_items_stmt_sql = "INSERT INTO tmp_bm_host_monitor_values (
89 host_id,
90 monitor_id,
91 monitor_type,
92 plan_id,
93 plan_name,
94 qty_ok,
95 qty_warning,
96 qty_critical,
97 avg_value,
98 report_day
99 )
100 SELECT
101 bhost.id,
102 bhist.id_item,
103 SUBSTRING_INDEX(bitems.name, '_', -1),
104 bplan.id_plan,
105 bplan.name,
106 SUM(
107 CASE WHEN bplan.warning_value >= bplan.critical_value = 1 THEN (
108 CASE WHEN bhist.value > bplan.warning_value
109 THEN 1
110 ELSE 0
111 END
112 ) ELSE (
113 CASE WHEN bhist.value < bplan.warning_value
114 THEN 1
115 ELSE 0
116 END
117 ) END
118 ),
119 SUM(
120 CASE WHEN bplan.warning_value >= bplan.critical_value = 1 THEN (
121 CASE WHEN bhist.value > bplan.critical_value AND bhist.value <= bplan.warning_value
122 THEN 1
123 ELSE 0
124 END
125 ) ELSE (
126 CASE WHEN bhist.value >= bplan.warning_value AND bhist.value < bplan.critical_value
127 THEN 1
128 ELSE 0
129 END
130 ) END
131 ),
132 SUM(
133 CASE WHEN bplan.warning_value >= bplan.critical_value = 1 THEN (
134 CASE WHEN bhist.value <= bplan.critical_value
135 THEN 1
136 ELSE 0
137 END
138 ) ELSE (
139 CASE WHEN bhist.value >= bplan.critical_value
140 THEN 1
141 ELSE 0
142 END
143 ) END
144 ),
145 AVG(bhist.value),
146 ? AS report_day
147 FROM tmp_bm_host bhost
148 INNER JOIN bm_host_group_details bgroupdetail ON bgroupdetail.group_id = bhost.group_id
149 INNER JOIN bm_history bhist ON bhist.id_host = bhost.id AND
150 bhist.id_item = ? AND
151 bhist.clock BETWEEN UNIX_TIMESTAMP(CONVERT_TZ(CONCAT(?,' 00:00:00'), bgroupdetail.timezone, 'UTC')) AND
152 UNIX_TIMESTAMP(CONVERT_TZ(CONCAT(?,' 23:59:59'), bgroupdetail.timezone, 'UTC'))
153 INNER JOIN bm_items bitems ON bitems.id_item = bhist.id_item
154 INNER JOIN (
155 SELECT
156 bplan2.id_plan AS id_plan,
157 bplan2.planname AS name,
158 (CASE
159 WHEN bthreshold.nominal = -1 THEN (bplan2.nacd * 1024)
160 WHEN bthreshold.nominal = -2 THEN (bplan2.nacu * 1024)
161 ELSE bthreshold.nominal
162 END) AS nominal_value,
163 (((CASE
164 WHEN bthreshold.nominal = -1 THEN (bplan2.nacd * 1024)
165 WHEN bthreshold.nominal = -2 THEN (bplan2.nacu * 1024)
166 ELSE bthreshold.nominal
167 END) * bthreshold.warning) / 100) AS warning_value,
168 (((CASE
169 WHEN bthreshold.nominal = -1 THEN (bplan2.nacd * 1024)
170 WHEN bthreshold.nominal = -2 THEN (bplan2.nacu * 1024)
171 ELSE bthreshold.nominal
172 END) * bthreshold.critical) / 100) AS critical_value
173 FROM bm_plan bplan2
174 INNER JOIN bm_threshold bthreshold ON ? = bthreshold.id_item
175 ) bplan ON bplan.id_plan = bhost.plan_id
176 WHERE (SUBSTRING_INDEX(bitems.name, '_', -1) = 'LOSSPER' OR bhist.valid = 1)
177 GROUP BY report_day, bhist.id_host, bhist.id_item";
178
179 SET @update_report_data_by_group_stmt_sql = "INSERT INTO bm_qxi_report (
180 execution_id,
181 group_id,
182 group_name,
183 tag_id,
184 tag_name,
185 sub_category_id,
186 sub_category_name,
187 plan_id,
188 plan_name,
189 monitor_type,
190 qty_critical,
191 qty_warning,
192 qty_ok,
193 avg_value,
194 report_day
195 )
196 SELECT *
197 FROM (
198 SELECT
199 ?,
200 btmphost.group_id,
201 bgroup.name AS group_name,
202 0 AS tag_id,
203 '' AS tag_name,
204 bsubcat.id AS sub_category_id,
205 bsubcat.name AS sub_category_name,
206 btmpdata.plan_id,
207 btmpdata.plan_name,
208 btmpdata.monitor_type,
209 SUM(btmpdata.qty_critical) AS sum_qty_critical,
210 SUM(btmpdata.qty_warning) AS sum_qty_warning,
211 SUM(btmpdata.qty_ok) AS sum_qty_ok,
212 AVG(btmpdata.avg_value) AS avg_avg_value,
213 btmpdata.report_day
214 FROM tmp_bm_host_monitor_values btmpdata
215 INNER JOIN tmp_bm_host btmphost ON btmphost.id = btmpdata.host_id
216 INNER JOIN bm_host_groups bgroup ON bgroup.groupid = btmphost.group_id
217 INNER JOIN bm_qxi_report_sub_category_monitors bmoni ON bmoni.monitor_id = btmpdata.monitor_id
218 INNER JOIN bm_qxi_report_sub_categories bsubcat ON bsubcat.id = bmoni.sub_category_id AND bsubcat.active = 1
219 INNER JOIN bm_qxi_report_categories bcat ON bcat.id = bsubcat.category_id AND bcat.active = 1
220 GROUP BY btmpdata.report_day, btmphost.group_id, bsubcat.id, btmpdata.plan_id, btmpdata.monitor_type
221 ) tmp
222 ON DUPLICATE KEY UPDATE
223 qty_critical = tmp.sum_qty_critical,
224 qty_warning = tmp.sum_qty_warning,
225 qty_ok = tmp.sum_qty_ok,
226 avg_value = tmp.avg_avg_value,
227 group_name = tmp.group_name,
228 plan_name = tmp.plan_name,
229 monitor_type = tmp.monitor_type,
230 execution_id = ?";
231
232 SET @update_report_data_by_tag_stmt_sql = "INSERT INTO bm_qxi_report (
233 execution_id,
234 group_id,
235 group_name,
236 tag_id,
237 tag_name,
238 sub_category_id,
239 sub_category_name,
240 plan_id,
241 plan_name,
242 monitor_type,
243 qty_critical,
244 qty_warning,
245 qty_ok,
246 avg_value,
247 report_day
248 )
249 SELECT *
250 FROM (
251 SELECT
252 ?,
253 btmphost.group_id,
254 bgroup.name AS group_name,
255 btag.id AS tag_id,
256 btag.tag AS tag_name,
257 bsubcat.id AS sub_category_id,
258 bsubcat.name AS sub_category_name,
259 btmpdata.plan_id,
260 btmpdata.plan_name,
261 btmpdata.monitor_type,
262 SUM(btmpdata.qty_critical) AS sum_qty_critical,
263 SUM(btmpdata.qty_warning) AS sum_qty_warning,
264 SUM(btmpdata.qty_ok) AS sum_qty_ok,
265 AVG(btmpdata.avg_value) AS avg_avg_value,
266 btmpdata.report_day
267 FROM tmp_bm_host_monitor_values btmpdata
268 INNER JOIN tmp_bm_host btmphost ON btmphost.id = btmpdata.host_id
269 INNER JOIN bm_host_groups bgroup ON bgroup.groupid = btmphost.group_id
270 INNER JOIN bm_tag_hosts btaghost ON btaghost.host_id = btmpdata.host_id
271 INNER JOIN bm_host_tags btag ON btag.id = btaghost.tag_id
272 INNER JOIN bm_qxi_report_sub_category_monitors bmoni ON bmoni.monitor_id = btmpdata.monitor_id
273 INNER JOIN bm_qxi_report_sub_categories bsubcat ON bsubcat.id = bmoni.sub_category_id AND bsubcat.active = 1
274 INNER JOIN bm_qxi_report_categories bcat ON bcat.id = bsubcat.category_id AND bcat.active = 1
275 GROUP BY btmpdata.report_day, btmphost.group_id, btag.id, bsubcat.id, btmpdata.plan_id, btmpdata.monitor_type
276 ) tmp
277 ON DUPLICATE KEY UPDATE
278 qty_critical = tmp.sum_qty_critical,
279 qty_warning = tmp.sum_qty_warning,
280 qty_ok = tmp.sum_qty_ok,
281 avg_value = tmp.avg_avg_value,
282 group_name = tmp.group_name,
283 plan_name = tmp.plan_name,
284 tag_name = tmp.tag_name,
285 monitor_type = tmp.monitor_type,
286 execution_id = ?";
287
288 SET @update_report_data_by_tags_monitors_stmt_sql = "INSERT INTO bm_qxi_report_tags_monitors (
289 execution_id,
290 tag_id,
291 monitor_id,
292 sub_category_id,
293 category_id,
294 nominal_count,
295 report_day
296 )
297 SELECT DISTINCT *
298 FROM (
299 SELECT
300 ?,
301 btag.id AS tag_id,
302 btmpdata.monitor_id,
303 bsubcat.id as subcategory_id,
304 bcat.id as category_id,
305 th.nominalCnt as nominal_count,
306 btmpdata.report_day
307 FROM tmp_bm_host_monitor_values btmpdata
308 INNER JOIN tmp_bm_host btmphost ON btmphost.id = btmpdata.host_id
309 INNER JOIN bm_tag_hosts btaghost ON btaghost.host_id = btmpdata.host_id
310 INNER JOIN bm_host_tags btag ON btag.id = btaghost.tag_id
311 INNER JOIN bm_threshold th ON th.id_item = btmpdata.monitor_id
312 INNER JOIN bm_qxi_report_sub_category_monitors bmoni ON bmoni.monitor_id = btmpdata.monitor_id
313 INNER JOIN bm_qxi_report_sub_categories bsubcat ON bsubcat.id = bmoni.sub_category_id AND bsubcat.active = 1
314 INNER JOIN bm_qxi_report_categories bcat ON bcat.id = bsubcat.category_id AND bcat.active = 1
315 ) tmp
316 ON DUPLICATE KEY UPDATE
317 tag_id = tmp.tag_id,
318 monitor_id = tmp.monitor_id,
319 category_id = tmp.subcategory_id,
320 sub_category_id = tmp.category_id,
321 report_day = tmp.report_day,
322 execution_id = ?";
323
324 SET @update_report_data_by_groups_monitors_stmt_sql = "INSERT INTO bm_qxi_report_groups_monitors (
325 execution_id,
326 group_id,
327 category_id,
328 sub_category_id,
329 plan_id,
330 monitor_id,
331 nominal_count,
332 report_day
333 )
334 SELECT *
335 FROM (
336 SELECT
337 ?,
338 btmphost.group_id,
339 bcat.id AS category_id,
340 bsubcat.id AS sub_category_id,
341 btmpdata.plan_id,
342 bmoni.monitor_id,
343 th.nominalCnt as nominal_count,
344 btmpdata.report_day
345 FROM tmp_bm_host_monitor_values btmpdata
346 INNER JOIN tmp_bm_host btmphost ON btmphost.id = btmpdata.host_id
347 INNER JOIN bm_host_groups bgroup ON bgroup.groupid = btmphost.group_id
348 INNER JOIN bm_threshold th ON th.id_item = btmpdata.monitor_id
349 INNER JOIN bm_qxi_report_sub_category_monitors bmoni ON bmoni.monitor_id = btmpdata.monitor_id
350 INNER JOIN bm_qxi_report_sub_categories bsubcat ON bsubcat.id = bmoni.sub_category_id AND bsubcat.active = 1
351 INNER JOIN bm_qxi_report_categories bcat ON bcat.id = bsubcat.category_id AND bcat.active = 1
352 GROUP BY btmpdata.report_day, btmphost.group_id, bsubcat.id, btmpdata.plan_id, btmpdata.monitor_type
353 ) tmp
354 ON DUPLICATE KEY UPDATE
355 group_id = tmp.group_id,
356 category_id = tmp.category_id,
357 sub_category_id = tmp.sub_category_id,
358 plan_id = tmp.plan_id,
359 monitor_id = tmp.monitor_id,
360 report_day = tmp.report_day,
361 execution_id = ?";
362
363 PREPARE update_tmp_items_stmt_sql FROM @update_tmp_items_stmt_sql;
364 PREPARE update_report_data_by_group_stmt_sql FROM @update_report_data_by_group_stmt_sql;
365 PREPARE update_report_data_by_tag_stmt_sql FROM @update_report_data_by_tag_stmt_sql;
366 PREPARE update_report_data_by_tags_monitors_stmt_sql FROM @update_report_data_by_tags_monitors_stmt_sql;
367 PREPARE update_report_data_by_groups_monitors_stmt_sql FROM @update_report_data_by_groups_monitors_stmt_sql;
368
369 -- --------------
370 -- Start data generation procedure
371 -- --------------
372
373 -- Configurations
374
375 SET time_zone = 'UTC';
376
377 -- Initialize execution row
378
379 INSERT INTO bm_qxi_report_data_generation_execs (
380 started_at
381 ) VALUES (
382 NOW()
383 );
384
385 COMMIT;
386
387 SET @execution_id = LAST_INSERT_ID();
388
389 SET @my_current_date := begin_date;
390
391 -- Loop items, and then loop for each day. Then, insert data into temp table
392
393 OPEN cursor_items;
394
395 read_items_loop : LOOP
396
397 FETCH cursor_items INTO row_item_id;
398
399 IF done THEN
400 LEAVE read_items_loop;
401 END IF;
402
403 SET @item_id = row_item_id;
404
405 date_loop : LOOP
406
407 IF @my_current_date > end_date THEN
408 LEAVE date_loop;
409 END IF;
410
411 -- Populate temporary table
412
413 EXECUTE update_tmp_items_stmt_sql USING @my_current_date, @item_id, @my_current_date, @my_current_date, @item_id;
414
415 COMMIT;
416
417 SET @my_current_date = DATE_ADD(@my_current_date, INTERVAL 1 DAY);
418
419 END LOOP date_loop;
420
421 SET @my_current_date := begin_date;
422
423 END LOOP read_items_loop;
424
425 -- Populate report data table
426
427 EXECUTE update_report_data_by_group_stmt_sql USING @execution_id, @execution_id;
428 EXECUTE update_report_data_by_tag_stmt_sql USING @execution_id, @execution_id;
429 EXECUTE update_report_data_by_tags_monitors_stmt_sql USING @execution_id, @execution_id;
430 EXECUTE update_report_data_by_groups_monitors_stmt_sql USING @execution_id, @execution_id;
431
432 COMMIT;
433
434 -- Remove report data on the date range received that was NOT updated by this execution
435
436 DELETE FROM bm_qxi_report
437 WHERE report_day BETWEEN begin_date AND end_date AND
438 execution_id != @execution_id;
439
440 COMMIT;
441
442 DELETE FROM bm_qxi_report_tags_monitors
443 WHERE report_day BETWEEN begin_date AND end_date AND
444 execution_id != @execution_id;
445
446 COMMIT;
447
448 DELETE FROM bm_qxi_report_groups_monitors
449 WHERE report_day BETWEEN begin_date AND end_date AND
450 execution_id != @execution_id;
451
452 COMMIT;
453
454 -- --------------
455 -- Free resources
456 -- --------------
457
458 CLOSE cursor_items;
459
460 DEALLOCATE PREPARE update_tmp_items_stmt_sql;
461 DEALLOCATE PREPARE update_report_data_by_group_stmt_sql;
462 DEALLOCATE PREPARE update_report_data_by_tag_stmt_sql;
463 DEALLOCATE PREPARE update_report_data_by_tags_monitors_stmt_sql;
464 DEALLOCATE PREPARE update_report_data_by_groups_monitors_stmt_sql;
465
466 SET sql_notes = 0 ;
467
468 DROP TEMPORARY TABLE IF EXISTS tmp_bm_host_monitor_values;
469
470 SET sql_notes = 1 ;
471
472 -- Restore session timezone
473
474 SET time_zone = @original_time_zone;
475
476 -- Close execution row
477
478 UPDATE bm_qxi_report_data_generation_execs
479 SET ended_at = NOW()
480 WHERE id = @execution_id;
481
482 COMMIT;
483
484END