Query to list hierarchy of ITCM Groups

Products

• CA Client Automation
• CA Client Automation - IT Client Manager

Issue/Introduction

How to see with a SQL Query the hierarchy of all ITCM Groups ?

This is to avoid to expand all groups in DSM Explorer which could take time.

Environment

Client Automation - All Versions

Resolution

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 :

Additional Information

Following SQL Query returns the group hierarchy with additionnal information on the group :

 

Origin : Domain or Enterprise

Type : Static or Dynamic Group with evaluation Engine and period

NBComputers : Number of computers inside the group

 

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