The SAMStore CI table has stale data

Products

• CA Service Operations Insight (SOI)

Issue/Introduction

There are only a few CI's modeled in Services. If you look at the SAMStore CI table you will see over 10k entries. Only modeled CI's that are part of a service should be in the CI table.

Environment

Release:
Component: SOIMGR

Resolution

1) Do the following to clean up the CI table:

• Shut down SOI Services
• Backup the 'SAMStore' database

2) Run this query to set the CIID to NULL for CI's in the 'CIStaging' table:

UPDATE CIStaging SET CIID=NULL WHERE CIID is not null and CIID not in (select distinct ANodeCIID from CIRelationship union select      distinct BNodeCIID from CIRelationship union select distinct ServiceCIID from CIRelationship)

3) Now run this query to delete the invalid CI's in the CI table:

delete from CI where CIID not in (select distinct ANodeCIID from CIRelationship union select distinct BNodeCIID from CIRelationship union select distinct ServiceCIID from CIRelationship) and ClassID not in (65535, 65533)

4) Restart SOI Services