This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Finding and Removing Duplicate Computer Names

Is there a method to find duplicate computer names in SEC easily? Finding I have to go through each one of our groups and go down the list and eyeball them, which takes a long time with 30+ groups and 6800+ clients. How is this not a built-in feature



This thread was automatically locked due to age.
Parents
  • 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.

  • When support run the query that deleted the duplicate name it was done to delete all 609 (304 computer names).

    I then forced an AD sync.  Any endpoints that came back greyed out but had a working AV installed, I just did Protect Computer which really need to be done so the SEC and the endpoint synced up.  I could have waited until the endpoint checked in but I felt that the wind had been blowing to much outside and the endpoint might not check in correctly.

    A week later I ran the query that identifies duplicates and there was 61 of them.  Not sure why this happened.  I had communicated with staff to only install AV once the PC had been joined to the domain.  Not sure if this is an issue with our 4 DC not syncing fast enough but I would see other DC issues and I am not.  It could be that the techs are joining the domain, restarting and then installing AV but the SEC AD sync has not happened, currently set to 30 minutes, and the SEC thinks the PC is not joined to the domain yet and this is what I am thinking is happening.  But what I don't know is SEC intelligent enough to know if the PC is a member of the domain or does it just look at if it is part of the OU sync.

    I am going with SEC Security Made Simple not intelligent because the AD sync is a one way process, it only looks at what is in the OUs it is syncing, and if a PC is deleted from AD, it doesn't get deleted from SEC on the next AD sync.  You have to manually find all of your stall PCs and deleted.

    I still have an on-going ticket with support on this and will update when and if we figure out why duplicates keep showing up.

  • Thanks for this. Seems as though this is a "know issue" yet we don't have a proper fix in place.

    On Thursday (just before a long bank holiday in the UK) we ran into a issue where no machines were showing as connected on the SEC-not a single machine was showing connected. This was of course of serious concern and a call to Sophos Support resolved it (essentially the envelope folder went into six figures). Support engineer was excellent, however looking at the time when the machines stopped talking to the SEC was about the time we ran the SQL script to delete one duplicate. Coincidence? Not sure, but I will proceed with caution next time.

  • My approach to this know issue/bug is to have Sophos support do all the deleting of the duplicates.

    This way they document in their records that they had another customer call in with the duplicate issue/bug and this forces Sophos to take ownership of the issue/bug.

    When support deleted my 609 duplicates, I saw no issues with SEC other than more duplicates show up again.

    We are hosting the SEC database on an Enterprise MS SQL server.

    From what I have seen just because you have duplicates doesn't mean your endpoint is not protected.  They might not be getting the most current polices, and they might not be able to communicate with the SEC but they should be communicating with Sophos directly for updates.  Should be....

    I will say this it appeared that after the 609 were deleted, SEC started preforming better.

    With SEC being a 110% reliant on DNS, your endpoint might be confusing SEC when their IP change and a Duplicate created.  This is just a thought.

    To reduce the DNS issue and permissions to the update folder, Support and I created an Alternate update location.  I set the share to full control for everyone, and the security to read for everyone.  this appears to have helped with updating and duplicate issues.

  • Thanks for this, very informative. I agree that DNS is probably critical to how Sophos operates (and lets be honest AD in general relies upon DNS working properly), so makes sense. The alternative update location is an interesting one- at the moment it is set to Sophos

    We have laptops flipping between LAN and Wireless LAN quite often if people do to meetings, etc. Perhaps this is causing an issue with the SEC?

    Our setup is similar to yours that the SEC database is on a separate MS SQL Server (Cluster)/ 

  • We host SEC internally.  But looking at Sophos Central.

    If you did a standard default install, the primary location should be a share off the SEC which took all the default share and security permissions which I feel some of our endpoints get confused with.  As you might have figured out already Sophos doesn't like netted security groups.  This is why you must add your domain admin account directly into the PCs local Sophos Administrator group if you want to uninstall Sophos AV.  This is also also why Sophos gets confused (also a factor that creates duplicates) when you install AV before you join the PC to your domain as the cloned local security groups will not get updated once the PC is joined to the domain with domain security groups.

    This was one of the reasoning why I set the alternate update location share to Everyone and the security to Everyone Read.

    You might want to try creating an alternate location for updates and have these laptops use it.

  • Hello Navar Holmes,

    you must add your domain admin account directly into the PCs local Sophos Administrator
    I've already dissented from the must. As you say you encounter problems when you install AV before you join the PC to your domain - admittedly there's no documented recommendation to first join the computer and then install Sophos (AFAIK there has been one when the groups' SIDs instead of the names were used in the configuration). It's by design (and documented) that the Sophos groups are not modified after the initial install. I can't see why Sophos has to be installed before joining the domain.

    Definitely Sophos' local  security groups and the update location(s)/policy are not related in any way.

    Christian 

  • Hello Peter,

    flipping between LAN and Wireless LAN
    shouldn't cause issues, the endpoint reports its IP but the IP isn't used to identify a computer or to associate it with a domain or workgroup.

    Christian

Reply Children
  • Morning Christian,

    Thanks for this. Do you know how Sophos identifies a machines, is it done by the machine SID?

    Thanks

    Peter

  • Hello Peter,

    it has changed over time, the SID is no longer used.
    Basically the endpoint generates a GUID during install and registers with the management server and requests a client certificate. Apart from this IdentityTag a number of other attributes is also recorded, the main potentially identifying attributes are Name, OS (version), and workgroup/domain. If you take an image at this point all machines derived (without performing additional steps) from this image will appear as the same.
    This is because the management server attempts to identify a machine as "the same" in two disparate scenarios

    1. the computer changes its name and/or workgroup/domain membership (the IdentityTag stays)
    2. the Endpoint software is uninstalled and reinstalled or the computer reimaged (the IdentityTag changes)

    Please note that identical names are permitted if

    1. the IdentityTag of the computers is different and
    2. not both OS and workgroup are the same

    Thus if an endpoint registers with a yet unknown name and IdentityTag a new computer object is created. If it registers with a known Name,OS,workgroup triple the IdentityTag of the existing computer object is updated. V.v. if the IdentityTag is known. In case of AD Sync a computer object with the applicable triple is searched for, if none is found one with an empty IdentityTag is created.

    Actually it's a little bit more intricate, the logic has been refined over the years, and it's not perfect. It can't be without added complexity. 

    Christian

  • Found another way to create duplicates.

    Had a W7 PC that a tech installed AV on and the PC was still in the default Computer OU (which is excluded from AD Sync).

    The install will complete buy communication to the SEC fails which is normal and the PC is added to the Unassigned group.

    I moved the PC to a custom group (non-AD Sync) and then did an update.

    The updated worked and I restarted.

    I then moved the PC to its correct OU which is part of the AD sync and magically I now have duplicates.

    I deleted the PC and forced an AD sync.  My grey out PC is in the AD sync group and my green PC is in the custom group.

    SEC gets confused why to easily.

  • More weird Duplicate issues.

    Seeing how the AD sync is only a one-way process you have to find you stale endpoints in SEC and delete them.  A normal process.

    So I did my SQL query and delete the duplicates.

    I then deleted unmanaged and stale (over 45 sense last checkin) workstation.

    Came back an hour later (AD sync is set to every 30 minutes) and noticed that my unmanaged number had really jumped.

    Queried the for duplicates and had a 160 duplicates.  Weird as I had none before the clean up.

  • Hello Navar Holmes,

    communication to the SEC fails which is normal
    not sure what you mean by communication as and the PC is added to the Unassigned group suggests that RMS did correctly register and communicate (otherwise the endpoint wouldn't appear in the console). One important detail is whether the computer joined the domain before or after the Sophos install and, as said, depending on the order of actions timing can be critical.

    [SQL] delete the duplicates [...] deleted unmanaged
    delete is overloaded as for the database it means actual removal but a console delete just "hides" a computer but leaves it in the database. Again it's not clear what exactly you are referring to: If delete the duplicates means deleting computers with names that exist more one time then this should already have taken care of the unmanaged ones. That it causes lots of duplicates to appear indicates that something is fundamentally wrong with this specific procedure (and not with SEC).

    Christian
       

  • I deleted all of my duplicates from the SQL database.  The SQL query deletes the endpoint twice. 2 x 84 = 168 as this is the only and best way to delete duplicates.

    For actively managed endpoints that had a duplicate, they will re-populate in the Sophos Enterprise Console when they check in for updates or is restarted.

    There are now no duplicates showing in the Sophos Enterprise Console.

    I then deleted all my stale endpoints that were in the Sophos Enterprise Console.

    Why? Because the AD sync in the Sophos Enterprise Console is a one way process and the Sophos Enterprise Console has no way of knowing when an Endpoint has been deleted from AD.

    Note:  This is also a normal maintenance process to the Sophos Enterprise Console Administrator if you want to keep your numbers accurate.

    I then checked for duplicates in the SQL database and found none.

    I then forced an normal AD sync to run.

    Then checked for duplicates in the SQL database and found a 168 of them.

    Why???  When the AD sync happened it should have only pulled in endpoints that were still in AD.

    Either AD is keeping multiple copies of endpoints.  Impossible.  So that only leaves Sophos Enterprise Console and its SQL database.

  • Hello Navar Holmes,

    they will re-populate in the Sophos Enterprise Console when they check in for updates
    (minor) correction: When they send a message to the management server. This can be a status message, an alert (detection, cleanup, change of settings), an event, or an error. Of course after an update the status is sent, likewise after a stop/start of certain Sophos services, and naturally a reboot.

    delete stale endpoints [because SEC] has no way of knowing when an Endpoint has been deleted from AD
    SEC does detect that a computer is either moved outside a synchronized OU or deleted (which of the two it can't tell as it does not query OUs outside the synced containers), in this case it moves the endpoint to the Unassigned group. Actually it can never tell that an endpoint is gone for good - my "record" endpoint resurfaced after almost five years(!), really.

    I deleted ...
    using DELETE FROM whichTable? WHERE ... ?

    168 duplicates - Why???
    indeed this is the serious question and a puzzler. Your description is coherent (up to this point), the procedures are appropriate, and I agree with AD not keeping multiples. I'd also expect that the SQL database is consistent and records that are gone are truly gone. SEC's AD Sync doesn't keep a slave cache and SEC doesn't keep a copy of the data outside the SQL database. Hm ...

    So these 168 computers ...?

    • are 84 names that don't exist at all (neither in AD nor are there actual machines with these names)
    • it's the same 168 each time
    • one instance is in the console in a synced group as unmanaged, the other in some other group (Unassigned?) as managed
    • none of the computers is Connected

    You do have computers that are correctly synced, haven't you? Do you have sub-OUs (and thus sub-groups under the syncpoint), if so can you move a computer to another sub-OU and is this move reflected in SEC?

    It is AFAIK possible to trace synchronization (as well as other actions performed by the management service), I don't have the details on how to enable it at hand though and even if I could find them they are perhaps outdated. You'd need assistance from Support. 

    Christian