Qualys processing job MERGE failure

Products

• Information Centric Analytics

Issue/Introduction

The Qualys processing job fails and logs the following error:

[1:ERROR] VulnerabilityKnowledgeBase.StageToFinal() System.Data.SqlClient.SqlException (0x80131904): The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Environment

Release : 6.x

Component : Qualys Import Utility

Resolution

The following workaround is available but requires modification of the stored procedure QualysGuardDW.dbo.spVulnerabilityKnowledgeBaseInsert.

In spVulnerabilityKnowledgeBaseInsert, locate the following section of code beginning around line 178:

merge into [dbo].[VulnerabilityVendorReference] tgt
	using (select * from [dbo].[stg_VulnerabilityBugtraq]) src
	on (tgt.[QID] = src.[QID] and tgt.[Name] = src.[ID] and tgt.[URL] = src.[URL])
	when matched then
		update set tgt.[Type] = 'Bugtraq'
	when not matched then
		insert ([QID],[Name],[Type],[URL]) values (src.[QID],src.[ID],'Bugtraq',src.[URL]);

Replace this with the following code (a DISTINCT is added to the first SELECT statement):

merge into [dbo].[VulnerabilityVendorReference] tgt
	using (select distinct * from [dbo].[stg_VulnerabilityBugtraq]) src
	on (tgt.[QID] = src.[QID] and tgt.[Name] = src.[ID] and tgt.[URL] = src.[URL])
	when matched then
		update set tgt.[Type] = 'Bugtraq'
	when not matched then
		insert ([QID],[Name],[Type],[URL]) values (src.[QID],src.[ID],'Bugtraq',src.[URL]);