[OpsMgr 2007 R2][OpsMgr 2012] Alerts associated to one MP Report

Source : Jean-Sébastien DUCHÊNE - in this article >>> HERE <<<


The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to list all alerts raised by a Management Pack in a given interval.
Beware: the proposed data depend on the retention time of the Data WareHouse.
Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager  and SQL Server 2008 RTM/R2.


Here is the query:

SELECT     Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
                      CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
                      CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
                      Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM         Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
                      Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
                      vRule WITH(NOLOCK) ON (vRule.RuleRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 0) INNER JOIN
                      vManagementPack WITH (NOLOCK) ON (vRule.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE     (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
UNION
 SELECT     Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
                      CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
                      CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
                      Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM         Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
                      Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
                      vMonitor WITH(NOLOCK) ON (vMonitor.MonitorRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 1) INNER JOIN
                      vManagementPack WITH (NOLOCK) ON (vMonitor.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE     (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
ORDER BY  Alert.vAlert.RaisedDateTime DESC

Download Management Pack - Alerts Raised Report

This posting is provided "AS IS" with no warranties.