Investigate most common alert

The following SQL queries can be used to first list which machine or path and then rules or monitors that generate most alerts in your environment. The first query will show you which computer or path that generate most alerts. The second query will show you which rule or monitor that generate most alerts on one singel machine or path. Run both queries against your data warehouse database (OperationsManagerDW).

 

All Path
SELECT
vManagedEntity.Path, COUNT(1) AS pathcount
FROM Alert.vAlertDetail INNER JOIN
Alert.vAlert ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
vManagedEntity ON Alert.vAlert.ManagedEntityRowId =
vManagedEntity.ManagedEntityRowId
GROUP BY vManagedEntity.Path
ORDER BY pathcount DESC
 
 

 

One Path
SELECT
Alert.vAlert.AlertName,
Alert.vAlert.AlertDescription,
vManagedEntity.Path, COUNT(1) AS alertcount
FROM Alert.vAlertDetail INNER JOIN
Alert.vAlert ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
vManagedEntity ON Alert.vAlert.ManagedEntityRowId =
vManagedEntity.ManagedEntityRowId
WHERE Path = 'opsmgr29.hq.contoso.local'
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, vManagedEntity.Path
ORDER BY alertcount DESC

You could also use these queries in a report, take a look at this post about author custom reports.

About

Microsoft

One thought on “Investigate most common alert

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.