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

Martin Ehrnst
Systems Engineer working with SCOM, OMS and Azure
IT Pro with a passion for monitoring. Working with System Center, OMS, Azure and related software and cloud services.

Direct customer experience from previously being a Technical Account Manager.

Community supporter where I try to contribute via blogging and social media.

Posted by Martin Ehrnst

Working as a systems engineer in one of Norway's leading enterprise cloud providers. Mainly working with System Center, Azure and Windows server products

*All post are personal

Engage by commenting