Knowledge BaseMetric InsightsMetrics & Reports Statistical queries (SQL statements)Query for report that lists all elements with alert subscriptions for every user

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

E-Mail me when someone replies to this comment