SQL Query DWH - Show a specific alarm with members of a specific group

Dashboard Server
, ,
1

Hello!

 

I’m a beginner in Transact-SQL.

Currently I´m trying to create a query that allows me to establish a connection between a group and an alarm in the SCOM DWH.

The goal is to list a specific alarm with members of a specific group.

 

With this query I can show all alarms with a specific AlertName:

 

SELECT TOP 100 a.AlertName, me.DisplayName, me.ManagedEntityGuid, a.RaisedDateTime

FROM Alert.vAlert AS a

Join vManagedEntity AS me ON a.ManagedEntityRowId = me.ManagedEntityRowId

Where a.AlertName = 'Failed to connect to Computer'

Order BY a.RaisedDateTime DESC

 

 

And with this query I can get a list of all groups:

 

select distinct DisplayName,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 vRelationshipManagementGroup.ToDateTime is null

order by DisplayName asc

 

 

Now I tried to combine both querys but without success.

select DISTINCT me.DisplayName, me.ManagedEntityRowId, a.AlertName, a.RaisedDateTime, me.Path from vManagedEntity AS me

join Alert.vAlert AS a on a.ManagedEntityRowId = me.ManagedEntityRowId

inner join vRelationship AS rs on me.ManagedEntityRowId=rs.SourceManagedEntityRowId

inner join vRelationshipType AS rst on rs.RelationshipTypeRowId=rst.RelationshipTypeRowId

inner join vRelationshipManagementGroup AS vrsm on vrsm.RelationshipRowId=rs.RelationshipRowId

where (rst.RelationshipTypeSystemName='Microsoft.SystemCenter.ComputerGroupContainsComputer'

or rst.RelationshipTypeSystemName like '%InstanceGroup%')

and vrsm.ToDateTime is null

order by DisplayName asc

 

 

I get a result but not that what I want to have…

 

Any ideas?