Violation of UNIQUE KEY constraint during Run User Loading Procedures

Products

• Information Centric Analytics

Issue/Introduction

The RiskFabric Processing job step Run User Loading Procedures (step 5) is failing and logging the following error:

Error=Violation of UNIQUE KEY constraint 'UK_LDC_IOC'. Cannot insert duplicate key in object 'dbo.LDW_IOC'. The duplicate key value is (<LinkedServerID>, <IOCSourceIndicatorID>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>).

Environment

Release : 6.x

Component : Integration Wizard

Cause

The key UK_LDC_IOC is a composite of the following:

• [LinkedServerID] ASC,
• [IOCSourceIndicatorID] ASC,
• [IOCSourceActorID] ASC,
• [IOCSourceCampaignID] ASC,
• [IOCSourceThreatBulletinID] ASC,
• [IOCSourceTTPID] ASC,
• [IOCSourceVulnerabilityID] ASC

This error is caused by events in the source system having NULL values for all columns but the LinkedServerID and the IOCSourceIndicatorID, which is concatenated with some other value or otherwise not unique.

Resolution

Correcting the concatenation on IOCSourceIndicatorID to ensure these values are unique will resolve this issue. If concatenation or any other transformation is being performed in the source system, it should be corrected there. Otherwise, the integration's import rule mapping can be modified to apply a formula to this field to ensure unique IDs are generated for each record.