JeremyDallas

PLEASE SEE STEVE_OAKES UPDATED POST FURTHER DOWN

Found this and thought it might be helpful...


Question:
How do I automatically Merge all Duplicate Computers found in the Merge Computers report-utility (located under the Configuration Tab, Server Settings, Notification Server Settings, Merge Computers, and Merge computers with duplicate names)?

Answer:
The following script will automatically merge all resouces found in the above mentioned report:


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]
&nbsp 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
&nbsp 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]
&nbsp td ON td.ResourceGuid = rus.ResourceGuid AND td.ModifiedDate = rus.ModifiedDate
&nbsp 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



Run the following script in SQL Query Analyzer pointing to the Altiris database

__________________
JEREMY SMITH