image

/*Top active repeating alerts
Jonathan Almquist (http://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.