How to delete the old patches in CA Patch Manager (UPM)?

Document ID : KB000030163
Last Modified Date : 14/02/2018
Show Technical Document Details

How to delete the old patches in CA Patch Manager (UPM)


Description:
When the CA Patch Manager console is launched, it takes long time to open. Also, it takes a long time to load the data while navigating within the dashboard tab.

Root Cause:
if there are a large number of patches in the 'pending user acceptance' phase, it takes a long time to retrieve the results.

Solution:
The solution is to delete the old data pertaining to full rollups while keeping the last three months full rollups.

Caution:
The sw_def_uuid table can be used to perform the deletions. It is very important to first check the uuids obtained to ensure that they fall within the set of patches to be deleted. In addition, it is a must to change the last_update_date < 1416038491 condition to delete the old patches data.

It is recommended to delete the patches which are older than three months.

(You can use http://www.epochconverter.com/ to convert human timestamp to unix timestamp.)

 

Following is a short script to do a batch deletion of old patch data.

---------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @uuid varchar(50)
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR

SELECT sw_def_uuid FROM ca_software_def WHERE name LIKE '%win%Rollup%' AND last_update_date < 1416038491

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @uuid

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE ca_install_package SET is_active=0 WHERE install_pkg_uuid IN (select install_pkg_uuid from ca_install_package where sw_def_uuid=@uuid)
UPDATE ca_install_step SET is_active=0 WHERE install_pkg_uuid IN (select install_pkg_uuid from ca_install_package where sw_def_uuid=@uuid)
UPDATE ca_software_def SET is_active=0 WHERE sw_def_uuid=@uuid

FETCH NEXT FROM @MyCursor
INTO @uuid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
---------------------------------------------------------------------------------------------------------------------------------------------------

Steps to run the above query:

  1. Login to SQL Server Management Studio with the ca_itrm user account.
  2. Open a new query window and execute the above cursor code against the MDB.

Note: It is advisable to take a full backup of the MDB before performing the operation above.