How to purge obsolete Heuristic software definitions

Document ID : KB000103820
Last Modified Date : 10/07/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...
-- 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

-- Unlink orphaned definitions from any categories.
delete from ca_category_member where sw_def_uuid in (select sw_def_uuid from ca_software_def where source_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 ca_software_def where source_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software))
go

-- Purge related linkages.
delete from ca_link_sw_def where secondary_sw_def_uuid in (select sw_def_uuid from ca_software_def where source_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software))
go
delete from ca_link_sw_def where primary_sw_def_uuid in (select sw_def_uuid from ca_software_def where source_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software))
go

-- Purge prop data, if any.
delete from ca_discovered_software_prop where dis_sw_uuid in (select sw_def_uuid from ca_software_def where source_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software))
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 source_type_id=3 and sw_def_uuid not in (select sw_def_uuid from ca_discovered_software);
    set @NUMROWS = @@ROWCOUNT;
    commit transaction;
    if @NUMROWS = 0 break;
end
go