Finding and Removing Duplicate Computer Names

Hi,

I would suggest querying the Sophos 'Core' database to obtain this information.

As a basic example to just get a list of computers with the same name (doesn't factor in domain/workgroup name):

SELECT name, COUNT(name) as Count FROM ComputersAndDeletedComputers GROUP BY name HAVING ( COUNT(name) > 1 )

As a one liner from a command prompt:

sqlcmd -E -S .\sophos -d SOPHOS521 -Q "SELECT name, COUNT(name) as Count FROM ComputersAndDeletedComputers GROUP BY name HAVING ( COUNT(name) > 1 )"

.\sophos is your SQL instance: https://community.sophos.com/kb/en-us/113030
SOPHOS521 is your SOPHOS core database - https://community.sophos.com/kb/en-us/17323

Hope it helps.

Regards,

Jak

Jak,

Wouldn't it actually be:

sqlcmd -E -S .\sophos -d SOPHOS540 -Q "SELECT name, COUNT(name) as Count FROM ComputersAndDeletedComputers GROUP BY name HAVING ( COUNT(name) > 1 )"

I suppose that would depend on which version of SEC you are running but hopefully it helps.

Jak,

That did help, thank you. Now that I have the list (which I piped out to a .txt file), what do I do with it? I checked on some of the results and in the GUI for SEC, two don't show, only one. 

I guess some are all ready set to deleted so they don't show in SEC.  In which case:

sqlcmd -E -S .\sophos -d SOPHOS540 -Q "SELECT name, COUNT(name) as Count FROM ComputersAndDeletedComputers where deleted = 0 GROUP BY name HAVING ( COUNT(name) > 1 )"

Regards,

Jak

Jak,

That solved the problem for me, was able to find the duplicates and removed them.

Thanks!

Jak,

That solved the problem for me, was able to find the duplicates and removed them.

Thanks!