If you use CMS
If you have a large group of servers and can't memorize all the names/IPs
Use below code to show them all (and copy to Excel to format)
Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)
WITH MyCTE
AS (
--root, anchor
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
WHERE is_system_object <>1 AND parent_id = 1
UNION ALL
--child
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id
)
SELECT
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END
,G.name, G.ParentName
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'
FROM MyCTE G
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id
WHERE 1 = 1
ORDER BY
GroupLevel ASC, Parent, Child, [Display name]
No comments:
Post a Comment