SCOM -TSQL Script – Find active alerts in a SCOM managed environment


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'

 

Scom

Advertisements

Tagged: , , ,

One thought on “SCOM -TSQL Script – Find active alerts in a SCOM managed environment

  1. Gavin Speed January 11, 2016 at 6:46 pm Reply

    Great work!….

    Rather than output a list of alert by MP as you’ve done, what part of the query would need to changed to output a ‘count’ of alerts per MP?

    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: