How to find the machines with duplicate hostuuid with a SQL Query ?
Following SQL Query could give you the list of machines with problem (machines which had the problem during the last 3 days) :
DISTINCT A.hnew 'New Computer', B.hold 'Previous Computer', A.hold 'Hostuuid',
dateadd ( ss, A.hdate + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Date'
FROM AMEPHIS A, AMEPHIS B
and A.hdate>datediff(ss, convert(datetime,'19700101'), getutcdate())-60*60*24*3
(A.hnew COLLATE DATABASE_DEFAULT not in (SELECT label COLLATE DATABASE_DEFAULT FROM ca_discovered_hardware) or
A.hnew COLLATE DATABASE_DEFAULT in (SELECT label COLLATE DATABASE_DEFAULT FROM ca_discovered_hardware WHERE A.hold=host_uuid ))
and A.hdate=B.hdate and B.hterid=11
ORDER BY 2,1
This query could be executed with Microsoft SQL Server Management Studio.
In this example, JY-PC6 and JY-PC7 have the same hostuuid. But only JY-PC7 appears in ITCM Console :
How to create a ITCM Query returning the machines with duplicate hostuuid ?
A ITCM Query could also be created to find machines which have duplicate hostuuid :
1- In ITCM Explorer, Right click on Queries folder and select "New..." in order to create a new Computers query.
2- In Query Designer, Click on Advanced Argument and fill the fields like this :
Pseudo text : Duplicate Hostuuid
Table : -Any-
Additional WHERE clause :
SELECT A.hpriuuid FROM AMEPHIS A, AMEPHIS B WHERE A.hterid=14 and A.hdate>datediff(ss, convert(datetime,'19700101'), getutcdate())-60*60*24*3 and (A.hnew COLLATE DATABASE_DEFAULT not in (SELECT label COLLATE DATABASE_DEFAULT FROM ca_discovered_hardware) or A.hnew COLLATE DATABASE_DEFAULT in (SELECT label COLLATE DATABASE_DEFAULT FROM ca_discovered_hardware WHERE A.hold=host_uuid )) and A.hdate=B.hdate and B.hterid=11
Remark: SQL Query in "additional WHERE clause" should be put in a single line. Do not cut it with carriage return.
3- Click OK and save the query (ex: "Duplicate Hostuuid").
Execution of this query should return the list of all computers visible in ITCM Console which had a duplicate hostuuid problem during the last 3 days.
How to correct automatically the machines with duplicate hostuuid ?
The solution is to delete the registry value HKEY_LOCAL_MACHINE\Software\ComputerAssociates\HostUUID\HostUUID
in order to have a new HostUUID generated automatically when CAF is restarted on the machine.
This could be done via a Asset Job of type script :
1- In DSM Explorer, expand Jobs folder and right click on "Asset Jobs" folder. Click New and select Script as Job Type. Then click Next button
2- Give a name for the job (ex : Delete HostUUID) and click Next button.
In the script text enter this :
DIM hkey1 as integer
hkey1 = RegOpenKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ComputerAssociates\hostUUID")
IF hkey1 <> 0 THEN
RegDeleteVariable(hKey1, "HostUUID") THEN
click Next button.
3- Click on "Set Scheduling..." button.
Select "Run only once" in "Scheduling" tab
and in "Miscellaneous" tab, check option "This job is allowed to run unattended"
4- Click OK and Finish to terminate the Asset Job creation.
5- Under Computer and Users, create a new dynamic group based on Query "Duplicate Hostuuid" created above.
Select an Engine for evaluation and a period (ex: every 60 minutes).
This group will contain all machines visible in ITCM Console which had a problem with duplicate hostuuid during the last 3 days.
5- Drag & Drop (or Copy/Paste) the Asset Job previously created onto this group.
AM Agent on machines with duplicate Hostuuid will execute the Asset job and generate a new Hostuuid.
After some days, the group should be empty.