Query for report that lists all expired/active elements according to categories
Use the following sample query (SQL statement) if you need report that lists all expired elements for each category.
SELECT DISTINCT dc.category, de.name, de.element_id,
IF(de.type = "multi-metric chart",ldesv_mm.expiration_time,ldesv.expiration_time) AS `Expiration Time`
FROM dashboard_element AS de
JOIN dashboard_category AS dc ON (dc.category_id=de.category_id)
JOIN last_dashboard_element_segment_value AS ldesv ON (ldesv.element_id=de.element_id)
LEFT JOIN dashboard_element de_mm_stoplight ON de_mm_stoplight.element_id = de.multi_chart_stoplight_metric_element_id
LEFT JOIN segment_value_info AS sv_mm_stoplight ON sv_mm_stoplight.segment_id = de_mm_stoplight.segment_id AND sv_mm_stoplight.segment_value_id IN (0, ldesv.segment_value_id)
LEFT JOIN last_dashboard_element_segment_value ldesv_mm ON (ldesv_mm.element_id, ldesv_mm.segment_value_id) = (de_mm_stoplight.element_id, sv_mm_stoplight.segment_value_id) AND ldesv_mm.expiration_time IS NOT NULL
WHERE
(de.type = "multi-metric chart" AND NOW() > ldesv_mm.expiration_time)
OR (de.type != "multi-metric chart" AND NOW() > ldesv.expiration_time)
ORDER BY 1,2
;
Click to copy
Use the following sample query (SQL statement) if you need report that lists all active elements for each category.
SELECT DISTINCT dc.category, de.name, de.element_id,
IF(de.type = "multi-metric chart",ldesv_mm.expiration_time,ldesv.expiration_time) AS `Expiration Time`
FROM dashboard_element AS de
JOIN dashboard_category AS dc ON (dc.category_id=de.category_id)
JOIN last_dashboard_element_segment_value AS ldesv ON (ldesv.element_id=de.element_id)
LEFT JOIN dashboard_element de_mm_stoplight ON de_mm_stoplight.element_id = de.multi_chart_stoplight_metric_element_id
LEFT JOIN segment_value_info AS sv_mm_stoplight ON sv_mm_stoplight.segment_id = de_mm_stoplight.segment_id AND sv_mm_stoplight.segment_value_id IN (0, ldesv.segment_value_id)
LEFT JOIN last_dashboard_element_segment_value ldesv_mm ON (ldesv_mm.element_id, ldesv_mm.segment_value_id) = (de_mm_stoplight.element_id, sv_mm_stoplight.segment_value_id)
AND ldesv_mm.expiration_time IS NULL
AND ldesv.expiration_time IS NULL
WHERE
(de.type = "multi-metric chart" AND NOW() < ldesv_mm.expiration_time)
OR (de.type != "multi-metric chart" AND NOW() < ldesv.expiration_time)
ORDER BY 1,2
;
Click to copy
0 Comments
Add your comment