Manually Remove A Connector From SOI

Products

• CA Service Operations Insight (SOI)

Issue/Introduction

How to manually remove a connector from SOI when you can't use the Remove option in the Administration tab or use the SOITOOLBOX utility.

 

Environment

SOI 4.2 

Cause

An example of scenario where the remove option is greyed out and you need to manually remove the connector manually is the following: 

SCOM connector running with a bad policy file that included the InstanceName value for all classes including ComputerSystsm, VirtualSystem, etc. SOI will use the InstanceName field to perform the correlation if it exist.
As a result the CIs from SCOM are not being correlated with existing CIs from other connectors like UIM, Spectrum. This created duplicate or separate SCOM CIs. 

Resolution

We need to collect the below information for the connector need to remove:
c_mdrprodinstance
ConnectorID 

 

select distinct c_mdrprodinstance from ca_ssa_ci_detail where c_mdrproduct = '<connector name>'

You will need the result from the above query for later queries.

Run the below query to get the ConnectorID you are going to remove

select * from ConnectorConfiguration

For this example, we will remove the SCOM connector and use ConnectorID 50 and P006P0153:16161 as the c_mdrprodinstance
Replace CA:00031 in the example with the connector you are going to remove like CA:00050 for UIM.

1) Stop all SOI services (Manager, UI, Connector) 

2) Ran the following SQL queries... 

if exists(select 1 from CIStaging s where s.ciid is not null AND s.ConnectorID = 50) begin 
update ci set USMNotebookID = null, USMSheetID = null where ciid in (select ciid from CIStaging where ConnectorID = 50) 
end 

delete from ca_ssa_ci_detail where c_mdrproduct = 'CA:00031' and c_mdrprodinstance = 'P006P0153:16161' 

delete from AlertHistory where AlertID in (select a.AlertID from AlertHistory a, Alerts b where a.AlertID = b.AlertID and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc) and b.ConnectorID != '0') 

delete from AlertImpact where AlertID in (select a.AlertID from AlertImpact a, Alerts b where a.AlertID = b.AlertID and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc) and b.ConnectorID != '0') 

delete from Alerts where ConnectorID = 50 

delete from AlertQueueAssignments where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageQualityAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageRiskAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from ConnectorConfiguration where ConnectorID = 50 

3) Restart all SOI services. 
 

Additional Information

Additional Details: 

Should "c_mdrproduct" be changed in the command to what we use?  ---> YES

Should  "c_mdrprodinstance" be changed? --> YES

And "ConnectorID" must be changed too? --> YES

 

 

Example:

-- First you need to locate your ConnectorID from ConnectorConfiguration table: 

select * from ConnectorConfiguration 

Below the result from my lab. 

Here let's say I'm deleting "ConnectorID = 5"

 

-- So in the above query you should determine and take note of the connector you need to delete. For example "connectorid = 5"

--Then locate your " c_mdrprodinstance" from the ca_ssa_ci_detail table run the query below:

select  distinct c_mdrprodinstance,c_mdrproduct from ca_ssa_ci_detail

 

--from the above output Take note of the "c_mdrprodinstance" you need to delete

--So - in the query below use the CONNECTOR ID you need to delete, in red: (in the example below is connectorID 5)

if exists(select 1 from CIStaging s where s.ciid is not null AND s.ConnectorID = 5) begin 
update ci set USMNotebookID = null, USMSheetID = null where ciid in (select ciid from CIStaging where ConnectorID = 5) 
end 

 

Onto the query below you have to replace the red part, with your previously noted  "c_mdrproduct and" "c_mdrprodinstance" 

delete from ca_ssa_ci_detail where c_mdrproduct = 'CA:00050' and c_mdrprodinstance = '10xxxxxx.44' 

 

-- Do the same for the rest of the queries where it is required to replace a value:

 

delete from AlertHistory where AlertID in (select a.AlertID from AlertHistory a, Alerts b where a.AlertID = b.AlertID and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc) and b.ConnectorID != '0') 

delete from AlertImpact where AlertID in (select a.AlertID from AlertImpact a, Alerts b where a.AlertID = b.AlertID and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc) and b.ConnectorID != '0') 

delete from Alerts where ConnectorID = 5 

delete from AlertQueueAssignments where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageQualityAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from OutageRiskAlerts where AlertID not in (select a.AlertID from Alerts a) 

delete from ConnectorConfiguration where ConnectorID = 5