SCOM Community MP Catalog

Another great SCOM community contribution from the guys over at SquaredUP  and Nathan Foreman – an open sourced community management pack catalog for SCOM. This basically lets you discover community management packs directly within the SCOM console, much like the built-in version and the announced partner catalog.

It will also show (or notify you) on any updates to the already installed MP’s you have installed and listed in the catalog.

As part of the moderator team, I encourage everyone to check it out or contribute on GitHub

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