/*

	AUTHOR:		Derik Hammer

	DATE:		20150507

	LINK:		http://www.sqlhammer.com/blog/querying-central-management-servers/

--*/



SELECT CASE( 

             SELECT name

               FROM msdb.dbo.sysmanagement_shared_server_groups

               WHERE server_group_id = Grps.parent_id )

       WHEN 'DatabaseEngineServerGroup' THEN 'NONE'

           ELSE( 

                 SELECT name

                   FROM msdb.dbo.sysmanagement_shared_server_groups

                   WHERE server_group_id = Grps.parent_id )

       END AS [Parent Group],

       Grps.name AS [Group Name],

       Srv.name AS [Display Name],

       Srv.server_name AS [Server Name],

       Srv.[description]

  FROM msdb.dbo.sysmanagement_shared_registered_servers Srv

       LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups Grps 

	  ON Srv.server_group_id = Grps.server_group_id;