T-SQL query to find Primary Management Server for SCOM Agents
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]