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!

Hi,

Is this not supported "fix" still the only option? We have 76 duplicates of machines that definitely have Sophos on (and in some instances are servers) yet they are appearing twice. Deleting both entries from the SEC make them disappear for an hour and they the AD sync happens and they are both back. The greyed out one shows the correct OS info, but the "connected" one shows it as unknown.

Its getting a little tedious now :(

This is a known bug with SEC.

I had 609 duplicates which was about 20% of my endpoints.

Worked with support to delete all 609 endpoints.

For anyone having this issue, it would be best to go thru support so they can log another customer that is having this issues and to have them run all SQL query to clean it up.

One of the reasons this happens is the AV is installed on the endpoint before it was joined to the domain. And the other is the wind changed direction.

Do you know what the queries they ran are? Interesting how they see and know that this is a bug, but won't come up with a more complete fix.

Ha! 

We ran the delete from computersanddeletedcomputers where name = 'COMPUTERNAME' and the duplicates disappeared-great. Once the sync happens the machine in question appears-but greyed out. Connected to the machine and it appears on the client to be working fine and updating. 

At a total loss now on fixing this.