Top Active Repeating Alerts (Report Dataset)


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.

Comments (0)

Skip to main content