Showing posts with label Alerts. Show all posts
Showing posts with label Alerts. Show all posts

Wednesday, May 21, 2014

List Alerts for Specific SCOM Group

There was a query in the Microsoft System Center Community Forum requesting for assistance for a SQL query to list all alerts for a particular SCOM Group within a specific time frame.
I have create the query below and hopefully this will help anyone that needs something similar.
All is needed is to replace those in bold and underlined based on your needs.

select AlertName
,RGV.targetMonitoringObjectDisplayName [Server Name]
,RGV.SourceMonitoringObjectDisplayName [SCOM Group Name]
,AlertDescription [Alert]
,ars.DWCreatedDateTime [Alert Date]
,alt.Priority [Priority]
,alt.Severity [Severity]
      ,day(ars.DWCreatedDateTime) [triggerdate]
    ,month(ars.DWCreatedDateTime) [triggermonth]
      ,year(ars.DWCreatedDateTime) [triggeryear]
       from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
left join dbo.vManagedEntity ME on ME.ManagedEntityRowId = alt.ManagedEntityRowId
left join OperationsManager.dbo.RelationshipGenericView RGV on RGV.TargetMonitoringObjectDisplayName = ME.DisplayName
where ars.DWCreatedDateTime between 'start date (mm/dd/yy)' and  'end date (mm/dd/yy)'
and ME.DisplayName in (select TargetMonitoringObjectDisplayName
from OperationsManager.dbo.RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = 'Group Name' )