Top 50 alert generating rules and monitors in last 7 days, sorted by alert + repeat count
/*Top 50 alert generating rules and monitors in last 7 days, sorted by alert + repeat count.*/
USE OperationsManagerDW
SELECT TOP (50) Alerts.AlertName, SUM(1) AS 'Alert Instances', SUM(Alerts.RepeatCount + 1) AS 'Alert + Repeat Count',
CASE Alerts.MonitorAlertInd WHEN 1 THEN vMonitor.MonitorDefaultName WHEN 0 THEN vRule.RuleDefaultName ELSE 'Not found' END AS 'Rule or Monitor Name',
CASE Alerts.MonitorAlertInd WHEN 1 THEN 'Monitor' WHEN 0 THEN 'Rule' ELSE 'Not Found' END AS 'Rule or Monitor'
FROM Alert.vAlert AS Alerts LEFT OUTER JOIN
vRule AS vRule ON Alerts.WorkflowRowId = vRule.RuleRowId LEFT OUTER JOIN
vMonitor AS vMonitor ON Alerts.WorkflowRowId = vMonitor.MonitorRowId
WHERE (Alerts.RaisedDateTime BETWEEN DATEADD(day, - 6, GETDATE()) AND GETDATE())
GROUP BY Alerts.AlertName, Alerts.MonitorAlertInd, vMonitor.MonitorDefaultName, vRule.RuleDefaultName
ORDER BY 'Alert + Repeat Count' DESC