Top Active Repeating Alerts (Report Dataset)

 /*Top active repeating alerts
 Jonathan Almquist (https://blogs.technet.com/b/jonathanalmquist/)
 04-08-2011
 */
  
 DECLARE @RowCount AS INT,
     @MGID AS INT,
     @TimeZoneOffset AS INT,
     @OffSetDays AS INT,
     @StartDate AS DATE,
     @EndDate AS DATE
     
 SET @RowCount = 20
 SET @MGID = 1
 SET @TimeZoneOffset = 5
 SET @OffSetDays = 60
 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE()))
 SET @EndDate = DATEADD(hour, @TimeZoneOffset, GETDATE())
  
 SELECT TOP (@RowCount) SUM(1) AS 'Count',
         SUM(RepeatCount) AS 'Repeat',
         vAlert.AlertName AS 'Alert',
         vRule.RuleDefaultName AS 'Rule',
         vMP.ManagementPackDefaultName AS 'MP'
 FROM Alert.vAlert AS vAlert INNER JOIN
         vManagedEntity AS vME ON vME.ManagedEntityRowId = vAlert.ManagedEntityRowId INNER JOIN
         vManagementGroup AS vMG ON vMG.ManagementGroupRowId = vME.ManagementGroupRowId INNER JOIN
         vRule ON vRule.RuleRowId = vAlert.WorkflowRowId INNER JOIN
         vManagementPack AS vMP ON vMP.ManagementPackRowId = vRule.ManagementPackRowId
 WHERE (vAlert.MonitorAlertInd = 0) AND (vAlert.RepeatCount <> 0) AND
         (vAlert.DWLastModifiedDateTime BETWEEN @StartDate AND @EndDate) AND 
         (vMG.ManagementGroupRowId = @MGID)
 GROUP BY vAlert.AlertName, vMP.ManagementPackDefaultName, vRule.RuleDefaultName
 ORDER BY Repeat DESC

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.