Closed dgoetz closed 5 years ago
This would require us to duplicate some logic from the monitoring module combined with reading/evaluating monitoring restrictions. The Cube has initially been designed as a drill-down tool for power users and not as a visualization tool for restricted users. I'm well aware of the fact that quite some installations use the cube for custom dashboards, so adding the possibility to grant restricted access would be a nice feature.
Played around a bit while figuring out how this entire ROLLUP works. An early prototype query looks like this:
SELECT rollup.env,
rollup.location,
rollup.department,
rollup.hosts_cnt,
rollup.hosts_nok,
rollup.hosts_unhandled_nok
FROM (SELECT sub.env,
sub.location,
sub.department,
SUM(hosts_cnt) AS hosts_cnt,
SUM(hosts_nok) AS hosts_nok,
SUM(hosts_unhandled_nok) AS hosts_unhandled_nok
FROM (SELECT c_env.varvalue AS env,
c_location.varvalue AS location,
c_department.varvalue AS department,
COUNT(*) AS hosts_cnt,
SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok,
SUM(CASE
WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND
hs.scheduled_downtime_depth = 0 THEN 1
ELSE 0 END) AS hosts_unhandled_nok
FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_env
ON c_env.varname = 'Env' AND c_env.object_id = o.object_id
LEFT JOIN icinga_customvariablestatus AS c_location
ON c_location.varname = 'location' AND c_location.object_id = o.object_id
LEFT JOIN icinga_customvariablestatus AS c_department
ON c_department.varname = 'department' AND c_department.object_id = o.object_id
WHERE h.host_id = (
SELECT s_h.host_id
FROM icinga_objects as s_so
INNER JOIN icinga_services as s_s on s_s.service_object_id = s_so.object_id
INNER JOIN icinga_servicestatus as s_ss on s_ss.service_object_id = s_s.service_object_id
INNER JOIN icinga_hosts as s_h on s_h.host_object_id = s_s.host_object_id
WHERE s_so.is_active = 1 and s_so.objecttype_id = 2 and s_ss.current_state != 0 and s_h.host_id = h.host_id
LIMIT 1
)
GROUP BY c_env.varvalue, c_location.varvalue, c_department.varvalue
) AS sub
GROUP BY (env), (location), (department)
WITH ROLLUP) AS rollup
ORDER BY (rollup.env IS NOT NULL) ASC, rollup.env ASC, (rollup.location IS NOT NULL) ASC, rollup.location ASC,
(rollup.department IS NOT NULL) ASC, rollup.department ASC, (rollup.hosts_cnt IS NOT NULL) ASC,
rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC,
(rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC;
Or with a CTE, though that's roughly 50% slower: (With a dataset of ~600 hosts and ~11k services)
WITH service_states as (
SELECT h.host_id
FROM icinga_objects as so
INNER JOIN icinga_services as s on s.service_object_id = so.object_id
INNER JOIN icinga_servicestatus as ss on ss.service_object_id = s.service_object_id
INNER JOIN icinga_hosts as h on h.host_object_id = s.host_object_id
WHERE so.is_active = 1 and so.objecttype_id = 2 and ss.current_state != 0
GROUP BY h.host_id
)
SELECT rollup.env,
rollup.location,
rollup.department,
rollup.hosts_cnt,
rollup.hosts_nok,
rollup.hosts_unhandled_nok
FROM (SELECT sub.env,
sub.location,
sub.department,
SUM(hosts_cnt) AS hosts_cnt,
SUM(hosts_nok) AS hosts_nok,
SUM(hosts_unhandled_nok) AS hosts_unhandled_nok
FROM (SELECT c_env.varvalue AS env,
c_location.varvalue AS location,
c_department.varvalue AS department,
COUNT(*) AS hosts_cnt,
SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok,
SUM(CASE
WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND
hs.scheduled_downtime_depth = 0 THEN 1
ELSE 0 END) AS hosts_unhandled_nok
FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_env
ON c_env.varname = 'Env' AND c_env.object_id = o.object_id
LEFT JOIN icinga_customvariablestatus AS c_location
ON c_location.varname = 'location' AND c_location.object_id = o.object_id
LEFT JOIN icinga_customvariablestatus AS c_department
ON c_department.varname = 'department' AND c_department.object_id = o.object_id
INNER JOIN service_states as ss on ss.host_id = h.host_id
GROUP BY c_env.varvalue, c_location.varvalue, c_department.varvalue) AS sub
GROUP BY (env), (location), (department)
WITH ROLLUP) AS rollup
ORDER BY (rollup.env IS NOT NULL) ASC, rollup.env ASC, (rollup.location IS NOT NULL) ASC, rollup.location ASC,
(rollup.department IS NOT NULL) ASC, rollup.department ASC, (rollup.hosts_cnt IS NOT NULL) ASC,
rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC,
(rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC;
Added here just as personal note. Comment it or don't. :roll_eyes: :grin:
Expected Behavior
Cube module does not show more objects than you could see in the monitoring module.
Current Behavior
Cube module does show all objects.
Possible Solution
Apply restrictions from monitoring module or at least provide the possibility to add the same restrictions.
Context
Users are restricted to systems relevant for them in the monitoring module, but can see more than these in the cube.
ref/NC/590017