---
title: "Hunting quick closing SCOM alerts"
date: 2017-10-12
updated: 2025-01-07
author: Martin Ehrnst
section: operationsmanager
categories: [Operations Manager]
tags: [database, OpsMgr, QUickPublish, SCOM]
url: "https://adatum.no/operationsmanager/hunting-quick-closing-scom-alerts/"
canonicalUrl: "https://adatum.no/operationsmanager/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](https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries/)
###