Query for report that lists all elements with alert subscriptions for every user
Use the following sample query (SQL statement) if you need report that lists all elements with alert subscriptions for every user (Data Source = Dashboard DB (SQL))
SELECT `t`.* FROM (SELECT u.display_name AS `User`, IF(arei.visualization_type = 'other', arei.visualization_element_id,arei.element_id) AS `element_id`, ldesv.element_dashboard_name AS `Element Name`, de.type AS `Type`, mi.name `Frequency` FROM `user_alert_rule` AS `uar` JOIN `alert_rule` AS `ar` ON ar.alert_rule_id=uar.alert_rule_id JOIN `alert_rule_element_info` AS `arei` ON arei.alert_rule_id=uar.alert_rule_id AND arei.element_id = uar.element_id JOIN `dashboard_element` AS `de` ON de.element_id=IF(arei.visualization_type = 'other',arei.visualization_element_id,arei.element_id) JOIN `user_dashboard_element_instance` AS `udei` ON udei.user_id=uar.user_id AND udei.element_id=de.element_id AND udei.segment_value_id= IF(uar.segment_value_id=-1,udei.segment_value_id, IF( (SELECT segment_id FROM dashboard_element WHERE element_id = IF(arei.visualization_type = "other",arei.visualization_element_id,arei.element_id)) = 0, 0, uar.segment_value_id)) AND udei.`favorite_id`=0 LEFT JOIN `segment_value` AS `sv` ON sv.segment_value_id=udei.segment_value_id LEFT JOIN `last_dashboard_element_segment_value` AS `ldesv` ON (ldesv.element_id,ldesv.segment_value_id)=(udei.element_id,udei.segment_value_id) JOIN user AS u ON u.user_id = uar.user_id JOIN measurement_interval AS mi ON mi.measurement_interval_id = de.measurement_interval_id WHERE (IF(sv.total_ind IS NULL,1,sv.total_ind = "N" OR (de.show_segment_total_ind="Y" AND sv.total_ind = "Y"))) GROUP BY element_id, uar.user_id HAVING (element_id IS NOT NULL)) AS `t` ORDER BY 1,2 ASC;
0 Comments
Add your comment