how do you find out computers with duplicated values?

Malik Haddad

April 01, 2015 00:48

I would like to generate a report for all computers that share the same below registry value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\SUSClientID.

for those who don't know, this ID should be unique per computer, and if its not, reporting to WSUS will be wrong as WSUS identify computers using this ID.

after some troubleshooting I found out that a lot of computers are sharing same ID because they were deployed using the same image, and now I have to keep track of those with same ID and deploy a script to fix it.

How am I doing it right now?

I created a scan profile that looks for the above key, and did a scan for some computers, then I generate a report that have 2 columns, computername and the above registry value, I filter by the registry key name, then I take the report to excel and find those with duplicate IDs.

later I even clean them up manually or deploy a script that will delete the ID from registry and restart the windows update service in order to renew it.

am trying to find a way to generate a report that only contains those with duplicated IDs, check attached to see my current report.

Regards

2015-03-31_17-47-52.jpg

Comments

3 comments

Shane Corellian

April 06, 2015 17:49

To do this create a new SQL report. Go to REPORT > New Report > SQL.

Paste in query below.

SELECT Computers.Name 
FROM Computers inner join 
  RegistryEntries using (ComputerId) INNER JOIN
  RegistryPaths using (RegistryPathId)
WHERE  AND RegistryPaths.PathName = 'SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate' and RegistryEntries.Name = 'SusClientId' 
AND EXISTS (
  SELECT * from Computers c INNER JOIN
  RegistryEntries re using (ComputerId) 
  WHERE re.RegistryPathId = RegistryEntries.RegistryPathId AND re.Name = RegistryEntries.Name and re.Value = RegistryEntries.Value AND c.ComputerId != Computers.ComputerId);

Obviously you'll need to scan all the computers with the Scan Profile you created.

Mariusz Sznerch
August 25, 2016 09:52 (Edited August 25, 2016 10:25)

Hello Shane.

I'm getting below error :-(

I can run basic report which return all the values.

any chance for a little help ?

Thank you

Mariusz

0

Colby Bouma

May 16, 2019 23:31

It looks like Shane's report had an extra AND in it. I fixed that, tweaked the formatting, added a couple things, and tested it (unlike Shane) 😉

SELECT
	  Computers.Name	AS "Computer Name"
	, RegistryEntries.Value	AS "SusClientId"
FROM
	Computers
INNER JOIN
	RegistryEntries USING (ComputerId)
INNER JOIN
	RegistryPaths USING (RegistryPathId)
WHERE
		RegistryPaths.PathName = 'SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate'
	AND
		RegistryEntries.Name = 'SusClientId' 
	AND
		EXISTS (
			SELECT
				*
			FROM
				Computers c
			INNER JOIN
				RegistryEntries re USING (ComputerId)
			WHERE
					re.RegistryPathId = RegistryEntries.RegistryPathId
				AND
					re.Name = RegistryEntries.Name
				AND
					re.Value = RegistryEntries.Value
				AND
					c.ComputerId != Computers.ComputerId
		) 
	AND
		<ComputerFilter>
ORDER BY
	RegistryEntries.Value

Please sign in to leave a comment.

Didn't find what you were looking for?

New post