Help & Documentation
Open Support Ticket

Query for the report that lists the number of alert notifications for each day (delivered not more than a week ago)

Metric Insights stores information about delivery within the week.
Use the following sample query (SQL statement) if you need report that lists the number of alert notifications (distinct emails / SMS messages / smartphone alerts) for each day (Data Source = Dashboard DB (SQL)).
But it does not give results on Alerts which were delivered more than week ago.

SELECT
  CAST(IFNULL(eq.created, eqdl.created) AS DATE) AS date,
  IF(aeen.is_alert_phone_ind IS NULL,NULL,
      IF(aeen.is_alert_phone_ind = 'Y', 'phone tray',
         IF(aeen.is_sms_ind = 'Y', 'sms',
            IF(aeen.is_immediate_ind = 'Y', 'immediate email', 'email digest')))) AS `delivery_method`,
  COUNT(*) AS amount
FROM alert_event_email_notification AS aeen
  LEFT JOIN `email_queue` AS `eq` ON eq.email_queue_id = aeen.email_queue_id
  LEFT JOIN `email_queue_delete_log` AS `eqdl` ON eqdl.email_queue_id = aeen.email_queue_id
  WHERE eq.created IS NOT NULL OR eqdl.created IS NOT NULL AND aeen.user_email_notification_enabled_ind='Y'
GROUP BY 1, 2;
Click to copy