User-Manager relationship in Service Management DB

David_Sebba

I'm trying to figure out how to build a dashboard based on the organizational manager for analysts.  There's a way to build this query in the Data Warehouse, but I'd like to use a query based on Service Managerment.

Justin_Workman

@David_Sebba - CI$User doesn't have this info.  But this query will work against ServiceManager and you can build a dashboard around it:

SELECT u.DisplayName AS 'User', m.DisplayName AS 'Manager'

  FROM [ServiceManager].[dbo].[Relationship] r

  INNER JOIN dbo.MT_System$Domain$User m

  ON m.BaseManagedEntityId = r.SourceEntityId

  INNER JOIN MT_System$Domain$User u

  ON u.BaseManagedEntityId = r.TargetEntityId

  WHERE RelationshipTypeId = '4a807c65-6a1f-15b2-bdf3-e967e58c254a'

  order by u.DisplayName

Justin_Workman

@David_Sebba - CI$User doesn't have this info.  But this query will work against ServiceManager and you can build a dashboard around it:

SELECT u.DisplayName AS 'User', m.DisplayName AS 'Manager'

  FROM [ServiceManager].[dbo].[Relationship] r

  INNER JOIN dbo.MT_System$Domain$User m

  ON m.BaseManagedEntityId = r.SourceEntityId

  INNER JOIN MT_System$Domain$User u

  ON u.BaseManagedEntityId = r.TargetEntityId

  WHERE RelationshipTypeId = '4a807c65-6a1f-15b2-bdf3-e967e58c254a'

  order by u.DisplayName

David_Sebba

Thanks!  I'm trying to do it against Service Management to avoid using Service Manager.  But I'll see if I can use this.