Here's a Script to Merge Duplicate Computers - Page 2

Nick · 09-19-2006, 10:31 AM

Thanks for sharing the updated information Steve!

kschroeder · 09-19-2006, 02:50 PM

Steve,
I used part of your "mr" join code for checking which is the true active machine and I think I have found a small bug. One of the data classes you check is AeX_AC_Location; from what I can tell this is modified by the AD Component (at least in the 6.1 release). The AC Identification table is also modified by the AD Connector as best I can tell (at least by a full AD Import). I used part of the code in a report I was working on to truly find "active" machines and several which show no configuration request for months came up with a ModifiedDate of today at 10:30 when I forced a full AD Import. I'm thinking about using a combined technique of grabbing the MAX of Last Config Request and the date returned by this join.

Thanks again for your efforts on this!

Steven_Oakes · 09-20-2006, 06:16 PM

Thanks for checking that out. Yeah the AD connector updates AC Location and Identification. They've been removed from the script.

Did I mention that the new version will also merge all of the duplicates for a particular name.domain combination. The old version would only do one duplicate each run.. so this is more complete..

Script follows..

/*

Duplicates merge script v2.1
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 (
-- Some of the 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
'9FEBC21A-580C-4C18-9DC9-2AC4179FD004', -- AeX AC SMS Client Properties
'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

kpjernigan · 10-24-2006, 05:47 PM

Is there something I'm missing? when running the script we get the following error: We're using the "Duplicates merge script v2.1"

Quote:

Msg 208, Level 16, State 1, Line 35
Invalid object name 'Inv_AeX_AC_Identification'.
Msg 208, Level 16, State 1, Line 35
Invalid object name 'Inv_AeX_AC_Identification'.
Msg 208, Level 16, State 1, Line 35
Invalid object name 'Inv_AeX_AC_Identification'.
Msg 208, Level 16, State 1, Line 35
Invalid object name 'vComputerResource'.

The NS we're trying this on is at Version No: 6.0.5287

Any ideas?

Steven_Oakes · 10-24-2006, 06:40 PM

It looks like your not selecting the Altiris database to run the script against.

In query analyzer, make sure the database listed at the top of the screen is 'Altiris' and not master, or some other database.

By the way, version v2.1 is now old.

I have attached the latest version (which merges retired resources aswell)

script begins below

-------------------------

Duplicates merge script v2.2
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.

v2.1: Exclude AD Connector Dataclasses
v2.2: Merges retired computers
*/

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 distinct iac.[Name],iac.Domain
from Inv_AeX_AC_Identification iac
join (
select [Name],Domain,count(*) as [Count]
from Inv_AeX_AC_Identification iac
group by [Name],Domain
having count(*) > 1
) dup
On lower(iac.[Name])=lower(dup.[Name])
And lower(iac.Domain)=lower(dup.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 (
-- Some of the 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
'9FEBC21A-580C-4C18-9DC9-2AC4179FD004', -- AeX AC SMS Client Properties
'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

kpjernigan · 10-26-2006, 12:09 PM

Well.. we're making progress.

Now I get

Quote:

Msg 102, Level 15, State 1, Line 47
Incorrect syntax near 'merge_cursor'.

Steven_Oakes · 10-26-2006, 02:11 PM

I just checked it, it is fine, except that it is missing the '/*' at the start to begin the commenting at the top.

Try again , and put /* at the start.

kpjernigan · 10-26-2006, 02:43 PM

Still same issue.

Running the following on the Altiris DB:

Quote:

/*
Duplicates merge script v2.2
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.
v2.1: Exclude AD Connector Dataclasses
v2.2: Merges retired computers
*/

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 CURSORFOR
selectdistinct iac.[Name],iac.Domain
from Inv_AeX_AC_Identification iac
join(
select [Name],Domain,count(*)as [Count]
from Inv_AeX_AC_Identification iac
groupby [Name],Domain
havingcount(*)> 1
) dup
Onlower(iac.[Name])=lower(dup.[Name])
Andlower(iac.Domain)=lower(dup.Domain)
OPEN merge_cursor s
FETCH NEXT FROM merge_cursor
INTO @MergeName, @MergeDomain
WHILE@@FETCH_STATUS= 0
BEGIN
PRINT'Merging '+ @MergeName +'.'+ @MergeDomain
CREATETABLE #DUPLICATE_NAME (
Resource UNIQUEIDENTIFIER,
[Primary] BIT,
[Name] NVARCHAR(64),
[Domain] NVARCHAR(64),
LastUpdated DATETIME,
[OS Name] NVARCHAR(64))
INSERTINTO #DUPLICATE_NAME
SELECT ident1.[_ResourceGuid] AS [Resource],CAST(0 ASBIT)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
GROUPBY ident2.[Name], ident2.[Domain]
HAVINGCOUNT(ident2.[Name])> 1
AND ident2.[Name] = @MergeName AND ident2.[Domain] = @MergeDomain
) dr ON ident1.[Name] = dr.[Name] and ident1.[Domain] = dr.[Domain]
LEFTOUTERJOIN
(
SELECT rus.[ResourceGuid],MAX( rus.[ModifiedDate] )AS ModifiedDate
FROM ResourceUpdateSummary rus
WHERE rus.InventoryClassGuid in(
-- Some of the 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
'9FEBC21A-580C-4C18-9DC9-2AC4179FD004',-- AeX AC SMS Client Properties
'9E6F402A-6A45-4CBA-9299-C2323F73A506'-- AeX AC Client Agent
)
AND ResourceGuid in(select Guid from vComputer)
GROUPBY rus.[ResourceGuid]
--) td ON td.ResourceGuid = rus.ResourceGuid AND td.ModifiedDate = rus.ModifiedDate
) mr ON ident1.[_ResourceGuid] = mr.[ResourceGuid]
SELECTTOP 1 @PrimaryResource = Resource FROM #DUPLICATE_NAME ORDERBY [LastUpdated] DESC
UPDATE #DUPLICATE_NAME SET [Primary]=1 WHERE Resource = @PrimaryResource
--SELECT * FROM #DUPLICATE_NAME ORDER BY [LastUpdated] DESC
SELECTTOP 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 CURSORFOR
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 asnvarchar(36))+' to: '+Cast(@ToGuid asnvarchar(36))
exec spResourceMerge @FromGuid,@ToGuid
FETCH NEXT FROM ResourcesToBeMerged
INTO @FromGuid
END
CLOSE ResourcesToBeMerged
DEALLOCATE ResourcesToBeMerged
DROPTABLE #DUPLICATE_NAME
FETCH NEXT FROM merge_cursor
INTO @MergeName, @MergeDomain
END
CLOSE merge_cursor
DEALLOCATE merge_cursor

Still getting error:

Quote:

Msg 102, Level 15, State 1, Line 47
Incorrect syntax near 'merge_cursor'.

I'm not sure why it's bombing out... am I losing my mind?

Steven_Oakes · 10-26-2006, 04:22 PM

The script you pasted above is definitely going to error out.
It has run the text together.

Youll have to workout why the copy and paste that you are doing is changing the text..

cfaletti · 12-11-2006, 09:42 AM

I am currently running into a problem with computers not merging. In resources, IT assets, computers, I try to make changes to an existing computer resource, but when saving it, it tells me the serial number already exists in the system. When I go to reports, assets and inventory...windows...hardware...serial number report and search for it, then the serial number is not found. I run an automatic merge overnight, but it doesn't seem to be working.

I don't know the first things about all these scripts and SQL reports, so does anyone have any other suggestions?

shawn.m · 12-11-2006, 11:54 AM

if the serial number is blank ' ' or space. the database thinks you have a duplicate value. becuase you have multiple blank serial numbers.

You can either do a simple update script to populate all the serial numbers with a varing number or fix them on a one off basis

try running the following update query to fill in blanks or nulls in inv_serial_number

Update sn
set sn.[serial number] = hwsn.[serial number]
from inv_serial_number sn
left outer join inv_asset_tag a
on sn._resourceguid = a._resourceguid
left outer join vasset
on a.barcode = vasset.Barcode
left outer join dbo.Inv_AeX_AC_Identification acid
on a.[barcode] = acid.[name]
left outer join dbo.Inv_AeX_HW_Serial_Number hwsn
on acid._resourceguid = hwsn._resourceguid

where sn.[serial number] like '' and hwsn.[serial number] is not null
or sn.[serial number] is null and hwsn.[serial number] is not null

Steven_Oakes · 12-11-2006, 11:55 AM

Love the table alias 'acid' Im going to use that one now ..

loskeee · 12-28-2006, 10:47 AM

I have a question about this script you designed how would I add this script? to my NS Console Will I add it by just creating a New report and enter the info in Directly? or an import from a text file ?

Steven_Oakes · 01-02-2007, 09:58 AM

The following should be saved as XML and can then be imported.
It is a folder which contains two reports, and the merge script v2.2 in a Notification policy.

Using an NP means you can schedule it to run. .

Steve

-----------------------------------

<item guid="{52f4b1a1-96ce-4ba4-a50f-5803729c65cc}" classGuid="{f1e8956a-a158-487f-8185-4c3b492734e7}">


<name>Duplicate computers</name>
<alias />
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description />
<name>Duplicate computers</name>
</culture>
<culture name="en">
<description />
</culture>
</itemLocalizations>
<url />
<imageUrl />
<replicateFolderContents>False</replicateFolderContents>
<InheritedCreatableClasses>True</InheritedCreatableClasses>
<AllowCreateSubFolders>True</AllowCreateSubFolders>
<parentFolderGuid>53369be2-e1f9-4188-a01a-0c9c132f6b3f</parentFolderGuid>
<security owner="S-1-5-21-842925246-2000478354-682003330-500" inherit="True">
<aces>
<ace type="trustee" sid="S-1-5-21-842925246-2000478354-682003330-500" name="">
<permissionGrants>
<permissionGrant guid="{f57658a3-865e-47d8-8a77-0210ade119b0}" name="Run Report" />
<permissionGrant guid="{7db9d601-2ef0-4222-b16d-06616b74c917}" name="Resource Association Write" />
<permissionGrant guid="{5b6fdad2-7c6f-4717-a077-16deff292ac8}" name="Use for Secured Collection" />
<permissionGrant guid="{0bef78d6-13f0-4f9e-9a56-1ba2c7a5dbe1}" name="Save Reports" />
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
<permissionGrant guid="{3ffe8fe1-6e70-4603-af63-30517bf871f7}" name="Apply Software Delivery Tasks" />
<permissionGrant guid="{bf3778ac-5e4c-40ce-85a9-3488fb20128f}" name="Data Class Read" />
<permissionGrant guid="{65dd8345-02b2-45e5-90fa-36cd22fad159}" name="Resource Association Read" />
<permissionGrant guid="{3433b599-a63e-4f5f-9888-40e23a1ef002}" name="Apply to Collections" />
<permissionGrant guid="{a8a0f946-bde7-4078-a2d8-44f575aa7b3f}" name="Apply to Collections" />
<permissionGrant guid="{0b094f39-caa2-468f-af84-4670a5c8493f}" name="Apply To Collections" />
<permissionGrant guid="{1ca96049-fbc3-4a20-a71f-4cffcf2f1dd5}" name="Apply Agent Settings" />
<permissionGrant guid="{93d33ee3-5703-4a24-ac4f-4dbe460b3b83}" name="Run Reports" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
<permissionGrant guid="{c7e46386-a9b2-42c9-b19b-586241fa718c}" name="Install On Approval" />
<permissionGrant guid="{65c07e54-cd90-4d43-8491-5ad31b9d4012}" name="Install On Approval" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
<permissionGrant guid="{0d631690-66f8-4bc8-9edf-6bfdabf4bd61}" name="Data Class Write" />
<permissionGrant guid="{a56f6866-b362-4efc-bffa-a29e7c9d2f2f}" name="Install Software" />
<permissionGrant guid="{fd0ae5cf-1d5e-4480-bc75-aeafe92bddee}" name="Apply Application Monitors" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
<permissionGrant guid="{25328a91-3f78-499b-9760-cace2f180a3a}" name="Install Software" />
<permissionGrant guid="{24feda4a-9025-401f-befd-cc9c9e99f047}" name="Policy Enable" />
<permissionGrant guid="{4a25c2ac-be78-4361-a82b-d0206d3ecac8}" name="Create Children" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences>
<itemReference guid="{66f99686-8cc7-45b1-b04c-5b7f1b096aa5}" hint="folderchilditem" type="DependentChild"><item guid="{66f99686-8cc7-45b1-b04c-5b7f1b096aa5}" classGuid="{67033a4e-a848-4a0b-b037-ab02854a315a}">


<name>Duplicate computer resources by name only (Inv_AeX_AC_Identification table)</name>
<alias />
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Please enter the report description</description>
<name>Duplicate computer resources by name only (Inv_AeX_AC_Identification table)</name>
</culture>
<culture name="en">
<description>Please enter the report description</description>
</culture>
</itemLocalizations>
<scheduling>
<enabled>False</enabled>
<sharedSchedule>{00000000-0000-0000-0000-000000000000}</sharedSchedule>
</scheduling>
<version>6.0</version>
<manufacturer />
<alwaysPromptParameters>True</alwaysPromptParameters>
<queries>
<query type="builderQuery">
<view type="grid">
<enabled>True</enabled>
<default>True</default>
<hide><![CDATA[Guid]]></hide>
</view><queryBuilder><directEdit legacy="true" /><userCustomized><![CDATA[
select i.Guid,i.Name,iac.Domain,iac.[System Type],iac.[OS Name]
from Item i
join Inv_AeX_AC_Identification iac
on i.Guid = iac._ResourceGuid
where i.Guid in (
select Guid
from vResource
where ResourceTypeGuid in (
select ResourceTypeGuid
from ResourceTypeHierarchy
where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f'
)
and Guid in (
select _ResourceGuid
from Inv_AeX_AC_Identification
where [Name] in (
select distinct iac.[Name]
from Inv_AeX_AC_Identification iac
join (
select [Name],count(*) as [Count]
from Inv_AeX_AC_Identification iac
group by [Name]
having count(*) > 1
) dup
On lower(iac.[Name])=lower(dup.[Name])
)
)
)
order by i.Name

]]></userCustomized></queryBuilder></query>
</queries>
<parentFolderGuid>52f4b1a1-96ce-4ba4-a50f-5803729c65cc</parentFolderGuid>
<security owner="S-1-5-21-842925246-2000478354-682003330-500" inherit="True">
<aces>
<ace type="trustee" sid="S-1-5-21-842925246-2000478354-682003330-500" name="">
<permissionGrants>
<permissionGrant guid="{f57658a3-865e-47d8-8a77-0210ade119b0}" name="Run Report" />
<permissionGrant guid="{0bef78d6-13f0-4f9e-9a56-1ba2c7a5dbe1}" name="Save Reports" />
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
<permissionGrant guid="{93d33ee3-5703-4a24-ac4f-4dbe460b3b83}" name="Run Reports" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences />
</item></itemReference>
<itemReference guid="{997c36e9-3d3e-4201-a298-82fbb17fcb2a}" hint="folderchilditem" type="DependentChild"><item guid="{997c36e9-3d3e-4201-a298-82fbb17fcb2a}" classGuid="{67033a4e-a848-4a0b-b037-ab02854a315a}">


<name>Duplicate name.domain computer resources (Inv_AeX_AC_Identification table)</name>
<alias />
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Please enter the report description</description>
<name>Duplicate name.domain computer resources (Inv_AeX_AC_Identification table)</name>
</culture>
<culture name="en">
<description>Please enter the report description</description>
</culture>
</itemLocalizations>
<scheduling>
<enabled>False</enabled>
<sharedSchedule>{00000000-0000-0000-0000-000000000000}</sharedSchedule>
</scheduling>
<version>6.0</version>
<manufacturer />
<alwaysPromptParameters>True</alwaysPromptParameters>
<queries>
<query type="builderQuery">
<view type="grid">
<enabled>True</enabled>
<default>True</default>
<hide><![CDATA[Guid]]></hide>
</view><queryBuilder><directEdit legacy="true" /><userCustomized><![CDATA[
select i.Guid,i.Name,iac.Domain,iac.[System Type],iac.[OS Name]
from Item i
join Inv_AeX_AC_Identification iac
on i.Guid = iac._ResourceGuid
where i.Guid in (
select Guid
from vResource
where ResourceTypeGuid in (
select ResourceTypeGuid
from ResourceTypeHierarchy
where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f'
)
and Guid in (
select _ResourceGuid
from Inv_AeX_AC_Identification iac2
join (
select distinct iac.[Name],iac.Domain
from Inv_AeX_AC_Identification iac
join (
select [Name],Domain,count(*) as [Count]
from Inv_AeX_AC_Identification iac
group by [Name],Domain
having count(*) > 1
) dup
On lower(iac.[Name])=lower(dup.[Name])
And lower(iac.Domain)=lower(dup.Domain)
) dup
on iac2.[Name] = dup.[Name]
And iac2.Domain = dup.Domain

)
)
order by i.Name

]]></userCustomized></queryBuilder></query>
</queries>
<parentFolderGuid>52f4b1a1-96ce-4ba4-a50f-5803729c65cc</parentFolderGuid>
<security owner="S-1-5-21-842925246-2000478354-682003330-500" inherit="True">
<aces>
<ace type="trustee" sid="S-1-5-21-842925246-2000478354-682003330-500" name="">
<permissionGrants>
<permissionGrant guid="{f57658a3-865e-47d8-8a77-0210ade119b0}" name="Run Report" />
<permissionGrant guid="{0bef78d6-13f0-4f9e-9a56-1ba2c7a5dbe1}" name="Save Reports" />
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
<permissionGrant guid="{93d33ee3-5703-4a24-ac4f-4dbe460b3b83}" name="Run Reports" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences />
</item></itemReference>
<itemReference guid="{9719b986-e8bd-43f0-9cbf-8fb659d32547}" hint="folderchilditem" type="DependentChild"><item guid="{9719b986-e8bd-43f0-9cbf-8fb659d32547}" classGuid="{bfa1aa3f-4a1d-453e-90d2-7ba2d3dec768}">


<name>Message Filter for Notification Policy {d218111a-c167-48ef-9397-956f9c91a757}</name>
<alias />
<productGuid>{d218111a-c167-48ef-9397-956f9c91a757}</productGuid>
<itemAttributes>Hidden</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Filters messages that are created by (and destined for) a Notification Policy</description>
<name>Message Filter for Notification Policy {d218111a-c167-48ef-9397-956f9c91a757}</name>
</culture>
<culture name="en">
<description>Filters messages that are created by (and destined for) a Notification Policy</description>
</culture>
</itemLocalizations>
<nsMessageSource>d218111a-c167-48ef-9397-956f9c91a757</nsMessageSource>
<nsMessageTypeGuid>{e12a0e9e-30a0-4529-b38d-493fed8744b4}</nsMessageTypeGuid>
<parentFolderGuid>52f4b1a1-96ce-4ba4-a50f-5803729c65cc</parentFolderGuid>
</item></itemReference>
<itemReference guid="{d218111a-c167-48ef-9397-956f9c91a757}" hint="folderchilditem" type="DependentChild"><item guid="{d218111a-c167-48ef-9397-956f9c91a757}" classGuid="{ff0a95e4-304e-45d2-90b7-7d0267865a25}">


<name>Automatic Merge of Name.Domain duplicates</name>
<alias />
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description />
<name>Automatic Merge of Name.Domain duplicates</name>
</culture>
<culture name="en">
<description />
</culture>
</itemLocalizations>
<enabled>True</enabled>
<scheduling>
<enabled>True</enabled>
<sharedSchedule>{8e6c708e-18bc-4ef9-acea-2de826c4f3b9}</sharedSchedule>
</scheduling>
<policyActionParameters />
<dataSource sourceType="Query">
<query type="builderQuery">
<queryBuilder>
<directEdit />
<userCustomized><![CDATA[
/*

Duplicates merge script v2.2
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.

v2.1: Exclude AD Connector Dataclasses
v2.2: Merges retired computers
*/

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 duplicated 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 distinct iac.[Name],iac.Domain
from Inv_AeX_AC_Identification iac
join (
select [Name],Domain,count(*) as [Count]
from Inv_AeX_AC_Identification iac
group by [Name],Domain
having count(*) > 1
) dup
On lower(iac.[Name])=lower(dup.[Name])
And lower(iac.Domain)=lower(dup.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 (
-- Some of the 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
'9FEBC21A-580C-4C18-9DC9-2AC4179FD004', -- AeX AC SMS Client Properties
'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

]]></userCustomized>
</queryBuilder>
</query>
</dataSource>
<parentFolderGuid>52f4b1a1-96ce-4ba4-a50f-5803729c65cc</parentFolderGuid>
<security owner="@APPLICATION_ID" inherit="True">
<aces>
<ace type="reserved" name="@APPLICATION_ID">
<permissionGrants>
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
<permissionGrant guid="{24feda4a-9025-401f-befd-cc9c9e99f047}" name="Policy Enable" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences>
<itemReference guid="{ce170ff0-58fb-4b89-bad1-3689bc1a6937}" hint="npmessagesubscriber" type="DependentChild"><item guid="{ce170ff0-58fb-4b89-bad1-3689bc1a6937}" classGuid="{3c87eda0-9522-4c27-8b7f-338db879ed71}">


<name>Do Nothing</name>
<alias />
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Hidden</itemAttributes>
<itemLocalizations>
<culture name="">
<description>This Notification Policy never fires, as the query never returns any rows.</description>
<name>Do Nothing</name>
</culture>
<culture name="en">
<description>This Notification Policy never fires, as the query never returns any rows.</description>
</culture>
</itemLocalizations>
<enabled>True</enabled>
<enabled>true</enabled>
<noUIDelete>false</noUIDelete>
<policyActionConfiguration><launchPolicyAction eachRow="false">
<commandLine><![CDATA[C:\DoNothing.bat]]></commandLine>
</launchPolicyAction></policyActionConfiguration>
<parentFolderGuid>00000000-0000-0000-0000-000000000000</parentFolderGuid>
</item></itemReference>
<itemReference guid="{9719b986-e8bd-43f0-9cbf-8fb659d32547}" hint="npmessagefilter" type="DependentChild"><item guid="{9719b986-e8bd-43f0-9cbf-8fb659d32547}" classGuid="{bfa1aa3f-4a1d-453e-90d2-7ba2d3dec768}">


<name>Message Filter for Notification Policy {d218111a-c167-48ef-9397-956f9c91a757}</name>
<alias />
<productGuid>{d218111a-c167-48ef-9397-956f9c91a757}</productGuid>
<itemAttributes>Hidden</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Filters messages that are created by (and destined for) a Notification Policy</description>
<name>Message Filter for Notification Policy {d218111a-c167-48ef-9397-956f9c91a757}</name>
</culture>
<culture name="en">
<description>Filters messages that are created by (and destined for) a Notification Policy</description>
</culture>
</itemLocalizations>
<nsMessageSource>d218111a-c167-48ef-9397-956f9c91a757</nsMessageSource>
<nsMessageTypeGuid>{e12a0e9e-30a0-4529-b38d-493fed8744b4}</nsMessageTypeGuid>
<parentFolderGuid>52f4b1a1-96ce-4ba4-a50f-5803729c65cc</parentFolderGuid>
</item></itemReference>
</itemReferences>
</item></itemReference>
</itemReferences>
</item>

Nick · 01-02-2007, 10:55 AM

Thanks for the updated version Steven!

Wings_Rule! · 01-02-2007, 12:50 PM

Hey Mr. Oakes,
How do you assure that old information from retired machines does not convolute this process? If a company re-uses computer naming convention, does the potential exist for inaccurate data to occur in this situation?

One thing I do not remember is whether the ResourceUpdateSummary table keeps track of both retired and current machines.

Thoughts?

Steven_Oakes · 01-11-2007, 07:11 AM

Yes I suppose this might be something worth considering.

It is possible for a machine to become duplicated (by reimaging) and then retired (since only one would be active) and remain in the database. This would represent a real duplication, and the desired action be to remove this duplicate.

I could see that there might be confusion as to whether you would want to merge these two different machines..

Considering an upgrade from 2K to XP:

Example A:
If a company uses a convention where the computer's name is its barcode number, when a computer gets upgraded you could have two resources, one of which is the old (replaced) operating system installation, and another that is the current active build on that workstation. In this scenario you would want to merge, as the old operating system no longer exists.

Example B:
If a company used a different naming convention, lets say, the users name, this could lead to a situation where the physical hardware was retired and replaced, but the computers name stays the same across two different PCs. In this case, you would probably not want to merge the two different pieces of hardware (Windows 2K, Windows XP builds) that happened to use the same computer name (UserXYZ).

What do people think about this? I dont think the latter is a common practise..
I beleive most companies conform to the first example, and in this case I beleive you would want to merge the two different resources as they really are the same.. it is just two different operating system installations, one of which no longer exists.

I doubt people would want to keep resources in the database which represent a previously installed operating system on a workstation.

chopper18 · 01-17-2007, 05:53 PM

Steven, my thanks for all the work and help with this. I have been struggling to use the original script version from the Altiris ‘Notification Server Maintenance’ document. I have been tweaking an Altiris report to run on a schedule with the original query in it but it fails, I guess for reasons you mentioned about the data class changes.

Please excuse my inexperience in this area, but could you lay out how, exactly to implement your XML import listed above? Would I copy and paste the entire post into a word processor such as MS Word, save it as XML, right click on a particular folder in Notification Server and just do an import of the complete file?
What will the end result be?
Where will your items show up?
Will it add anything to <Configuration><Server Settings><Notification Policies> ??
Is there any input required to run the policy other than setting a schedule?

I’m optimistic that the work you’ve done can help many of us when setup properly and I think your approach to implementing the merge as a policy with those benefits, is the way to go. I just would feel safer in knowing exactly the outcome before doing the import. So any feedback you could offer would be greatly appreciated.

Steven_Oakes · 01-18-2007, 05:58 AM

I wouldnt use the one that comes from Altiris.
I beelive that would be the script I wrote when I was doing Notification server support back a while ago.

The script I am providing is more up to date, and takes a few more things into consideration.

You are right about how to use it, just save the xml above to an xml file and import it. When you import an item, the location where the item is placed is where you chose to do the import.

Personally, I keep a whole collection of "tools" (Diagnostic collections, reports, NPs) under a folder under Configuration, called "Diagnostics". I recommend you do the same, and create a folder there for such diagnostic utilities.

The only problem is, I often see a big with NS when dealing with Notifcation policies. You may get this after importing the item. When the problem occurs you will see "Object reference not set to an instance of an object error".

The end result of the running of this script is that any resource that is considered "identical" will be merged. Identical is where two different computer resources in the database have the same Name/Domain combination.

Merging means that the resources will be changed into only one resource in the Notification Server. The resource that is merged "to" is the one that has the most recent dataclass update (except those dataclasses generated by the Notification Server, Ie: Direcotry Import DCs.. and so forth).

If you need further understanding, let me know.

Steven

chopper18 · 01-18-2007, 12:05 PM

Steve, thanks so much for the feedback and tools. I have successfully done the import and everything looks great.

My system just does not have any duplicates to work with at this time. So for now I will leave the policy turned off and wait until duplicates accumulate (which they always do because of re-imaging PCs with the same name), then post back my results after using the policy to clean up. I have already seen resources show up in the ‘duplicate name only’ report, which is nice to have so I can investigate and clean those up too.

For the record, as many of you probably already are aware of, MS Word is not a good choice for XML editing. In fact it was just a simple matter of pasting the code into Notepad, saving, and then renaming the extension to .xml. Then when you double-click on the file, you can see the proper structure in your web browser. Altiris did not like the MS Word version at all, and so that import failed.

For those that would like to know what happens with Steve's import, here’s what you’ll get:
I created the folder, ‘Tools Diagnostics and Utilities’. Steve’s import created the folder, ‘Duplicate Computers’. Below are screenshots of the import results.

heres-script-merge-duplicate-computers-stevenoakes_import_menu.jpg

heres-script-merge-duplicate-computers-stevenoakes_import_policy.jpg

heres-script-merge-duplicate-computers-stevenoakes_import_report1.jpg

heres-script-merge-duplicate-computers-stevenoakes_import_report2.jpg

P.S. Don't forget to maximize the screenshots to see them correctly!

Mike

chopper18 · 01-18-2007, 05:09 PM

Update: I already had six duplicates showing in the report early this afternoon. I scheduled the policy to run once immediately to test it and it worked beautifully! No duplicates of name.domain resources after the policy ran. (going by the reports….haven’t actually poured through the SQL tables) Even though I’ve successfully implemented all the Altiris recommended SQL and NS maintenance plans, I’m still occasionally getting SQL deadlocks in my logs. So hopefully these cleanups might help that problem as I’ve seen a correlation between the two in the past.

Steve, I have a couple of question regarding the policy. I noticed that in the policy, you can select to have it call on a query or call on a report. With it being set to a query, I didn’t see anything in the server logs when it kicked off and no where to see any results. So, just trying to have my cake and eat it too, here, so to speak:
- Could your updated v2.2 query be put into a separate report and called upon from the policy, to have saved reports with results on the merges? Or would it only be able to show that the query ran with no rows returned as a result of the merge?

- Also, am I to assume that when a merge happens, it will say, take all events from the older ‘from’ PC and add them into all the events and information in the ‘master’ PC? Resulting in an accumulated record of all the information that’s associated with that physical box’s history?

I ask this because, we are leaning heavily on Application Metering Solution and it seems that it tracks things like software events in a physical ‘PC box’ centric fashion, rather than ‘user’ centric fashion which suits us fine. We are trying to determine how the PC’s are being utilized in an area over time, rather than, how Joe Anybody’s particular PC usage behavior on many different PCs over time. So we want the history to stay with the physical resource records in Altiris NS database.

- As an aside, I thought you might know if the other scripts in the maintenance plans are o.k. to use, such as the defragging SQL or re-indexing scripts?

Here, I’ve attached a copy of the current v2.2 XML file to import: StevenOakesDuplicatePcMerge_2_2.xml

Mike

Steven_Oakes · 01-19-2007, 12:49 PM

Thats a great idea.

I have rebuilt it as a report, and changed the functionality so that there is a report of which computers and their guids are merged.

The report can be scheduled however you please.

I have attached the whole folder I have for troubleshooting duplicates. This includes some other reports, that gives you the ability to see the duplicates in a report if there are any.

Duplicate Computers v2.4.xml

Steve

chopper18 · 01-19-2007, 05:07 PM

Steve, thank you so much for the updated version. Your new version 2.4 is excellent. I ran the report and was super pleased to find you've designed the report to include the time it ran, the resource(s) merged, and the 'to' and 'from' guid. I can't see how you could top that one!

Thank you for all your work and I'll be checking into and contributing to your other post about creating a NS toolkit. I think that's fantastic and I'm especially interested in the maintenance tools such as the defrag SQL script, etc.

Thanks so much again!

Mike

Steven_Oakes · 02-07-2007, 06:07 AM

Shelly, please post this in a seperate thread.

I dont beleive you have the same issue as the one discussed here.

Steven

shawn.m · 03-07-2007, 03:59 PM

Quote:

Originally Posted by Steven_Oakes

The end result of the running of this script is that any resource that is considered "identical" will be merged. Identical is where two different computer resources in the database have the same Name/Domain combination.

Merging means that the resources will be changed into only one resource in the Notification Server. The resource that is merged "to" is the one that has the most recent data class update (except those data classes generated by the Notification Server, Ie: Direcotry Import DCs.. and so forth).

If you need further understanding, let me know.

Steven

The one thing to remember when you are using a notification server to find your assets. Then forwarding them to a Asset Management server. If you have imports to the Asset Management server either to Contracts or by Barcode.

That you will have duplicates still exist in your database where the Name/Domain will not be the same. Also In my case because we have incomplete inventory scans the serial number will be blank with a "" space in the field which cause the serial number field on the inventory record to have a greater modified date than the asset record. So when you do a merge it has to be done a set or two at a time. You have to go into the merge screen to select the OLDER of the two records for inv_serial_number in order to keep the serial number.

duplicates in Item
-- Query to show what duplicates by asset tag are on the Table, then display all the rows so that a determination can be made on which is more accurate

SELECT
a.guid,a.name,t1.[client date],[last barcode scan],vasset.[asset tag],vasset.[serial number], [asset type]

FROM item a INNER JOIN
(SELECT item.[name], COUNT(*) AS 'Num of Dups'
FROM item
GROUP BY item.[name]
HAVING COUNT(*) > 1) b ON a.[name] = b.[name]
left outer join [Inv_AeX_AC_Identification] T1
on a.[guid] = t1.[_resourceguid]
left outer join vasset
on a.[guid] = vasset.[_resourceguid]

where vasset.[asset type] ='computer'

Group BY a.[name], a.guid, [client date],vasset.[serial number],vasset.[asset tag], [asset type],[last barcode scan]
order by a.[name]asc , a.guid

I have about 15 different queries that I run as troubleshooting tools. I will have to rememeber to create a .xml file and post it.