Here's a Script to Merge Duplicate Computers

Same name, different domain...

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

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!

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)

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

Seems simple enough?

Code:

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

Is this Query still valid?

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

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

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

NS console script/task?

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.

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

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!

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

Hello,

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

Thanks,
Dom

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)