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

Products

• CA Client Automation - Asset Management
• CA Client Automation - IT Client Manager
• CA Client Automation
• CA Client Automation - Remote Control
• CA Client Automation - Asset Intelligence
• CA Client Automation - Desktop Migration Manager
• CA Client Automation - Patch Manager

Issue/Introduction

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.

Environment

Client Automation - All supported versions.

Cause

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

Resolution

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.