Blog Post: Top Monitor Generated Alerts (Report Dataset)

/*Top monitor generated 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',

    vAlert.AlertName AS 'Alert',

    vMonitor.MonitorDefaultName AS 'Monitor',

    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

    vMonitor ON vMonitor.MonitorRowId = vAlert.WorkflowRowId INNER JOIN

    vManagementPack AS vMP ON vMP.ManagementPackRowId = vMonitor.ManagementPackRowId

WHERE (vAlert.MonitorAlertInd = 1) AND

    (vAlert.RaisedDateTime BETWEEN @StartDate AND @EndDate) AND

    (vMG.ManagementGroupRowId = @MGID)

GROUP BY vAlert.AlertName, vMonitor.MonitorDefaultName, vMP.ManagementPackDefaultName

ORDER BY 'Count' DESC

Alexis Bledel Kim Kardashian China Chow Alecia Elliott Kat Von D