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

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!