Hunting quick closing SCOM alerts

I had a feeling that we had alot of alerts that closed automatically, very quickly and without any interaction from automation. To get an overview I put together this SQL query to run against the Datawarehouse DB.

The query joins three views and extract the alert Guids, name, ManagedEntity (monitoring object) and alert name.


select Res.AlertGuid, Res.TimeFromRaisedSeconds, Alert.AlertName, Entity.ManagedEntityDefaultName, Entity.ManagedEntityGuid from Alert.vAlertResolutionState AS Res
INNER JOIN Alert.vAlert as Alert
on Res.AlertGuid = Alert.AlertGuid
INNER JOIN vManagedEntity AS Entity
on Alert.ManagedEntityRowId = Entity.ManagedEntityRowId
where Res.TimeFromRaisedSeconds < '30' AND Res.StateSetByUserId = 'System' AND ResolutionState = '255' AND Alert.RaisedDateTime >= DATEADD(day,-30, GETDATE())


With a little help from my DBA we ended up with this which also group alert name and count the # of alerts for each object

select
--Res.AlertGuid
--, Res.TimeFromRaisedSeconds
COUNT(*)
,Alert.AlertName
--, Entity.ManagedEntityDefaultName
, Entity.Path
, Entity.DisplayName
from Alert.vAlertResolutionState AS Res
INNER JOIN Alert.vAlert as Alert
on Res.AlertGuid = Alert.AlertGuid
INNER JOIN vManagedEntity AS Entity
on Alert.ManagedEntityRowId = Entity.ManagedEntityRowId
where Res.TimeFromRaisedSeconds < '49' AND Res.StateSetByUserId = 'System' AND ResolutionState = '255' AND Alert.RaisedDateTime >= DATEADD(day,-4, GETDATE())
GROUP BY AlertName, Path, DisplayName

For other useful SCOM SQL Queries, take a look at Kevin Holman’s blog