Cannot insert duplicate key row in object 'ResourceFolder' with unique index

Products

• IT Management Suite

Issue/Introduction

Each time a full AD import is attempted, the error, "Cannot insert duplicate key row in object," is shown in the log files.

Cause

The cause of this problem is that there is a duplicate entry in one of the resource tables.
 
This can happen if machines are moved in Active Directory but the Synchronization Schedule has not moved them in Notification Server.  It can also happen if you are using virtual machines and any Discovery process (AD import, Network discovery, Resource Discovery) because virtual machines get listed as virtual machine not standard computer resources we create duplicates every time we do a discovery and the duplicate is put in the resource tree.  Causing the problem.

If this error occurs, then the NSE created by the import will end up failing to actually import into the database.

Resolution

If these are coming from duplicates caused by a discovery process please follow kb23871 also.

The following SQL query has fixed this problem by cleaning up the Resource Associations:

--/This displays all resources that are present in more than one resource folder.

 

SELECT vComputer.name,resourceFolder.resourceGuid, Count(*) as Occurances
    FROM resourceFolder JOIN vcomputer ON vComputer.guid = resourceFolder.resourceGuid
    GROUP BY vComputer.name, resourceFolder.resourceguid
    ORDER BY Occurances DESC, resourceGuid

 

--/This displays the name of those resource which have been found by the above query, based on the resource folder guid

 

Select name from vComputer where guid in (select resourceGuid from resourceFolder where resourceGuid = '4B7AE44C-051E-4C00-9B41-B9B8B04ACB29')

 

--/This displays the resource folder guids that are populated by the same resource

SELECT * FROM resourceFolder WHERE resourceGuid IN (SELECT resourceGuid
    FROM resourceFolder rf WHERE EXISTS (SELECT vComputer.name, resourceGuid, Count(*) AS Occurances
    FROM resourceFolder
    JOIN vComputer ON vComputer.guid = resourceFolder.resourceGuid WHERE rf.resourceGuid = resourceFolder.resourceGuid
        GROUP BY resourceFolder.resourceGuid, vComputer.Name HAVING Count(vComputer.name) > 1))

 

--/Deletes resource from resource folders
DELETE FROM resourceFolder WHERE resourceGuid IN (SELECT resourceGuid
    FROM resourceFolder rf WHERE EXISTS (SELECT vComputer.name, resourceGuid, Count(*) AS Occurances
    FROM resourceFolder
    JOIN vComputer ON vComputer.guid = resourceFolder.resourceGuid WHERE rf.resourceGuid = resourceFolder.resourceGuid
        GROUP BY resourceFolder.resourceGuid, vComputer.Name HAVING Count(vComputer.name) > 1))

 

--/Displays resource association

SELECT * FROM itemreference WHERE parentitemguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7'
    AND childitemguid IN (SELECT Guid FROM vcomputer WHERE guid IN
        (SELECT childitemguid FROM itemreference WHERE parentitemguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7')
    AND guid NOT IN
        (SELECT resourceGuid FROM resourcefolder WHERE parentfolderguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7'))

 

--/Deletes resources from resource association
DELETE FROM itemreference WHERE parentitemguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7'
AND childitemguid IN (SELECT Guid FROM vcomputer WHERE guid IN
    (SELECT childitemguid FROM itemreference WHERE parentitemguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7')
    AND guid NOT IN
        (SELECT resourceGuid FROM resourcefolder WHERE parentfolderguid = '9eff444f-83cc-47bf-acf9-c5d55733e4e7'))

There could be another problem in that there are child items that may have multiple references to parent objects. If this is the case then the query above may work for a while, but the problem could show up again at some later point. The following query takes care of this situation:

set nocount on
declare @guid uniqueidentifier
declare cur cursor for select Child from
    (
        select i.Guid as Child, ParentItemGuid from ItemReference
        join vItem i on i.Guid = ChildItemGuid
        join ItemResource r on r.Guid = ChildItemGuid 
        where Hint = 'folderchilditem'
    ) t
    group by Child
    having count(*) > 1

 

open cur
    fetch next from cur into @guid
    while @@fetch_status = 0
    begin
        delete from ItemReference
        where ChildItemGuid = @guid
        and ModifiedDate < (select max(ModifiedDate) from ItemReference where ChildItemGuid = @guid and Hint = 'folderchilditem')
        and Hint = 'folderchilditem'

 

        fetch next from cur into @guid
    end
close cur
deallocate cur
set nocount off

Note: If these are coming from duplicates caused by a discovery process please follow kb23871 also.

Start> Control Panel> Scheduled Tasks double click the task NS.Modify VirtualMachine Item Resource Type and change the schedule to run once for a date in the past so that it does not continue to cause the same problem.

Then run the following in SQL query Analyzer to change the class guid to a Standard Computer Resource not a virtual machine.
Always make sure you have a current copy of the database before making these changes. 

UPDATE    Item

SET              ClassGuid = '539626D8-A35A-47EB-8B4A-64D3DA110D01'

WHERE     (ClassGuid LIKE '1F54FC7C-6DFB-4EFE-AB2B-97194CDD75E8')

Applies To
Notification Server
Active Directory Import