Knowledge BaseMetrics & Reports Statistical queries (SQL statements)Query for report that lists all expired/active elements according to categories

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
;

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
;

0 Comments

Add your comment

E-Mail me when someone replies to this comment