Database Cleanup: Orphaned Discovered Software Records caused by Coracleanup

Document ID : KB000045390
Last Modified Date : 02/03/2018
Show Technical Document Details
Issue:
This document describes how to identify and cleanup orphaned discovered software records in the Client Automation (ITCM) mdb database.  Orphaned software is characterized as records in the discovered software table (ca_discovered_software), that does not match/correspond with any agent record in the agent table (ca_agent).
 
To detect if your database has orphaned discovered software records, you can run the following SQL query against the database:
select count(*) from ca_discovered_software where asset_source_uuid not in (select object_uuid from ca_agent)
 
If the count returned is greater than 0, this indicates you have orphaned records that require cleanup.
Environment:
Client Automation (ITCM) -- any version.
Cause:
This issue is caused by running the Coracleanup utility which disables two foreign key constraints in the MDB, and never re-enables them:
 
Coracleanup runs:
ALTER TABLE ca_discovered_software NOCHECK CONSTRAINT [$ca_di_r000002c700000000]
ALTER TABLE ca_discovered_hardware NOCHECK CONSTRAINT [$ca_di_r000002ac00000000]
 
The first statement disables foreign key constraint ($ca_di_r000002c700000000), which protects the ca_discovered_software table from receiving new records that do not properly match with a registered agent in the database.  With the constraint check disabled, the engine is allowed to insert any discovered software data not properly matching with any asset into the database.
 
The second statement disables foreign key constraint ($ca_di_r000002ac00000000), which protects the ca_discovered_hardware table from receiving new records that do not properly match with a CORA registration in the database.  With the constraint check disabled, the engine is allowed to register/insert a new computer record without first ensuring the computer is properly registered with CORA.
 
To verify if either of these two constraints are disabled in your MDB database, you can run the following SQL query:
select * from sys.foreign_keys where is_disabled=1
Resolution:
Firstly, the database foreign key constraints disabled by the Coracleanup utility need to be enabled.  Execute the following against the mdb database for Client Automation:
 
ALTER TABLE ca_discovered_software CHECK CONSTRAINT [$ca_di_r000002c700000000]
ALTER TABLE ca_discovered_hardware CHECK CONSTRAINT [$ca_di_r000002ac00000000]
 
Next, to cleanup the orphaned discovered software records which exist, execute the following against the mdb database for Client Automation:
 
delete from ca_discovered_software where asset_source_uuid not in (select object_uuid from ca_agent)
Additional Information:
Note related technical document, KB000072533, pertaining to performance of the coracleanup utility.  The related document can be found at: