Home » Scripts » Investigate most common alert

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP
MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
OMS
Inside the Microsoft Operations Management Suite

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.


1 Comment

Leave a comment

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.