Configuration Policies not inherited to group member

Products

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

Issue/Introduction

Some computers have not received the configuration policy applied to the group.

Following SQL Query shows the Group Name with the assigned Configuration Policy

SELECT o3.name 'Group Name', o1.name 'Policy Name'
FROM csm_object o1, csm_link l1, csm_link l2, csm_object o2, csm_object o3
WHERE o1.class=2000 AND o1.id=l1.child AND l1.parent=o2.id AND
l2.child=o2.id AND l2.parent=o3.id and o3.class=200
ORDER BY 2

 

And following SQL Query shows computers which are in a group with a configuration policy applied but which have not received the configuration policy :

IF OBJECT_ID ('tempdb.dbo.#TMP1','U') IS NOT NULL DROP TABLE #TMP1
IF OBJECT_ID ('tempdb.dbo.#TMP2','U') IS NOT NULL DROP TABLE #TMP2

SELECT o3.name 'Group Name', o1.name 'Policy Name',  a.agent_name 'Computer Name', p.value, CHARINDEX(UPPER(o1.name),UPPER(p.value)) 'Index_Pos',
o3.id 'Group_id',
o4.id 'Computer_id',
o5.id 'Job_id'
INTO #TMP1
FROM csm_object o1
LEFT JOIN csm_link l1 ON o1.id=l1.child
LEFT JOIN csm_object o2 ON l1.parent=o2.id
LEFT JOIN csm_link l2 ON l2.child=o2.id
LEFT JOIN csm_object o3 ON l2.parent=o3.id and o3.name<>'$allcomputer'
LEFT JOIN ca_group_def g ON UPPER(g.label)=UPPER(o3.name COLLATE SQL_Latin1_General_CP1_CS_AS)
LEFT JOIN ca_group_member m ON m.group_uuid=g.group_uuid
LEFT JOIN ca_agent a ON m.member_uuid=a.object_uuid
LEFT JOIN csm_object o4 ON UPPER(o4.name COLLATE SQL_Latin1_General_CP1_CS_AS)=UPPER(a.agent_name)
LEFT JOIN csm_link l3 ON l3.parent=o4.id
LEFT JOIN csm_object o5 ON l3.child=o5.id and o5.class=108
LEFT JOIN csm_property p ON p.object=o5.id and p.name='list_c_v21'
WHERE o1.class=2000 and o3.class=200 and o4.class in (102,200) and p.value is NOT NULL
ORDER BY 2, 3

SELECT Group_id, Computer_id, MAX(Index_Pos) 'max_index'
INTO #TMP2
FROM #TMP1 
GROUP BY Group_id, Computer_id

SELECT t1.[Group Name], t1.[Policy Name], t1.[Computer Name] FROM  #TMP1 t1, #TMP2 t2 WHERE t1.Group_id=t2.Group_id and t1.Computer_id=t2.Computer_id and t2.max_index=0
ORDER BY 1,3

If this query returns 1 or more rows then apply solution of this article

Environment

Client Automation - All Versions

Resolution

1- Execute following SQL Query :

IF OBJECT_ID ('tempdb.dbo.#TMP1','U') IS NOT NULL DROP TABLE #TMP1
IF OBJECT_ID ('tempdb.dbo.#TMP2','U') IS NOT NULL DROP TABLE #TMP2

SELECT o3.name 'Group Name', o1.name 'Policy Name',  a.agent_name 'Computer Name', p.value, CHARINDEX(UPPER(o1.name),UPPER(p.value)) 'Index_Pos',
o3.id 'Group_id',
o4.id 'Computer_id',
o5.id 'Job_id'
INTO #TMP1
FROM csm_object o1
LEFT JOIN csm_link l1 ON o1.id=l1.child
LEFT JOIN csm_object o2 ON l1.parent=o2.id
LEFT JOIN csm_link l2 ON l2.child=o2.id
LEFT JOIN csm_object o3 ON l2.parent=o3.id and o3.name<>'$allcomputer'
LEFT JOIN ca_group_def g ON UPPER(g.label)=UPPER(o3.name COLLATE SQL_Latin1_General_CP1_CS_AS)
LEFT JOIN ca_group_member m ON m.group_uuid=g.group_uuid
LEFT JOIN ca_agent a ON m.member_uuid=a.object_uuid
LEFT JOIN csm_object o4 ON UPPER(o4.name COLLATE SQL_Latin1_General_CP1_CS_AS)=UPPER(a.agent_name)
LEFT JOIN csm_link l3 ON l3.parent=o4.id
LEFT JOIN csm_object o5 ON l3.child=o5.id and o5.class=108
LEFT JOIN csm_property p ON p.object=o5.id and p.name='list_c_v21'
WHERE o1.class=2000 and o3.class=200 and o4.class in (102,200) and p.value is NOT NULL
ORDER BY 2, 3

SELECT Group_id, Computer_id, MAX(Index_Pos) 'max_index'
INTO #TMP2
FROM #TMP1 
GROUP BY Group_id, Computer_id

DELETE csm_link
FROM csm_link l, #TMP1 t1, #TMP2 t2 
WHERE t1.Group_id=t2.Group_id and t1.Computer_id=t2.Computer_id and (t2.max_index=0 or t2.max_index is NULL)
and l.parent =t1.Group_id and l.child=t1.Computer_id

 

2- Wait 1 hour and check if all the machines have received the configuration jobs