Schedule this Query to run at least once a week to auto merge your duplicate computers (computers with the same GUID). This automatically takes the most recent data and merges the two records together. This is a great way to keep your environment clean.
DECLARE @MergeName nvarchar(400) DECLARE @MergeDomain nvarchar(400) DECLARE @FromGuid uniqueidentifier DECLARE @ToGuid uniqueidentifier DECLARE @PrimaryResource UNIQUEIDENTIFIER PRINT 'The following script will automatically merge resources as per the ''Merge computers with duplicate names'' report.' PRINT 'The merge automatically chooses the resource that has the most recent update.' DECLARE merge_cursor CURSOR FOR SELECT Ident.[Name], Ident.[Domain] FROM Inv_AeX_AC_Identification Ident INNER JOIN ( SELECT Ident1.[_ResourceGuid], Ident1.[Name], Ident1.[Domain] FROM Inv_AeX_AC_Identification Ident1 INNER JOIN Inv_AeX_AC_Identification Ident2 ON Ident2.[Name] = Ident1.[Name] AND Ident2.[Domain] = Ident1.[Domain] AND Ident2.[_id] != Ident1.[_id] INNER JOIN vComputerResource Ident3 ON Ident1.[_ResourceGuid] = Ident3.[Guid] GROUP BY Ident1.[_ResourceGuid], Ident1.[Name], Ident1.[Domain] ) udr ON Ident.[_ResourceGuid] = udr.[_ResourceGuid] GROUP BY Ident.[Name], Ident.[Domain] OPEN merge_cursor FETCH NEXT FROM merge_cursor INTO @MergeName, @MergeDomain WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Merging ' + @MergeName + '.' + @MergeDomain CREATE TABLE #DUPLICATE_NAME (Resource UNIQUEIDENTIFIER, [Primary] BIT, [Name] NVARCHAR(64), [Domain] NVARCHAR(64), LastUpdated DATETIME, [OS Name] NVARCHAR(64)) INSERT INTO #DUPLICATE_NAME SELECT ident1.[_ResourceGuid] AS [Resource], CAST (0 AS BIT) AS [Primary], ident1.[Name], ident1.[Domain], md.[ModifiedDate] AS [LastUpdated], ident1.[OS Name] FROM Inv_AeX_AC_Identification ident1 JOIN ( SELECT ident2.[Name], ident2.[Domain] FROM Inv_AeX_AC_Identification ident2 GROUP BY ident2.[Name], ident2.[Domain] HAVING COUNT (ident2.[Name]) > 1 AND ident2.[Name] = @MergeName AND ident2.[Domain] = @MergeDomain ) dr ON ident1.[Name] = dr.[Name] and ident1.[Domain] = dr.[Domain] LEFT OUTER JOIN ( SELECT DISTINCT rus.[ResourceGuid], rus.[ModifiedDate] FROM ResourceUpdateSummary rus JOIN ( SELECT rus2.[ResourceGuid], MAX( rus2.[ModifiedDate] ) AS ModifiedDate FROM ResourceUpdateSummary rus2 GROUP BY rus2.[ResourceGuid] ) td ON td.ResourceGuid = rus.ResourceGuid AND td.ModifiedDate = rus.ModifiedDate ) md ON ident1.[_ResourceGuid] = md.[ResourceGuid] SELECT TOP 1 @PrimaryResource = Resource FROM #DUPLICATE_NAME ORDER BY [LastUpdated] DESC UPDATE #DUPLICATE_NAME SET [Primary]=1 WHERE Resource = @PrimaryResource /*SELECT * FROM #DUPLICATE_NAME ORDER BY [LastUpdated] DESC*/ SELECT TOP 1 @ToGuid = Resource FROM #DUPLICATE_NAME where Name = @MergeName and Domain = @MergeDomain and [Primary] = '1' SELECT TOP 1 @FromGuid = Resource FROM #DUPLICATE_NAME where Name = @MergeName and Domain = @MergeDomain and [Primary] = '0' DROP TABLE #DUPLICATE_NAME exec spResourceMerge @FromGuid,@ToGuid FETCH NEXT FROM merge_cursor INTO @MergeName, @MergeDomain END CLOSE merge_cursor DEALLOCATE merge_cursor