Get List of Managed Servers

-- note: must be run from one of the managed servers connected to the CMS server instance

SELECT

server_id AS server_id,

server_groups.name AS 'server_group_name',

server_name AS server_name,

shared_servers.description AS 'server_description',

(

SELECT name

FROM msdb.dbo.sysmanagement_shared_server_groups AS shared_groups

WHERE shared_groups.is_system_object = 1

AND shared_servers.server_type = shared_groups.server_type

) AS 'server_type_name'

FROM msdb.dbo.sysmanagement_shared_registered_servers AS shared_servers

INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS server_groups

ON shared_servers.server_group_id = server_groups.server_group_id;