SQL Query to Auto Merge Computers

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