I had a requirement from one of the teams that uses SCOM monitoring – the requirement was to change alerting level on scom alerts from critical to warning for all servers that were not on production for BizTalk management pack. Now there are several rules and monitors related to this management pack – or in general any one management pack – there is no quick and easy way to override all of them. To do so manually would be very cumbersome and time consuming. Instead – a better way to approach this would be to find rules and monitors that generate the most alerts and override them instead. This would create the override for most relevant rules and monitors – the others can be overridden as and when they happen. The query I used to find most used rules and monitors is as below. I wanted the BizTalk management pack – this can be substituted with any other as necessary.
Use OperationsManagerDW
SELECT
alt.AlertName,
COUNT(*) AS AlertRepeated
FROM Alert.vAlertResolutionState AS ars INNER JOIN
Alert.vAlertDetail AS adt ON ars.AlertGuid=adt.AlertGuid INNER JOIN
Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid INNER JOIN
vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId
WHERE
alt.AlertName like ‘%biztalk%’
GROUP BY alt.AlertName
ORDER BY count(*) desc
Curious..about data 