SQL query to show Alerts raised by Groups

Hi all,

I am trying to show alerts generated from monitors assigned to specific groups.

Does anyone know what query to run or table to target to show alerts from Groups?

Try this:

 

SELECT CONVERT(VARCHAR(19),[RaisedDateTime],120) as Date, a.AlertName, me.Path, a.AlertDescription
FROM
Alert.vAlert AS a
JOIN
vManagedEntity AS me ON a.ManagedEntityRowId = me.ManagedEntityRowId
and me.TopLevelHostManagedEntityRowId in
(
–Get list of objects in a group (pass ManagedEntityRowId for the group as a parameter)
select distinct vme2.ManagedEntityRowId
–,DisplayName = vme2.displayname
from vrelationship r
inner join vManagedEntity vme on vme.ManagedEntityRowId=r.TargetManagedEntityRowId
inner join vManagedEntity vme2 on vme.TopLevelHostManagedEntityRowId=vme2.ManagedEntityRowId
inner join vRelationshipManagementGroup rmg on rmg.RelationshipRowId=r.RelationshipRowId
where SourceManagedEntityRowId=
(
–Get ID for a specific group
select Distinct ManagedEntityRowId from vManagedEntity
inner join vRelationship on vManagedEntity.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId
inner join vRelationshipType on vRelationship.RelationshipTypeRowId=vRelationshipType.RelationshipTypeRowId
inner join vRelationshipManagementGroup on vRelationshipManagementGroup.RelationshipRowId=vRelationship.RelationshipRowId
where (vRelationshipType.RelationshipTypeSystemName=’Microsoft.SystemCenter.ComputerGroupContainsComputer’
or vRelationshipType.RelationshipTypeSystemName like ‘%InstanceGroup%’)
and DisplayName=’<Insert Group Name here>’
and vRelationshipManagementGroup.ToDateTime is null
)
and rmg.ToDateTime is null
)