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.
[…] Anders Bengtsson on Investigate most common alert […]