Title: How to permanently remove some or all UPM signatures from the ITCM Asset Management signature file (W000....xml and .zml files).
Description:
Over the years the signature file for Client Automation/ITCM has grown to over 150mb.
Due to this, the time required to download this file down from their Domain Manager(s) (DM) to the Scalability Servers (SS) and then on to the agents has increased signficiantly.
Signature files are copied down during the 'Validating Scalability Server' portion of the ITCM Engine collect task for each of the above machines.
If a customer does not have UPM or does not make use of these signatures, a very large portion of that file download is unnecessary.
Even if a customer is regularly using Patch Manager (UPM/PM), any patch signature over 6-12 months would have little value since they have most likely applied newer patches since then.
Solution:
CA is working on a way to automatically remove these signatures, but until that happens, customers may want to run the following SQL Queries.
******** In all cases customer should make sure they have a proper MDB backup before running any SQL commands. ********
Login into SQL using either a Windows/SQL admin account or the built in ITCM 'ca_itrm' user and password.
IF YOU DON'T USE UPM AT ALL:
Run the following SQL commands:
use mdb update ca_software_signature set is_active = 0 where signature_data like '%upm%'
This will not delete the UPM signatures, but just mark them as inactive and not add them to the signature file.
IF YOU DO USE UPM or want to retain SOME active signatures:
You will need to run the following SQL commands for each calendar year of UPM Rollup patch signatures you want to remove
(remove means from the signature files, not the MDB database; the queries will just set the item(s) to inactivate in the table, not delete them).
CA started creating UPM Patch Rollups in 2005, so there will be patches in the MDB from October 2005- Present
NOTE: The dates in UPM are recorded in the format YYMM:
This first commands removes all signatures for Year (YY) and month (MM) 1-9 (months Jan-Sept)
use mdb update ca_software_signature set is_active = 0 where signature_data like '%upm%YY0%'
This command removes all signatures for Year YY and month 10-12 (months Oct-Dec)
use mdb update ca_software_signature set is_active = 0 where signature_data like '%upm%YY1%'
NOTE: This means you need two queries or a two-part query to delete any one year. Shorting the search query will have NEGATIVE effects and should be avoided.
EXAMPLE: To deactivate all UPM signatures from the year 2012, you could run the following query:
select signature_data from ca_software_signature where signature_data like '%upm%121%' or signature_data like '%upm%120%'
EXAMPLE: I want to remove all signature data from 2008, October-December
If you specify 081, it will remove 0810, 0811 and 0812
If you specify 080, it will remove 0801, 0802, 0803,0804, 0805, 0806, 0807, 0808, and 0809
If you only want a certain Year and month then you can select for Example 0809 (Sept 2008)
This commands removes all signatures for Year 2008 and month Oct-Dec
use mdb update ca_software_signature set is_active = 0 where signature_data like '%upm%081%'
To remove only one month like December 2013:
use mdb update ca_software_signature set is_active = 0 where signature_data like '%upm%1312%'
AFTER RUNNING ONE OF THE TWO ABOVE OPTIONS:
Wait until your collect engine has run thru at least one collect process and then run a collect on an agent pointing to that DM or SS.
ON AN AGENT:
Navigate to ca\dsm\agent\units\00000001\uam and check the number and size of the w00000***.xml file (should be about 160mb)
caf start amagent -args /rescan_inventory /rescan_software /collect
The signature file size with UPM patches included have gotten bigger each year, so removing only 2005 might only shrink the files a few MB, where removing 2013 patches can shrink the signature file 30-40MB