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