Here's a Script to Merge Duplicate Computers

JeremyDallas · 09-30-2005, 10:31 AM

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

JAustgen · 09-30-2005, 10:48 AM

Thanks, Jeremy!

JeremyDallas · 09-30-2005, 03:31 PM

You're welcome!

JeremyDallas · 10-01-2005, 10:37 PM

Please let me know if you have any problems with this. Thanks!

ddavis · 01-05-2006, 04:29 AM

Jeremy (or anyone else),

We are seeing a number of machines on our network that for one reason or another get a domain change (training machines switching between domains, or domain field was blank in the db & then gets populated). When this happens, the NS gives it a new GUID and we end up with "Duplicates" of the same machine in the database, even though Altiris doesn't consider them to be duplicates, per se' because of the different domain.

Do you have any magic way to detect records with duplicate names (and matching serial number or MAC, to make sure they are actually the same physical machine), and purging out only the "older" one - i.e. the one with the older last inventory date?

Thanks,
Darrell

jperry933 · 01-05-2006, 09:12 AM

I have the same exact issue with some of my clients... I have been looking for an easy to do this. Unfortunately, right now it has been a manual merge for me. Hopefully someone has a better solution!

madmanu · 01-13-2006, 01:51 PM

Is it possible, when a new guid is affected to a machine, to keep this machine collections membership (not really a duplicate GUID but a duplicate name)?
-> that will approximatively be to merge duplicate name with the older guid to keep the collections membership (that is based on the GUID and not the computer's name)

eth123 · 02-03-2006, 06:02 PM

Jeremy - Does something similar to this exist for the DS db?

Nick · 02-03-2006, 08:00 PM

There's a DS Purge script that will purge machines from the DS database that haven't updated in a defined number of days.

eth123 · 02-03-2006, 08:21 PM

Seems simple enough?

Code:

 DELETE from computer 
WHERE datediff(dd,last_inventory,getdate())>30

kschroeder · 03-04-2006, 05:04 AM

I'm using this script to work around a known issue between Inventory 6.1 and Virtual machines; all of our VMs were showing up duplicated due to some bug in IS changing the machine type. My only concern is exactly how this "merge" works...I don't want to lose the existing events, etc for my VMs when they are merged with a non-existent GUID imported by the AD Connector. Unfortunately I dont' have any free time to extensively beta test the new AD Connector beta to see if it will clear this up. Looking at the code for the script, I'm trying to figure out how it "knows" which machine is the "newest". I would prefer that it went by the last Configuration Request, but as far as I can tell it just used "ModifiedDate" column from ResourceUpdateSummary table; but I don't know what that ModifiedDate really refers to.

At this point I'm pretty much forced to run this script (I just set it as a scheduled report that runs ~30 minutes before my nightly AD update import) so that I can successfully import new machines from AD. If I don't do this, then the AD import fails due to some primary key conflict, which in turn causes my machines to not flow down into their OU-based collections, which causes my regional Admins to not see all of their actual machines in their collections, etc etc etc...ARGGGH!

gsd · 03-16-2006, 03:28 PM

Just wanted to know if this query would still work on NS 6.0.6074 SP3. Anyone know?

dominique · 03-17-2006, 02:02 PM

Hello,

For 5.5
The table names have changed AeXInv_Aex_AC_Identification, AeXv_Computer but the structure remains.

The selection should look like this:

Duplication Name:

SELECT Ident.[Name], Ident.[Domain] FROM AeXInv_Aex_AC_Identification Ident
INNER JOIN
(
SELECT Ident1.[WrkstaId], Ident1.[Name], Ident1.[Domain] FROM AeXInv_Aex_AC_Identification Ident1
INNER JOIN AeXInv_Aex_AC_Identification Ident2 ON Ident2.[Name] = Ident1.[Name] AND Ident2.[Domain] = Ident1.[Domain] AND Ident2.[_id] != Ident1.[_id]
INNER JOIN AeXv_Computer Ident3 ON Ident1.[WrkstaId] = Ident3.[WrkstaId]
GROUP BY Ident1.[WrkstaId], Ident1.[Name], Ident1.[Domain]
) udr ON Ident.[WrkstaID] = udr.[WrkstaID]
GROUP BY Ident.[Name], Ident.[Domain]

Duplication on Serial Number:

SELECT Ident.[Serial Number], Ident.[Computer Model] FROM AeXInv_Aex_HW_Serial_Number Ident
INNER JOIN
(
SELECT Ident1.[WrkstaId], Ident1.[Serial Number], Ident1.[Computer Model] FROM AeXInv_Aex_HW_Serial_Number Ident1
INNER JOIN AeXInv_Aex_HW_Serial_Number Ident2 ON Ident2.[Serial Number] = Ident1.[Serial Number] AND Ident2.[Computer Model] = Ident1.[Computer Model] AND Ident2.[WrkstaId] != Ident1.[WrkstaID]
INNER JOIN AeXv_Computer Ident3 ON Ident1.[WrkstaId] = Ident3.[WrkstaId]
GROUP BY Ident1.[WrkstaId], Ident1.[Serial Number], Ident1.[Computer Model]
) udr ON Ident.[WrkstaID] = udr.[WrkstaID]
GROUP BY Ident.[Serial Number], Ident.[Computer Model]

Thanks,
Dom

shawn.m · 05-04-2006, 03:15 PM

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

cnpalmer · 07-05-2006, 03:00 PM

Does anyone or has anyone researched a way to automate this thru NS?

Currently the only way to implement the script is to run it manually thru Query Analyzer or make the script a stored procedure and schedule it to run through SQL Ent. Manager.

I'd like to see if anyone has any good suggestions about running this thru the NS console.

shawn.m · 07-05-2006, 04:53 PM

I have mine running as a stored procedure.
the merge stored procedure does not like being run through a console window

cnpalmer · 07-06-2006, 07:32 AM

Hey Shawn,

I figured as much but the procedure works fine through the report when you right click on a workstation in the report list.

Just would like to automate that right click function... call me lazy!

Paco808 · 08-03-2006, 10:54 AM

I think I saw a XML file in the Manage fusion website for a script to merge the machines.

/Quote
"We are seeing a number of machines on our network that for one reason or another get a domain change (training machines switching between domains, or domain field was blank in the db & then gets populated). When this happens, the NS gives it a new GUID and we end up with "Duplicates" of the same machine in the database, even though Altiris doesn't consider them to be duplicates, per se' because of the different domain.
/quote

^^^^
IMO...
This sounds to me like you have machine with duplicate GUID's!!! are your using Sidchange or Sysprep on the machine images? is the domain change in the machine or the NS database?

Francisco

dominique · 08-23-2006, 01:26 PM

Hello,

Instead of merging duplicated serial number I am looking for a script which will delete the oldest one.

Thanks,
Dom

bgreen · 08-23-2006, 01:55 PM

Dom,

Reading through the query in the original post, it already grabs the two duplicate machines and determines which one has been updated more recently. That being the case, I would think you could just replace the line that executes the merge stored procedure with one that replaces the delete stored procedure (which I can't remember the name of right now):

So instead of:
exec spResourceMerge @FromGuid,@ToGuid

You'd do:
CAUTION - Just thinking out loud here, have nowhere to test it at the moment - CAUTION
exec spResourceDelete @FromGuid (or whatever the delete stored proc is called)

ack3r · 09-11-2006, 03:50 PM

I just upgraded the majority of my workstations to XP and the NS Console saw both the XP and W2k versions of the same machine. This was because the GUID changed as the XP was a clean install. Running the script got rid of the majority of my duplicates which saved me from having to do it by hand.

Thanks

Steven_Oakes · 09-12-2006, 03:47 PM

Hi Everyone.

Im new to these forums, so straight up, I will say Hello.

In answer to how to automate the script, when I need to do things like this I usually use a Notification policy.

Notification policies allow you to automate running some SQL, and you can set it on whatever schedule you would like.

The only catch is that you have to setup an automated action. I usually just create an empty batchfile and point the automated action at that, this causes nothing to happen.

Furthermore, the automated action will only run if you have rows returned from the query. If the query doesnt return rows, then you shouldnt even execute the automated action.

Creating Notification Policies to automate running queries is neat because you can export the items and re-use them on other Notification servers. It also easily allows you to disable the NP if you have a situation where you only want to perform some work and then switch the configuration off.

Keep in mind with this script that it only merges one instance of the duplicate name each run.. so that (and this would rarely be the case) if you have more than one duplicate for the name.domain pair, you will need to execute this script more than once to remove all of the duplicates.

I know how this script works, I was the one who wrote it when working for Altiris.

Cheers,
Steven Oakes

cnpalmer · 09-13-2006, 11:10 AM

to the board!!!

Anyway to share a screen shot of setting up the SQL script in NS?

Steven_Oakes · 09-19-2006, 08:28 AM

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

kschroeder · 09-19-2006, 09:28 AM

Steven,
Excellent! Thanks a lot for the update. I still have some duplicated VMs that I need to clean up; this should do the trick!