Agent Management–List Primary and Failover Configuration

Something I don’t like about using the SDK (powershell) to manage agents, are the get* member cmdlet’s to return information – large scale queries take too long! The SDK is typically pretty slow in this regard, and that’s a shame because I find myself writing TSQL to accomplish tasks that the SDK should be able to promptly handle.

Recently I wrote some TSQL that will return all agents with their associated primary and failover management servers. This is very informative when the question "where does this agent failover to", and it’s a speedy way to implement some sort of automation process to expedite agent assignment.


Here you go!


SELECT rgv.SourceObjectPath AS [Agent], rgv.TargetObjectPath AS [ManagementServer], 
              WHEN rtv.DisplayName = 'Health Service Communication' THEN 'Primary'
              ELSE 'Failover'
       END AS [Type]
FROM ManagedTypeView mt INNER JOIN
       ManagedEntityGenericView AS meg ON meg.MonitoringClassId = mt.Id INNER JOIN
       RelationshipGenericView rgv ON rgv.SourceObjectId = meg.Id INNER JOIN
       RelationshipTypeView rtv ON rtv.Id = rgv.RelationshipId
WHERE mt.Name = 'Microsoft.SystemCenter.Agent' AND
       rtv.Name like 'Microsoft.SystemCenter.HealthService%Communication' AND
       rgv.IsDeleted = 0
ORDER BY rgv.SourceObjectPath ASC, rtv.DisplayName ASC


Something like this would take several minutes in small to medium sized environments, and maybe upwards of 15-30 minutes in larger environments. This little bit of TSQL returns in 1-2 seconds. Eat that!



3 thoughts on “Agent Management–List Primary and Failover Configuration”

  1. Hi Jonathan,

    Excellent job, I like this.

    I wanted to ask you, Is there any SQL Query also available to update the SCOM Agents primary and fail over server ?

    Doing this in Shell for a large server makes the Shell script time out.

    It would be great if you have any script to update this info directly into the database its self.

    1. Gautam – I suppose taking the above and turning it into an UPDATE statement would do the trick, but I’m not sure how well that would work (if at all – I’ve never tried). This is an agent configuration, and I would suspect an update like this in the database would not be “supported”. If it’s timing out for you, then maybe a good approach would be to build collections of 100 each, and iterate through them using some sort of logic…

Comments welcome (links require moderation)