mhorner 67968 (11/12/2015)
I'm using PowerShell as well but not with the CMS. How does that work? I'd be appreciative of that information and a copy of your script if you don't mind. Thanks, Marcus.
you can query the msdb database, of the server that serves up the Central Management Server list.
with that list, you can use powershell to connect to each one;
select * from msdb.dbo.sysmanagement_shared_server_groups_internal
select * from msdb.dbo.sysmanagement_shared_registered_servers_internal
select * from msdb.dbo.sysmanagement_shared_registered_servers
select * from msdb.dbo.sysmanagement_shared_server_groups
here's an example where i pull stuff together:
SELECT ROW_NUMBER()
OVER (
PARTITION BY TheServer.NAME
ORDER BY TheServer.NAME) AS rw,
TheGroup.NAME AS [servergroup],
TheGroup.[Description] AS [groupdescription],
TheServer.NAME,
TheServer.server_name AS [servername],
TheServer.[description] AS [description]
FROM [HOL-WKS-444].msdb.dbo.sysmanagement_shared_server_groups_internal TheGroup
LEFT JOIN [HOL-WKS-444].msdb.dbo.sysmanagement_shared_registered_servers_internal TheServer
ON TheGroup.server_group_id = TheServer.server_group_id
WHERE TheGroup.server_type = 0 --only the Database Engine Server Group
AND server_name IS NOT NULL
Lowell