T-SQL query to find Primary Management Server for SCOM Agents

Question

I am trying to replicate the function of a script which uses PowerShell to get info about SCOM agents. I can get everything I am trying to do for the moment except get the Primary Management Server for each agent, so the info that is returned by the PrimaryManagementServerName property when you run the Get-SCOMAgent cmdlet.

I've searched the tables/views etc in the OperationsManager database but have hit a wall, has anyone else tried to do this via T-SQL and been successful? I also tried running a trace on my login while using the SCOM console and selecting the Managed Agents view, but the console isn't passing through my credentials.

Answer 1

In case anyone else is interested, I found a query that seems to do the trick finally. The query was found here

SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryManagementServer
FROM Relationship  R WITH  (NOLOCK) 
JOIN BaseManagedEntity SourceBME ON  R.SourceEntityID  = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME ON  R.TargetEntityID  = TargetBME.BaseManagedEntityID 
WHERE  R.RelationshipTypeId  = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK) 
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK) 
where TypeName  = 'Microsoft.SystemCenter.GatewayManagementServer') and IsDeleted  ='0') 
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK) 
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK) 
where TypeName  = 'Microsoft.SystemCenter.ManagementServer') and IsDeleted  ='0') 
AND  R.IsDeleted  = '0'
ORDER BY [PrimaryManagementServer],[Agent]