Hiding Outdated UPM Rollup Patches from Patch Manager thru SQL commands

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

Description:

CA started creating UPM patches in August 2005 and those Rollups are still showing in ITCM Patch Manager (UPM).

You can use filters to hide items from the UPM console searches based on different criteria, but this can be complex or intimidating process to some.

In SQL you can run a few Queries to hide patches from previous years and not see them in UPM anymore at all.

Solution:

To create the SQL queries do the following:

Logon to the ITCM SQL server using a Windows/SQL admin account or the built in ITCM 'ca_itrm' account and password.

******* A database backup should be performed before any MDB modifications are done *******

To hide a certain calendar years' patches , you just need to run the following command, where you replace YY with the last 2 digits of the Year.
This command will only hide the patched not delete them, so they can always be unhide by changing the patches back to is_active = 1

In UPM, years are represented with 2 digits and this is represented by the string 'YY' here:


 use mdb
 go
 update ca_install_package
 set is_active = 0
 where sw_def_uuid in (select sw_def_uuid from ca_software_def
 where name like '%Rollup vYY%')
 go

An example of the command you use to hide all UPM Rollups created in 2010:


 use mdb       
 go 
 update ca_install_package
 set is_active = 0
 where sw_def_uuid in (select sw_def_uuid from ca_software_def
 where name like '%Rollup v10%')
 go

After running the SQL scripts open the UPM web console.
Select the Patches Tab.
Change Filter By: to Patch Name.
Specify % v10% and you should only see one patch , which is not a Rollup