Hi again, the clients are running current hardware. Running linux and chrome. There should be no issues with client side, also the network should not be a problem.
The database itself seems to be to source of the problem. I've tried to tune it a bit, but it still seems too slow. The server itself has 30+ gb of physical memory.
Part of mysqldumpslow output attached. The top queries seem to be super slow compared to the bottom ones.
mysqlreport log also attached. Any help on this would be appreciated. Regards, Vaclav On 08/20/2018 05:48 PM, Tobias Koeck wrote:
Is the Database fast enough? use e.g. mysql_tuner for mysql to find out if it can be tweaked? Does the server has enough memory? is your client computer and your browser fast enough? Use general performance advices for it. On Mon, Aug 20, 2018 at 2:32 PM Václav Mach <ma...@cesnet.cz> wrote:Hi, are there some options how to speed up icingaweb2? Certain parts are slow and take several seconds to render nevertheless the server itself has no dramatic load. regards, Vaclav -- Václav Mach tel: +420 234 680 206 CESNET, z.s.p.o. www.cesnet.cz _______________________________________________ icinga-users mailing list icinga-users@lists.icinga.org https://lists.icinga.org/mailman/listinfo/icinga-users_______________________________________________ icinga-users mailing list icinga-users@lists.icinga.org https://lists.icinga.org/mailman/listinfo/icinga-users
-- Václav Mach tel: +420 234 680 206 CESNET, z.s.p.o. www.cesnet.cz
MariaDB 10.1.26-MariaDB-0+d uptime 0+0:3:48 Fri Aug 31 12:49:13 2018
__ Key _________________________________________________________________
Buffer used 0 of 4.00G %Used: 0.00
Current 746.91M %Usage: 18.24
Write hit 0.00%
Read hit 0.00%
__ Questions ___________________________________________________________
Total 51.49k 225.8/s
DMS 50.42k 221.2/s %Total: 97.93
Com_ 842 3.7/s 1.64
-Unknown 510 2.2/s 0.99
QC Hits 486 2.1/s 0.94
COM_QUIT 247 1.1/s 0.48
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 50.42k 221.2/s 97.93
UPDATE 49.25k 216.0/s 95.65 97.67
SELECT 993 4.4/s 1.93 1.97
INSERT 156 0.7/s 0.30 0.31
DELETE 25 0.1/s 0.05 0.05
REPLACE 0 0/s 0.00 0.00
Com_ 842 3.7/s 1.64
set_option 313 1.4/s 0.61
begin 250 1.1/s 0.49
commit 249 1.1/s 0.48
__ Rows ________________________________________________________________
Rows 150.64M 660.7k/s
Using idx 142.88M 626.7k/s %Index: 94.85
Rows/question 2.93k
__ SELECT and Sort _____________________________________________________
Scan 390 1.7/s %SELECT: 39.27
Range 9 0.0/s 0.91
Full join 138 0.6/s 13.90
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 391 1.7/s
Sort range 0 0/s
Sort mrg pass 35 0.2/s
__ Query Cache _________________________________________________________
Memory usage 549.80k of 16.00M %Usage: 3.36
Block Fragmnt 4.31%
Hits 486 2.1/s
Inserts 298 1.3/s
Insrt:Prune 298:1 1.3/s
Hit:Insert 1.63:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 51.94k 227.8/s
__ Tables ______________________________________________________________
Open 145 of 2000 %Cache: 7.25
Opened 151 0.7/s
__ Connections _________________________________________________________
Max used 11 of 5000 %Max: 0.22
Total 249 1.1/s
__ Created Temp ________________________________________________________
Disk table 154 0.7/s %Disk: 29.56
Table 521 2.3/s Size: 16.0M
File 76 0.3/s
__ Threads _____________________________________________________________
Running 1 of 2
Created 11 0.0/s
Slow 0 0/s
Cached 9 of 64 %Hit: 95.58
__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 0 0/s
__ Bytes _______________________________________________________________
Sent 42.53M 186.5k/s
Received 7.50M 32.9k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 313.58M of 16.00G %Usage: 1.91
Read hit 100.00%
Pages
Free 1.03M %Total: 98.09
Data 17.89k 1.71 %Drty: 11.62
Misc 2175 0.21
Latched 0 0.00
Reads 313.52M 1.4M/s
From disk 9.01k 39.5/s %Disk: 0.00
Ahead Rnd 0 0/s
Writes 122.79k 538.5/s
Wait Free 0 0/s %Wait: 0.00
Flushes 2.37k 10.4/s
LRU 0 0/s %LRU: 0.00
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
Trx history 39
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 17.74k 77.8/s
Writes 2.85k 12.5/s
fsync 535 2.3/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 276 1.2/s
Read 17.62k 77.3/s
Written 2.37k 10.4/s
Rows
Deleted 92 0.4/s
Inserted 156 0.7/s
Read 103.65M 454.6k/s
Updated 49.25k 216.0/s
__ Aria Pagecache ______________________________________________________
Buffer used 9.33M of 128.00M %Used: 7.29
Current 5.30M %Usage: 4.14
Write hit 100.00%
Read hit 99.99%
Count: 12 Time=0.98s (11s) Lock=0.00s (0s) Rows_sent=25.0 (300), Rows_examined=575310.0 (6903720), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS host_state, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state, CASE WHEN (ss.scheduled_downtime_depth = N OR ss.scheduled_downtime_depth IS NULL) THEN N ELSE N END AS service_in_downtime, ss.problem_has_been_acknowledged AS service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS service_handled, ss.output AS service_output, ss.perfdata AS service_perfdata, ss.current_check_attempt || 'S' || ss.max_check_attempts AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS service_last_state_change, s.icon_image AS service_icon_image, s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS service_is_flapping, ss.state_type AS service_state_type, CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END AS service_severity, ss.notifications_enabled AS service_notifications_enabled, ss.active_checks_enabled AS service_active_checks_enabled, ss.passive_checks_enabled AS service_passive_checks_enabled, ss.check_command AS service_check_command FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id
LEFT JOIN icinga_servicegroup_members AS sgm ON sgm.service_object_id = so.object_id
LEFT JOIN icinga_servicegroups AS sg ON sg.servicegroup_id = sgm.servicegroup_id
LEFT JOIN icinga_objects AS sgo ON sgo.object_id = sg.servicegroup_object_id AND sgo.is_active = N AND sgo.objecttype_id = N WHERE (sgo.name1 = 'S') GROUP BY so.object_id,
s.service_id ORDER BY CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END DESC, ss.last_state_change DESC LIMIT N
Count: 6 Time=1.05s (6s) Lock=0.00s (0s) Rows_sent=1.0 (6), Rows_examined=575629.0 (3453774), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT COUNT(*) AS cnt FROM (SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS host_state, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state, CASE WHEN (ss.scheduled_downtime_depth = N OR ss.scheduled_downtime_depth IS NULL) THEN N ELSE N END AS service_in_downtime, ss.problem_has_been_acknowledged AS service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS service_handled, ss.output AS service_output, ss.perfdata AS service_perfdata, ss.current_check_attempt || 'S' || ss.max_check_attempts AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS service_last_state_change, s.icon_image AS service_icon_image, s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS service_is_flapping, ss.state_type AS service_state_type, CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END AS service_severity, ss.notifications_enabled AS service_notifications_enabled, ss.active_checks_enabled AS service_active_checks_enabled, ss.passive_checks_enabled AS service_passive_checks_enabled, ss.check_command AS service_check_command FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id
LEFT JOIN icinga_servicegroup_members AS sgm ON sgm.service_object_id = so.object_id
LEFT JOIN icinga_servicegroups AS sg ON sg.servicegroup_id = sgm.servicegroup_id
LEFT JOIN icinga_objects AS sgo ON sgo.object_id = sg.servicegroup_object_id AND sgo.is_active = N AND sgo.objecttype_id = N WHERE (sgo.name1 = 'S') GROUP BY so.object_id,
s.service_id) AS t
Count: 7 Time=0.36s (2s) Lock=0.00s (0s) Rows_sent=1.0 (7), Rows_examined=166764.0 (1167348), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT SUM(CASE WHEN state = N THEN N ELSE N END) AS services_critical, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_critical_handled, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_critical_unhandled, SUM(CASE WHEN state = N THEN N ELSE N END) AS services_ok, SUM(CASE WHEN state = N THEN N ELSE N END) AS services_pending, SUM(N) AS services_total, SUM(CASE WHEN state = N THEN N ELSE N END) AS services_unknown, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_unknown_handled, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_unknown_unhandled, SUM(CASE WHEN state = N THEN N ELSE N END) AS services_warning, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_warning_handled, SUM(CASE WHEN state = N AND handled = N THEN N ELSE N END) AS services_warning_unhandled FROM (SELECT CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS handled, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS state, UNIX_TIMESTAMP(ss.last_state_change) AS state_change FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id) AS servicestatussummary
Count: 7 Time=0.31s (2s) Lock=0.00s (0s) Rows_sent=1.0 (7), Rows_examined=166764.0 (1167348), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT COUNT(*) AS services_critical_unhandled FROM (SELECT so.name2 AS service_description FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END = N AND CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END = N)) AS problems
Count: 2 Time=1.00s (2s) Lock=0.00s (0s) Rows_sent=25.0 (50), Rows_examined=593422.5 (1186845), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS host_state, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state, CASE WHEN (ss.scheduled_downtime_depth = N OR ss.scheduled_downtime_depth IS NULL) THEN N ELSE N END AS service_in_downtime, ss.problem_has_been_acknowledged AS service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS service_handled, ss.output AS service_output, ss.perfdata AS service_perfdata, ss.current_check_attempt || 'S' || ss.max_check_attempts AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS service_last_state_change, s.icon_image AS service_icon_image, s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS service_is_flapping, ss.state_type AS service_state_type, CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END AS service_severity, ss.notifications_enabled AS service_notifications_enabled, ss.active_checks_enabled AS service_active_checks_enabled, ss.passive_checks_enabled AS service_passive_checks_enabled, ss.check_command AS service_check_command FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = 'S') ORDER BY CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END DESC, ss.last_state_change DESC LIMIT N
Count: 2 Time=0.81s (1s) Lock=0.00s (0s) Rows_sent=20.0 (40), Rows_examined=587196.0 (1174392), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = N) GROUP BY service_description,
service_display_name ORDER BY s.display_name COLLATE latin1_general_ci ASC LIMIT N
Count: 2 Time=0.72s (1s) Lock=0.00s (0s) Rows_sent=20.0 (40), Rows_examined=587042.0 (1174084), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = N) GROUP BY host_name,
host_display_name ORDER BY h.display_name COLLATE latin1_general_ci ASC LIMIT N
Count: 2 Time=0.47s (0s) Lock=0.00s (0s) Rows_sent=1.0 (2), Rows_examined=41919.0 (83838), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT SUM(CASE WHEN object_type = 'S' AND state = N THEN N ELSE N END) AS hosts_up, SUM(CASE WHEN object_type = 'S' AND state = N AND handled > N THEN N ELSE N END) AS hosts_down_handled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled = N THEN N ELSE N END) AS hosts_down_unhandled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled > N THEN N ELSE N END) AS hosts_unreachable_handled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled = N THEN N ELSE N END) AS hosts_unreachable_unhandled, SUM(CASE WHEN object_type = 'S' AND state = N THEN N ELSE N END) AS hosts_pending, SUM(CASE WHEN object_type = 'S' AND is_active_checked = N AND is_passive_checked = N THEN N ELSE N END) AS hosts_not_checked, SUM(CASE WHEN object_type = 'S' AND state = N THEN N ELSE N END) AS services_ok, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem > N THEN N ELSE N END) AS services_warning_handled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem = N THEN N ELSE N END) AS services_warning_unhandled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem > N THEN N ELSE N END) AS services_critical_handled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem = N THEN N ELSE N END) AS services_critical_unhandled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem > N THEN N ELSE N END) AS services_unknown_handled, SUM(CASE WHEN object_type = 'S' AND state = N AND handled + host_problem = N THEN N ELSE N END) AS services_unknown_unhandled, SUM(CASE WHEN object_type = 'S' AND state = N THEN N ELSE N END) AS services_pending, SUM(CASE WHEN object_type = 'S' AND is_active_checked = N AND is_passive_checked = N THEN N ELSE N END) AS services_not_checked FROM (SELECT CASE WHEN (hs.problem_has_been_acknowledged + hs.scheduled_downtime_depth) > N THEN N ELSE N END AS handled, CASE WHEN COALESCE(hs.current_state, N) = N THEN N ELSE N END AS host_problem, NULL AS host_state, hs.active_checks_enabled AS is_active_checked, hs.flap_detection_enabled AS is_allowed_to_flap, hs.is_flapping, CASE WHEN hs.active_checks_enabled = N AND hs.passive_checks_enabled = N THEN N ELSE N END AS is_passive_checked, hs.event_handler_enabled AS is_processing_events, hs.notifications_enabled AS is_triggering_notifications, ('S') AS object_type, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE CASE WHEN hs.current_state = N THEN N ELSE CASE WHEN hs.current_state = N THEN N WHEN hs.current_state = N THEN N ELSE N END + CASE WHEN hs.problem_has_been_acknowledged = N THEN N WHEN hs.scheduled_downtime_depth > N THEN N ELSE N END END END AS severity, UNIX_TIMESTAMP(hs.last_state_change) AS state_change, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS state FROM icinga_objects AS ho
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id AND ho.is_active = N AND ho.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id UNION ALL SELECT CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS handled, CASE WHEN COALESCE(hs.current_state, N) = N THEN N ELSE N END AS host_problem, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE CASE WHEN hs.state_type = N THEN hs.current_state ELSE hs.last_hard_state END END AS host_state, ss.active_checks_enabled AS is_active_checked, ss.flap_detection_enabled AS is_allowed_to_flap, ss.is_flapping, CASE WHEN ss.active_checks_enabled = N AND ss.passive_checks_enabled = N THEN N ELSE N END AS is_passive_checked, ss.event_handler_enabled AS is_processing_events, ss.notifications_enabled AS is_triggering_notifications, ('S') AS object_type, CASE WHEN ss.current_state = N THEN CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE N END + CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END ELSE CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N WHEN ss.current_state = N THEN N ELSE N END + CASE WHEN hs.current_state > N THEN N ELSE CASE WHEN ss.problem_has_been_acknowledged = N THEN N ELSE CASE WHEN ss.scheduled_downtime_depth > N THEN N ELSE N END END END END AS severity, UNIX_TIMESTAMP(ss.last_state_change) AS state_change, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS state FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id) AS statussummary
Count: 1 Time=0.93s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=587934.0 (587934), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT COUNT(*) AS cnt FROM (SELECT so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = N) GROUP BY service_description,
service_display_name) AS t
Count: 1 Time=0.69s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=587472.0 (587472), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT COUNT(*) AS cnt FROM (SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = N) GROUP BY host_name,
host_display_name) AS t
Count: 1 Time=0.31s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=208455.0 (208455), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT COUNT(*) AS cnt FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (CASE WHEN COALESCE(ss.current_state, N) = N THEN N ELSE N END = 'S')
Count: 16 Time=0.02s (0s) Lock=0.00s (0s) Rows_sent=1.0 (16), Rows_examined=192.5 (3080), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (so.name1 COLLATE latin1_general_ci = 'S' AND so.name2 COLLATE latin1_general_ci = 'S') ORDER BY s.display_name COLLATE latin1_general_ci ASC
Count: 8 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (8), Rows_examined=8.0 (64), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT i.instance_name, hs.current_check_attempt || 'S' || hs.max_check_attempts AS host_attempt, h.icon_image AS host_icon_image, h.icon_image_alt AS host_icon_image_alt, hs.problem_has_been_acknowledged AS host_acknowledged, hs.active_checks_enabled AS host_active_checks_enabled, h.address AS host_address, h.address6 AS host_address6, h.alias COLLATE latin1_general_ci AS host_alias, h.display_name COLLATE latin1_general_ci AS host_display_name, CASE WHEN (hs.problem_has_been_acknowledged + hs.scheduled_downtime_depth) > N THEN N ELSE N END AS host_handled, CASE WHEN (hs.scheduled_downtime_depth = N) THEN N ELSE N END AS host_in_downtime, hs.is_flapping AS host_is_flapping, UNIX_TIMESTAMP(hs.last_state_change) AS host_last_state_change, so.name1 AS host_name, hs.notifications_enabled AS host_notifications_enabled, hs.passive_checks_enabled AS host_passive_checks_enabled, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS host_state, hs.state_type AS host_state_type, s.icon_image AS service_icon_image, s.icon_image_alt AS service_icon_image_alt, ss.problem_has_been_acknowledged AS service_acknowledged, ss.acknowledgement_type AS service_acknowledgement_type, s.action_url AS service_action_url, ss.active_checks_enabled AS service_active_checks_enabled, CASE WHEN ss.active_checks_enabled=s.active_checks_enabled THEN N ELSE N END AS service_active_checks_enabled_changed, ss.current_check_attempt || 'S' || ss.max_check_attempts AS service_attempt, ss.check_command AS service_check_command, ss.execution_time AS service_check_execution_time, (s.check_interval * N) AS service_check_interval, ss.latency AS service_check_latency, ss.check_source AS service_check_source, ctp.alias COLLATE latin1_general_ci AS service_check_timeperiod, ss.current_notification_number AS service_current_notification_number, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, ss.event_handler_enabled AS service_event_handler_enabled, CASE WHEN ss.event_handler_enabled=s.event_handler_enabled THEN N ELSE N END AS service_event_handler_enabled_changed, ss.flap_detection_enabled AS service_flap_detection_enabled, CASE WHEN ss.flap_detection_enabled=s.flap_detection_enabled THEN N ELSE N END AS service_flap_detection_enabled_changed, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS service_handled, CASE WHEN (ss.scheduled_downtime_depth = N OR ss.scheduled_downtime_depth IS NULL) THEN N ELSE N END AS service_in_downtime, ss.is_flapping AS service_is_flapping, ss.is_reachable AS service_is_reachable, UNIX_TIMESTAMP(ss.last_check) AS service_last_check, UNIX_TIMESTAMP(ss.last_notification) AS service_last_notification, UNIX_TIMESTAMP(ss.last_state_change) AS service_last_state_change, ss.long_output AS service_long_output, UNIX_TIMESTAMP(ss.next_check) AS service_next_check, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN NULL ELSE UNIX_TIMESTAMP(ss.last_check) + CASE WHEN COALESCE(ss.current_state, N) > N AND ss.state_type = N THEN ss.retry_check_interval ELSE ss.normal_check_interval END * N * N + CEIL(ss.execution_time) END AS service_next_update, s.notes AS service_notes, s.notes_url AS service_notes_url, ss.notifications_enabled AS service_notifications_enabled, CASE WHEN ss.notifications_enabled=s.notifications_enabled THEN N ELSE N END AS service_notifications_enabled_changed, ss.obsess_over_service AS service_obsessing, CASE WHEN ss.obsess_over_service=s.obsess_over_service THEN N ELSE N END AS service_obsessing_changed, ss.output AS service_output, ss.passive_checks_enabled AS service_passive_checks_enabled, CASE WHEN ss.passive_checks_enabled=s.passive_checks_enabled THEN N ELSE N END AS service_passive_checks_enabled_changed, ss.percent_state_change AS service_percent_state_change, ss.perfdata AS service_perfdata, ss.process_performance_data AS service_process_perfdata, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_instances AS i ON i.instance_id = so.instance_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id
LEFT JOIN icinga_timeperiods AS ctp ON ctp.timeperiod_object_id = s.check_timeperiod_object_id WHERE (so.name1 = 'S' AND so.name2 = 'S') ORDER BY s.display_name COLLATE latin1_general_ci ASC
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows_sent=380.0 (380), Rows_examined=1919.0 (1919), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT h.display_name COLLATE latin1_general_ci AS host_display_name, so.name1 AS host_name, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END AS service_handled, ss.output AS service_output, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (so.name2 IN ('S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S') AND so.name1 IN ('S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S'))
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=1373.0 (1373), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT so.name1 AS host_name, SUM( CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N THEN N ELSE N END ) AS unhandled_service_count FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = N
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id AND ss.current_state > N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN (SELECT ho.object_id FROM icinga_objects AS ho
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id AND ho.is_active = N AND ho.objecttype_id = N
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id WHERE (CASE WHEN COALESCE(hs.current_state, N) = N THEN N ELSE N END = 'S') LIMIT N) AS h ON h.object_id = s.host_object_id GROUP BY so.name1 HAVING (SUM(
CASE
WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, N)) > N
THEN N
ELSE N
END
) > N)
Count: 8 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), icinga2[icinga2]@localhost
SELECT c.comment_author_name AS author, c.comment_data AS comment, c.comment_expiration AS expiration, c.comment_internal_id AS id, c.comment_name AS name, c.comment_is_persistent AS persistent, c.comment_timestamp AS timestamp, c.comment_type AS type FROM (SELECT c.author_name COLLATE latin1_general_ci AS comment_author, c.author_name AS comment_author_name, c.comment_data, CASE c.expires WHEN N THEN UNIX_TIMESTAMP(c.expiration_time) ELSE NULL END AS comment_expiration, c.internal_comment_id AS comment_internal_id, c.is_persistent AS comment_is_persistent, c.name AS comment_name, UNIX_TIMESTAMP(c.comment_time) AS comment_timestamp, CASE c.entry_type WHEN N THEN 'S' WHEN N THEN 'S' WHEN N THEN 'S' WHEN N THEN 'S' END AS comment_type, i.instance_name, ('S') AS object_type, h.display_name COLLATE latin1_general_ci AS host_display_name, so.name1 AS host_name, CASE WHEN hs.has_been_checked = N OR hs.has_been_checked IS NULL THEN N ELSE hs.current_state END AS host_state, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, so.name1 AS service_host_name, CASE WHEN ss.has_been_checked = N OR ss.has_been_checked IS NULL THEN N ELSE ss.current_state END AS service_state FROM icinga_comments AS c
INNER JOIN icinga_objects AS so ON so.object_id = c.object_id AND so.is_active = N AND so.objecttype_id = N
INNER JOIN icinga_instances AS i ON i.instance_id = c.instance_id
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE (so.name1 = 'S' AND so.name2 = 'S' AND CASE c.entry_type WHEN N THEN 'S' WHEN N THEN 'S' WHEN N THEN 'S' WHEN N THEN 'S' END IN ('S', 'S') AND ('S') = 'S')) AS c ORDER BY c.comment_timestamp DESC
smime.p7s
Description: S/MIME Cryptographic Signature
_______________________________________________ icinga-users mailing list icinga-users@lists.icinga.org https://lists.icinga.org/mailman/listinfo/icinga-users
