Building groups in Operations Manager, with a bit of Orchestrator magicThe 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 pathcountFROM 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.ManagedEntityRowIdWHERE Path = 'opsmgr29.hq.contoso.local'
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, vManagedEntity.Path
ORDER BY alertcount DESCYou 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 […]