How to purge obsolete Heuristic software definitions

Document ID : KB000103820
Last Modified Date : 31/10/2018
Show Technical Document Details
Introduction:

- Heuristic software discovery is a bottom-up type of discovery, in which new software definitions are created dynamically in the database, as agents report this type of discovered software.

- Over time, as agents are registered and deleted from the database, it is possible to have a build-up of heuristic software definitions in the database such that no agent(s) are referencing them any longer.

- This build-up of heuristic definitions can eventually lead to DSM Explorer GUI performance issues, as well as general database performance degradation.

- This document provides a procedure for identifying and removing these orphaned/obsolete heuristic software definitions.

Environment:
Client Automation (ITCM) -- any version.
Instructions:
Using SQL Management Studio, run the following series of DELETE statements, which will purge any heuristic software definition that is not referenced by any agent...
-- Do a backup of tables
DECLARE @date_time VARCHAR(50),@sql VARCHAR(255)
SET @date_time=convert(varchar(255),GETDATE(),121)
SET @date_time=REPLACE(@date_time,' ','___')
SET @date_time=REPLACE(@date_time,'-','_')
SET @date_time=REPLACE(@date_time,':','_')
SET @date_time=LEFT(@date_time,LEN(@date_time)-4)

SET @sql='select * into ca_category_member_BACKUP_'+@date_time+' from ca_category_member'
exec(@sql)
SET @sql='select * into ca_software_signature_BACKUP_'+@date_time+' from ca_software_signature'
exec(@sql)
SET @sql='select * into ca_link_sw_def_BACKUP_'+@date_time+' from ca_link_sw_def'
exec(@sql)
SET @sql='select * into ca_software_def_BACKUP_'+@date_time+' from ca_software_def'
exec(@sql)
GO


-- Purge orphaned discovered software, not linked to any agent.
delete from ca_discovered_software where asset_source_uuid not in (select object_uuid from ca_agent)
go

-- Create temp table with heuristic release sw_def_uuid to delete
IF OBJECT_ID ('tempdb..#temp', 'U') IS NOT NULL DROP TABLE #TEMP
SELECT sw_def_uuid into #temp FROM ca_software_def WHERE source_type_id=3 and software_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software)
GO

-- Purge related signatures.
delete from ca_software_signature where sw_def_uuid in (select sw_def_uuid from #temp)
go

-- Purge related linkages.
delete from ca_link_sw_def where secondary_sw_def_uuid in (select sw_def_uuid from #temp) OR primary_sw_def_uuid IN (select sw_def_uuid from #temp)
go

-- add in temp table heuristic product sw_def_uuid to delete
INSERT INTO #temp
SELECT sw_def_uuid FROM ca_software_def
WHERE software_type_id=8 and source_type_id=3 and sw_def_uuid not in 
(select primary_sw_def_uuid from ca_link_sw_def UNION select secondary_sw_def_uuid from ca_link_sw_def UNION select sw_def_uuid from ca_discovered_software)
GO

-- Unlink orphaned definitions from any categories.
delete from ca_category_member where sw_def_uuid in (select sw_def_uuid from #temp)
go


-- Batch the purge of obsolete data, to avoid database locks.
declare @ROWCNT int ,@NUMROWS int, @TOPNUM int, @message nvarchar(50)
set @NUMROWS = 0
set @TOPNUM = 100
while 1 = 1
begin
    begin transaction;
    delete top (@TOPNUM) from ca_software_def where sw_def_uuid in (select sw_def_uuid from #temp)
    set @NUMROWS = @@ROWCOUNT;
    commit transaction;
    if @NUMROWS = 0 break;
end
go
IF OBJECT_ID ('tempdb..#temp', 'U') IS NOT NULL DROP TABLE #TEMP
go

Additional Information:
In case of problem, backup tables could be used to restore the deleted data.
Replace table names in bold by correct name of backup tables.

You could find the suffix of backup tables with this query :

select name from sysobjects where name like 'ca_software_def_BACKUP_%' and xtype='U' order by crdate


INSERT INTO ca_software_def
SELECT sw_def_uuid,name,sw_version_label,language,creation_user,creation_date,last_update_user,last_update_date,version_number,sw_version_number,replication_flag
,software_type_id,source_type_id,description,NULL,manufacturer_uuid,bit_support_id,chip_set_id,domain_uuid,impact,severity,is_active,no_longer_available,family_sw_def_uuid
,lang_code,class_id,linear_sequence_number,filter_from_view,drcs_content_type_id,requires_review,unsealed
FROM ca_software_def_BACKUP_2018_10_31___14_51_07 WHERE sw_def_uuid not in (select sw_def_uuid from ca_software_def)
 
 
INSERT INTO ca_link_sw_def
SELECT last_update_date,version_number,secondary_sw_def_uuid,primary_sw_def_uuid,link_type_id,source_type_id,NULL
FROM ca_link_sw_def_BACKUP_2018_10_31___14_51_07 l1 WHERE NOT EXISTS 
(SELECT * FROM ca_link_sw_def l2 WHERE l1.primary_sw_def_uuid=l2.primary_sw_def_uuid and l1.secondary_sw_def_uuid=l2.secondary_sw_def_uuid)
 
 
INSERT INTO ca_software_signature
SELECT signature_uuid,sw_def_uuid,signature_data,version,signature_type,export_date,comment,is_active,content_version_number,creation_user,creation_date
,last_update_user,last_update_date,version_number, NULL,source_type_id,signature_os_group_id,domain_uuid,script,timeout,checksum
FROM ca_software_signature_BACKUP_2018_10_31___14_51_07 WHERE signature_uuid NOT IN (SELECT signature_uuid FROM ca_software_signature)
 
 
INSERT INTO ca_category_member
SELECT category_uuid,sw_def_uuid,last_update_date,version_number,source_type_id,category_domain_uuid,member_domain_uuid, NULL
FROM ca_category_member_BACKUP_2018_10_31___14_51_07 m1 WHERE NOT EXISTS
(SELECT * FROM ca_category_member m2 WHERE m1.category_uuid=m2.category_uuid and m1.sw_def_uuid=m2.sw_def_uuid)