|
New version of Merge duplicates script.
I have written a second version of this script.
The original version will not work in SP3 due to it using dataclasses that are updated by the Notification Server. This means it will not correctly determine the most recent resource to merge to.
The new version is below. It only considers basic inventory dataclasses, so if the computer hasnt sent basic inventory then it is the old resource.
Dataclasses that SP3 NS is 'updating' are Domain discovery, AD import dataclasses, and there are some new ones to do with Collection updating duration. These are not generated by the client, and so even if clients have recent data for these dataclasses they are old and should be ignored. Basic Inventory from the client is the most reliable way to tell if a client is the most recent.
So if you use this script, you should use the newer version of it !
---------------------------
/*
Duplicates merge script v2.0
Steven Oakes (steven.oakes@gmail.com)
This script will automatically merge name.domain duplicates according to the Inv_Aex_AC_Identification table.
It takes the list of resources (guids) that have the same name and then works out which resource is the most 'recent'.
This is based from information in ResourceUpdateSummary.
Older versions of the script considered every dataclass, however some server generated dataclasses are updated by the Notification Server itself,
such as Directory Import dataclasses, or those relating to Domain Discovery.
The safest way to determine if a resource is recently updated is to query only basic inventory dataclasses as an update for one of those actually represents data coming from the client.
This version also now merges every duplicate (not just one of them) and so the script will only ever need to be ran once to get rid of all the duplicates that are currently in the database.
*/
SET NOCOUNT ON
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 script uses the same query as for the report, but automates merging the duplciated resources.'
PRINT 'The most recent duplicated resource is chosen as the master, and all older resources (as per ResourceUpdateSummary) are merged into that resource.'
PRINT ''
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],mr.[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 rus.[ResourceGuid], MAX( rus.[ModifiedDate] ) AS ModifiedDate
FROM ResourceUpdateSummary rus
WHERE rus.InventoryClassGuid in (
-- Basic Inventory Dataclasses..
'8EE53180-630E-438E-AD50-113610D2DE13', -- AeX AC Client Connectivity
'C961BFD7-07DC-40FB-B216-34B86DD20D82', -- AeX AC Roles
'BECF506C-1A95-489D-BFF6-481F53845EBF', -- AeX AC Primary User
'C9D314F7-D39D-43FE-B50A-F90AD44EC12F', -- AeX AC NT Services
'37974FB9-E60E-4027-B72D-19E3F088A6A5', -- AeX AC TCPIP
'C034D157-635E-4CCE-A320-20E3C4656A28', -- AeX AC Location
'9FEBC21A-580C-4C18-9DC9-2AC4179FD004', -- AeX AC SMS Client Properties
'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D', -- AeX AC Identification
'9E6F402A-6A45-4CBA-9299-C2323F73A506' -- AeX AC Client Agent
)
AND ResourceGuid in (select Guid from vComputer)
GROUP BY rus.[ResourceGuid]
--) td ON td.ResourceGuid = rus.ResourceGuid AND td.ModifiedDate = rus.ModifiedDate
) mr ON ident1.[_ResourceGuid] = mr.[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'
-- #DUPLICATE_NAME table contains the list of duplicates.
-- Now process the list and merge each 'old' record into the new one.
DECLARE ResourcesToBeMerged CURSOR FOR
SELECT Resource FROM #DUPLICATE_NAME where Name = @MergeName and Domain = @MergeDomain and [Primary] = '0'
OPEN ResourcesToBeMerged
FETCH NEXT FROM ResourcesToBeMerged
INTO @FromGuid
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Merging: ' + Cast(@FromGuid as nvarchar(36)) + ' to: ' + Cast(@ToGuid as nvarchar(36))
exec spResourceMerge @FromGuid,@ToGuid
FETCH NEXT FROM ResourcesToBeMerged
INTO @FromGuid
END
CLOSE ResourcesToBeMerged
DEALLOCATE ResourcesToBeMerged
DROP TABLE #DUPLICATE_NAME
FETCH NEXT FROM merge_cursor
INTO @MergeName, @MergeDomain
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
|
