Quote:
|
Originally Posted by Jeremy_Dallas
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 resources found in the above mentioned report:
Run the following script in SQL Query Analyzer pointing to the Altiris database
|
the problem with this merge script is it only lets you merge computers with that considered duplicates by name and domain. If you have a dual domain environment - ugh
I have it- then you need to merge on the name only.
I created the following variation after getting frustrated with Altiris support saying that merging will be better in the next version of asset management 6.2. The following merge query has a restriction based on computer name -'IS%' with IS being the beginning of all correctly name computers
DECLARE @MergeName 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] FROM Inv_AeX_AC_Identification Ident
INNER JOIN
( SELECT Ident1.[_ResourceGuid], Ident1.[Name]
FROM Inv_AeX_AC_Identification Ident1
INNER JOIN Inv_AeX_AC_Identification Ident2
ON Ident2.[Name] = Ident1.[Name]
AND Ident2.[_id] != Ident1.[_id]
INNER JOIN vComputerResource Ident3 ON Ident1.[_ResourceGuid] = Ident3.[Guid]
GROUP BY Ident1.[_ResourceGuid], Ident1.[Name] ) udr
ON Ident.[_ResourceGuid] = udr.[_ResourceGuid]
where ident.[name] like 'is%'
GROUP BY Ident.[Name]
OPEN merge_cursor
FETCH NEXT FROM merge_cursor
INTO @MergeName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Merging ' + @MergeName
CREATE TABLE #DUPLICATE_NAME
(Resource UNIQUEIDENTIFIER, [Primary] BIT,
[Name] 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], md.[ModifiedDate] AS [LastUpdated], ident1.[OS Name]
FROM Inv_AeX_AC_Identification ident1
JOIN ( SELECT ident2.[Name] FROM Inv_AeX_AC_Identification ident2
GROUP BY ident2.[Name]
HAVING COUNT (ident2.[Name]) > 1 AND ident2.[Name] = @MergeName ) dr
ON ident1.[Name] = dr.[Name]
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 [Primary] = '1'
SELECT TOP 1 @FromGuid = Resource FROM #DUPLICATE_NAME
where Name = @MergeName and [Primary] = '0'
DROP TABLE #DUPLICATE_NAME
exec spResourceMerge @FromGuid,@ToGuid
FETCH NEXT FROM merge_cursor
INTO @MergeName
END
