Client Automation - All Versions
Following SQL Query could be used :
IF OBJECT_ID('tempdb..#TEMP_TABLE','U') IS NOT NULL DROP TABLE #TEMP_TABLE
SELECT CAST(IIF(m.group_uuid in (select group_uuid from ca_group_def where label='$AllComputerUserGroups'),CAST(0 AS BINARY(16)), m.group_uuid) AS BINARY(16)) AS 'parent_uuid',
parent.group_uuid, parent.label
INTO #TEMP_TABLE
FROM ca_group_def parent
INNER JOIN ca_group_member m ON parent.group_uuid=m.member_uuid
WHERE parent.member_type=1 and parent.group_system=1
ORDER BY parent_uuid;
WITH CTE AS (
SELECT parent_uuid, group_uuid, label, 0 as Group_Level, CAST('_____' + label AS VARCHAR(MAX)) AS OrderByField
FROM #TEMP_TABLE
WHERE parent_uuid=0x0
UNION ALL
SELECT t.parent_uuid, t.group_uuid, t.label, (c.Group_level+1) as Group_Level, c.OrderByField + CAST('_____' + t.label AS VARCHAR(MAX)) AS OrderByField
FROM #TEMP_TABLE t
INNER JOIN CTE c ON c.group_uuid = t.parent_uuid
)
SELECT (REPLICATE( '-----' , Group_Level ) + label) AS Hierachy FROM CTE
ORDER BY OrderByField
Output Example :
In DSM Explorer :
IF OBJECT_ID('tempdb..#TEMP_TABLE','U') IS NOT NULL DROP TABLE #TEMP_TABLE
DECLARE @domain_uuid AS BINARY(16)
select @domain_uuid=set_val_uuid from ca_settings where set_id=1
SELECT CAST(IIF(m.group_uuid in (select group_uuid from ca_group_def where label='$AllComputerUserGroups'),CAST(0 AS BINARY(16)), m.group_uuid) AS BINARY(16)) AS 'parent_uuid',
parent.group_uuid, parent.label,
IIF(parent.domain_uuid=@domain_uuid,'DM','EM') AS 'Origin',
IIF(query_uuid IS NULL,'Static',IIF(evaluation_uuid IS NULL,'Dynamic - All Engine', 'Dynamic'+ ' - '+e.label+' - '+LTRIM(STR(eval_freq/60))+' minutes')) AS 'Type', 0 'NBComputers'
INTO #TEMP_TABLE
FROM ca_group_def parent
INNER JOIN ca_group_member m ON parent.group_uuid=m.member_uuid
LEFT JOIN ca_engine e ON parent.evaluation_uuid IS NOT NULL and parent.evaluation_uuid=e.engine_uuid
WHERE parent.member_type=1 and parent.group_system=1
ORDER BY parent_uuid;
UPDATE #TEMP_TABLE
SET NBComputers=c.nb
FROM (select t.group_uuid, count(*) nb FROM ca_group_member g, #TEMP_TABLE t WHERE g.group_uuid=t.group_uuid and g.member_type=1 GROUP BY t.group_uuid) as c
WHERE #TEMP_TABLE.group_uuid=c.group_uuid;
WITH CTE AS (
SELECT parent_uuid, group_uuid, label, 0 as Group_Level, CAST('_____' + label AS VARCHAR(MAX)) AS OrderByField, Origin, [Type], NBComputers
FROM #TEMP_TABLE
WHERE parent_uuid=0x0
UNION ALL
SELECT t.parent_uuid, t.group_uuid, t.label, (c.Group_level+1) as Group_Level, c.OrderByField + CAST('_____' + t.label AS VARCHAR(MAX)) AS OrderByField, t.Origin, t.Type, t.NBComputers
FROM #TEMP_TABLE t
INNER JOIN CTE c ON c.group_uuid = t.parent_uuid
)
SELECT (REPLICATE( '-----' , Group_Level ) + label) Hierachy, Origin, Type, NBComputers FROM CTE
ORDER BY OrderByField