Examples on how to check the data integrity in CA Service Desk Manager database

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

Introduction: 

There are times when we need to find out dangling references in Service Desk. In other words, check the data integrity. 

The following document provides some queries which may help identify such points of failures.

Environment:  

CA Service Desk Manager (SDM) R12.x, R14.1

Microsoft SQL Server

Oracle

Instructions: 

These are examples of some SELECT statements that can be used to validate this point, with an explanation on what each one checks:

 Retrieves the number of lines from USP_Contact table which do not have a match 
 in CA_Contact table
SELECT count(*) FROM usp_contact WHERE contact_uuid NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of lines from the Activity Log table which do not have a match
 in Call_Req table
SELECT count(*) FROM act_log WHERE call_req_id NOT IN (SELECT persid FROM Call_Req)
 Retrieves the number of Attachments which have a Read Group set which is not in the Group table.SELECT count(*) from attmnt WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of Attachments which have a Write Group set which is not in the Group table. SELECT count(*) from attmnt WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of Attachment Folders which have a Read Group set which is not in the Group table.SELECT count(*) from attmnt_folder WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of Attachment Folders which have a Write Group set which is not in the Group table. SELECT count(*) from attmnt_folder WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of lines from CA_Owned_Resource table which don't have
 an owner
SELECT count(*) FROM ca_owned_resource WHERE own_resource_uuid NOT IN (SELECT affected_rc FROM call_req)
 Retrieves the number of tickets (Incident/Problem/Request) which have a Change Order set where this Change Order does not exist.SELECT count(*) FROM call_req WHERE change NOT IN (SELECT id FROM chg)
 Retrieves the number of Change Requests which do not have a correspondent  
 ticket (Requests/Incidents/Problems). 
SELECT count(*) FROM chg WHERE id NOT IN (SELECT change FROM call_req)
 Retrieves the number of tickets (Incident/Problem/Request) which do not have a Change Order
 associated to. 
SELECT count(*) FROM call_req WHERE caused_by_chg NOT IN (SELECT id FROM chg)
 Retrieves the number of Change Requests which does not have a Customer set. SELECT count(*) FROM chg WHERE id NOT IN (SELECT caused_by_chg FROM call_req)
 Retrieves the number of tickets (Incident/Problem/Request) which have a Customer which is not part of
 the CA Contact table.
SELECT count(*) FROM call_req WHERE customer NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of tickets (Incident/Problem/Request) which log_agent is not  in the CA Contact
 table. 
SELECT count(*) FROM call_req WHERE log_agent NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of tickets (Incident/Problem/Request) which are not a parent  ticket.SELECT count(*) FROM call_req WHERE parent NOT IN (SELECT persid FROM call_req)
 Retrieves the number of tickets  (Incident/Problem/Request) which do not have a parent ticket.SELECT count(*) FROM call_req WHERE persid NOT IN (SELECT parent FROM call_req)
 Retrieves the number of tickets (Incident/Problem/Request) which have a Root cause set which is  not registered of the Root cause table. SELECT count(*) FROM rootcause WHERE id NOT IN (SELECT rootcause FROM call_req)
 Retrieves the number of Root causes which are not in use by tickets (Incident/Problem/Request).SELECT count(*) FROM rootcause WHERE id NOT IN (SELECT rootcause FROM call_req)
 Retrieves the number of KDs in the KD Index table which have an Author set which is not in the
 CA Contact table.
SELECT count(*) FROM O_INDEXES WHERE author_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the KD Index table which have an Owner set which is not in the
 CA Contact table.
SELECT count(*) FROM O_INDEXES WHERE owner_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the KD Index table which have an Expert set which is not in the
 CA Contact table.
SELECT count(*) FROM O_INDEXES WHERE subject_expert_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the KD Index table which have a Write Group set which is not in the
 Group table.
SELECT count(*) from o_indexes WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of KDs in the Document table which have an Assignee set which is not in the
 CA Contact table. 
SELECT count(*) FROM SKELETONS WHERE assignee_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the Document table which have an Author set which is not in the
 CA Contact table.
SELECT count(*) FROM SKELETONS WHERE author_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the Document table which have an Initiator set which is not in the
 CA Contact table. 
SELECT count(*) FROM SKELETONS WHERE initiator_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the Document table which have an Owner set which is not in the
 CA Contact table. 
SELECT count(*) FROM SKELETONS WHERE owner_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the Document table which have a Read Group set which is not in the
 Groups table.
SELECT count(*) FROM SKELETONS WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
 Retrieves the number of KDs in the Document table which have an Asset assigned to which is not part of
 the CA Owned Resource table. 
SELECT count(*) FROM SKELETONS WHERE sd_asset_ID NOT IN (SELECT own_resource_uuid FROM ca_owned_resource)
 Retrieves the number of KDs in the Document table which have a Product set which is not part of the
 Product table. 
SELECT count(*) FROM SKELETONS WHERE sd_product_id NOT IN (SELECT id FROM product)
 Retrieves the number of existent Products which are not referenced by the Documents table.SELECT count(*) FROM product WHERE persid NOT IN (SELECT sd_product_id FROM SKELETONS)
 Retrieves the number of KDs in the Document table which have an Expert set which is not in the
 CA Contact table. 
SELECT count(*) FROM SKELETONS WHERE subject_expert_id NOT IN (SELECT contact_uuid FROM ca_contact)
 Retrieves the number of KDs in the Document table which have a Write Group set which is not in the
 Group table.
SELECT count(*) FROM SKELETONS WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
 

Notes:

  • You can run these SELECT statements in SQL Management Studio or via SQL Plus.
  • They all should return a count of zero as result.
  • Otherwise, this indicates that there is a referential data integrity issue (dangling references). You could raise a Support ticket for additional help in getting this cleared up.
  • Refer to the Additional Information section of this document for the list of queries. 

 

Additional Information:

1. For information about tables and reference fields, please consult: 

Data Element Dictionary

Objects and Attributes

2. It is important to note that this is NOT an exhaustive check on the referential integrity of the MDB, but this does indicate a few items that every customer should, at a minimum, address what was found by this check and that a full referential integrity should be performed on the MDB.

3. You can get the complete list of queries to be used by the DBA below:

SELECT count(*) FROM usp_contact WHERE contact_uuid NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM act_log WHERE call_req_id NOT IN (SELECT persid FROM Call_Req)
SELECT count(*) from attmnt WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) from attmnt WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) from attmnt_folder WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) from attmnt_folder WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) FROM ca_owned_resource WHERE own_resource_uuid NOT IN (SELECT affected_rc FROM call_req)
SELECT count(*) FROM call_req WHERE change NOT IN (SELECT id FROM chg)
SELECT count(*) FROM chg WHERE id NOT IN (SELECT change FROM call_req)
SELECT count(*) FROM call_req WHERE caused_by_chg NOT IN (SELECT id FROM chg)
SELECT count(*) FROM chg WHERE id NOT IN (SELECT caused_by_chg FROM call_req)
SELECT count(*) FROM call_req WHERE customer NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM call_req WHERE log_agent NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM call_req WHERE parent NOT IN (SELECT persid FROM call_req)
SELECT count(*) FROM call_req WHERE persid NOT IN (SELECT parent FROM call_req)
SELECT count(*) FROM rootcause WHERE id NOT IN (SELECT rootcause FROM call_req)
SELECT count(*) FROM rootcause WHERE id NOT IN (SELECT rootcause FROM call_req)
SELECT count(*) FROM O_INDEXES WHERE author_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM O_INDEXES WHERE owner_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM O_INDEXES WHERE subject_expert_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) from o_indexes WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) FROM SKELETONS WHERE assignee_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM SKELETONS WHERE author_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM SKELETONS WHERE initiator_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM SKELETONS WHERE owner_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM SKELETONS WHERE (read_pgroup <> '0' and read_pgroup NOT IN (SELECT id FROM p_groups))
SELECT count(*) FROM SKELETONS WHERE sd_asset_ID NOT IN (SELECT own_resource_uuid FROM ca_owned_resource)
SELECT count(*) FROM SKELETONS WHERE sd_product_id NOT IN (SELECT id FROM product)
SELECT count(*) FROM product WHERE persid NOT IN (SELECT sd_product_id FROM SKELETONS)
SELECT count(*) FROM SKELETONS WHERE subject_expert_id NOT IN (SELECT contact_uuid FROM ca_contact)
SELECT count(*) FROM SKELETONS WHERE (write_pgroup <> '0' and write_pgroup NOT IN (SELECT id FROM p_groups))