User account relationships retained after computer reinstallation

Products

• CA Client Automation - IT Client Manager

Issue/Introduction

When a computer is reinstalled in CA Client Automation using the New Installation (current)... option, user account relationships that existed before the reinstallation are often retained in the database.

This can lead to a large volume of obsolete records (e.g., over 900 rows) that do not reflect the current state of the machine.

Is there a way to delete the relationships to the user accounts that existed before a reinstallation from the database?

Environment

CA Client Automation 14.x

Cause

This issue occurs because the OSIM reinstallation process does not automatically purge existing user-to-device relationships from the ITCM database, causing them to persist alongside new records.

Resolution

You can remove these obsolete links using database queries against the ITCM database via SQL Management Studio or sqlcmd from the Domain Manager.

Note: Always ensure you have a full backup of the ITCM database before running any deletion queries.

Step 1: Identify obsolete links Run the following query to identify computer-to-user links created before the most recent reinstallation:

IF OBJECT_ID ('tempdb.dbo.#TMP1','U') IS NOT NULL DROP TABLE #TMP1
SELECT o.uuid, MAX(CAST(LEFT(p2.value,10) AS INT)) 'OSIM_Reinstall_Date'
INTO #TMP1
FROM csm_object o
INNER JOIN csm_link l on l.parent=o.id
INNER JOIN csm_object o2 on l.child=o2.id and o2.class=1004
INNER JOIN csm_property p on p.object=o2.id and p.name='configstate' and p.value=1000
INNER JOIN csm_property p2 on p2.object=o2.id and p2.name='configstatetime'
WHERE o.class=102
GROUP BY o.uuid

SELECT h.label 'Computer Name', u.label 'User Name',
dateadd ( ss, l.last_update_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Last Update Link Computer-User Date',
dateadd ( ss, h.creation_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Computer Creation Date',
dateadd(ss, t.OSIM_Reinstall_Date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'OSIM Reinstall Date'
from ca_discovered_hardware h
INNER JOIN ca_link_dis_hw_user l ON l.dis_hw_uuid=h.dis_hw_uuid
INNER JOIN ca_discovered_user u ON l.user_uuid=u.user_uuid
LEFT JOIN #TMP1 t ON t.uuid=h.dis_hw_uuid
WHERE t.OSIM_Reinstall_Date IS NOT NULL and t.OSIM_Reinstall_Date-l.last_update_date > 3600*24
order by 1

Step 2: Backup the target table and delete records If the results from Step 1 are correct, proceed with backing up the specific table and deleting the obsolete links:

This command will make changes to your system. Review it carefully before running.

-- Do a backup of table ca_link_dis_hw_user
SELECT * INTO ca_link_dis_hw_user_backup FROM ca_link_dis_hw_user

IF OBJECT_ID ('tempdb.dbo.#TMP1','U') IS NOT NULL DROP TABLE #TMP1
SELECT o.uuid, MAX(CAST(LEFT(p2.value,10) AS INT)) 'OSIM_Reinstall_Date'
INTO #TMP1
FROM csm_object o
INNER JOIN csm_link l on l.parent=o.id
INNER JOIN csm_object o2 on l.child=o2.id and o2.class=1004
INNER JOIN csm_property p on p.object=o2.id and p.name='configstate' and p.value=1000
INNER JOIN csm_property p2 on p2.object=o2.id and p2.name='configstatetime'
WHERE o.class=102
GROUP BY o.uuid

DELETE FROM ca_link_dis_hw_user WHERE link_dis_hw_user_uuid in
(SELECT l.link_dis_hw_user_uuid
FROM ca_discovered_hardware h
INNER JOIN ca_link_dis_hw_user l ON l.dis_hw_uuid=h.dis_hw_uuid
INNER JOIN ca_discovered_user u ON l.user_uuid=u.user_uuid
LEFT JOIN #TMP1 t ON t.uuid=h.dis_hw_uuid
WHERE t.OSIM_Reinstall_Date IS NOT NULL and t.OSIM_Reinstall_Date-l.last_update_date > 3600*24)