I had to write this query as SCOM dashboard doesn’t always give depict a proper view of the open active alerts in the system. This would be helpful for creating reports for SCOM or for monitoring purposes as to which alerts are open and from which management packs.

DROP TABLE #Base_State
Go
SELECT *
INTO #Base_State
from
(
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],
aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS Status
,mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
 FROM
OperationsManager.
dbo.AlertView (NOLOCK) aa
 LEFT JOIN
 OperationsManager.dbo.RuleView (NOLOCK) Rv
ON aa.MonitoringRuleId = rv.Id
JOIN OperationsManager.dbo.ManagementPack mp
ON rv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
union
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254
 THEN 'Open' ELSE 'closed' END AS STATUS,
mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
FROM
OperationsManager.dbo.AlertView (NOLOCK) aa JOIN OperationsManager.dbo.MonitorView (NOLOCK) Mv
ON aa.MonitoringRuleId = Mv.Id
 JOIN OperationsManager.dbo.ManagementPack mp
ON mv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
UNION
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,ars.ResolutionState,
CASE WHEN ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vRule (NOLOCK) Vr
ON aa.AlertProblemGuid =  Vr.RuleGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON vr.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
ars.ResolutionState = 255
union
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,
ars.ResolutionState,
CASE WHEN
 ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vMonitor (NOLOCK) Vm
ON aa.AlertProblemGuid =  Vm.MonitorGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON Vm.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
 ars.ResolutionState = 255
) AS Base

SELECT bs.AlertId,bs.RaisedDateTime,
bs.ResolutionState,bs.Status,
bs.ManagementPack
FROM #Base_State BS
WHERE Bs.Status = 'Open'

Tagged: Alerts, Operations Manager, SCOM, SQL Server Monitoring