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], CASE 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!