SQL Query for top alerts for specific group

Dashboard Server
, ,
1

Hello,

I am not knowledge able on how to write a query that will only show me say the top ten noisiest objects for everything that is contained in a particular group. It would be nice to get another query that can display the most common alerts for a specific group as well.

Kevin H. does not have a query that will do this, yet.

Thank you in advance

1 Like
2

Hello,

There is a built in report that should be able to provide the information you are looking for.

On the Reporting Tab in the Operations Console, navigate to the Microsoft Generic Report Library folder. In here you will find an Alerts report that can be scoped to the group level.

 

If you specifically want a SQL Query that returns this data please add a comment, these reports are based on SSRS and could provide hints into how to make it.

3

Nathan,

Yes I am just looking for a SQL Query that will give me a count of alerts for the specific group. Would want to break the alerts down by type as well.

4

Nathan,

Yes I am just looking for a SQL Query that will give me a count of alerts for the specific group. Would want to break the alerts down by type as well.

5

There’s two non-SQL methods that could work - popping it here for others that may be interested.

  1. Add your SCOM workspace to OMS, and use the Alert Management Solution to analyse your alerts.
  2. I use a powershell script to return a list of open alerts for a group or class.Maybe building on something like that could work?
6

I am not getting any solution for my SQL, there are lots of error which I face during the software compilation, and also my printer is in an error state do not understand how to solve all these problems, my system does not support me at all.

7

Perhaps you can start with this query that gets the member of the group?
http://opsit.blogspot.se/2013/11/useful-sql-queries-in-scom-database.html

select SourceMonitoringObjectDisplayName as ‘Group Name’,
TargetMonitoringObjectDisplayName as ‘Group Members’
from RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = ‘Agent Managed Computer Group’
ORDER BY TargetMonitoringObjectDisplayName