1

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.

  • I founded this table Agents but I don't know if is the one. Check the info in your database – Krismorte Jan 29 '18 at 23:56
  • I can't see any table, view, SP or function named that? At the moment the tables and views I'm using are dbo.ManagedEntityGenericView, dbo.ManagedTypeView, dbo.MT_HealthService, and dbo.MT_Computer – Gavan O'Connor-Risch Jan 30 '18 at 0:29
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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.