how do you find out computers with duplicated values?

Comments

3 comments

  • Shane Corellian

    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.

     

    0
    Comment actions Permalink
  • Mariusz Sznerch

    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
    Comment actions Permalink
  • Colby Bouma

    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
    
    0
    Comment actions Permalink

Please sign in to leave a comment.