Hi @cronventis,
The fix will only prevent new duplicates from being created. Mainly this is because I cannot ensure that the first vulnerability is always the properly assessed vulnerability. For now, the best option will be to run a SQL query directly against SQL Server ProGet database after you upgrade to 6.0.5.
I have created a SQL query that will delete all the duplicates excluding the first vulnerability that was added to ProGet. If that criteria works for you, this query should be good enough.
BEGIN TRANSACTION
DELETE FROM [Vulnerabilities]
WHERE [Vulnerability_Id] in (
SELECT v.[Vulnerability_Id]
FROM [Vulnerabilities] v
INNER JOIN (
SELECT [External_Id]
,[FeedType_Name]
,[VulnerabilitySource_Id]
,COUNT([External_Id]) as [NumberOfDuplicates]
,MIN([Vulnerability_Id]) as [FirstVulnerability]
,MAX([Vulnerability_Id]) as [LastVulnerability]
FROM [Vulnerabilities_Extended]
GROUP BY External_Id, FeedType_Name, VulnerabilitySource_Id
HAVING count(External_Id) > 1
) duplicates on v.External_Id = duplicates.External_Id
WHERE v.Vulnerability_Id != duplicates.[FirstVulnerability]
)
ROLLBACK
Currently, I have the script set to rollback at the end (meaning it won't actually delete the duplicates). If this works for you, you can simply change ROLLBACK to COMMIT and rerun the query and it will remove the duplicates.
Please let me know if you have any questions!
Thanks,
Rich
