Top Rule Generated Alerts (Report Dataset)


image

/*Top rule generated alerts report dataset
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',
    vAlert.AlertName AS 'Alert',
    vRule.RuleDefaultName AS 'Rule',
    vMP.ManagementPackDefaultName AS 'MP'
FROM  Alert.vAlert AS vAlert INNER JOIN
    vRule AS vRule ON vAlert.WorkflowRowId = vRule.RuleRowId INNER JOIN
    vManagementPack AS vMP ON vMP.ManagementPackRowId = vRule.ManagementPackRowId INNER JOIN
    vManagedEntity AS vME ON vME.ManagedEntityRowId = vAlert.ManagedEntityRowId INNER JOIN
    vManagementGroup AS vMG ON vMG.ManagementGroupRowId = vME.ManagementGroupRowId
WHERE (vAlert.MonitorAlertInd = 0) AND
    (vAlert.RaisedDateTime BETWEEN @StartDate AND @EndDate) AND
    (vMG.ManagementGroupRowId = @MGID)
GROUP BY vAlert.AlertName, vMP.ManagementPackDefaultName, vRule.RuleDefaultName
ORDER BY 'Count' DESC

 

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

Comments (0)

Skip to main content