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

Sophos Duplicate IDs

I just wanted to share the perl script i wrote to find duplciate unique IDs in Sophos. It scans the IIS logs looking for duplicate GUIDs.

#Stephen
#Check for Duplicates
use Data::Dumper;
$file = "\\\\sophos-c108-01\\W3SVC1\\u_ex110822.log";
my %hash = ();
my %hDup = ();
open FILE, $file or die $!;
while (<FILE>)
{ 
@data = ($_ =~ /(\b143\.55\.\d{1,3}\.\d{1,3}\b).*?(\b143\.55\.\d{1,3}\.\d{1,3}\b).*?(\{{0,1}[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}\}{0,1})/);
#print $data[0] . "\n";
if ((exists $hash{$data[2]}) && ($hash{$data[2]} ne $data[1]))
{
if(not exists $hDup{$data[1]})
{
print $data[1] . "\n";
$hDup{$data[1]} = $data[1];
}
}
else
{
$hash{$data[2]} = $data[1];
}
}
close(FILE); 

I have more info on my blog about it.

http://www.stephenjc.com/2011/08/23/sophos-duplicate-ids/

:20691


This thread was automatically locked due to age.
  • Ok then. Here's what I did (WARNING: you'll lose the computers' history and all associated events and statistics!):

    • add a (or use an existing) subscription of a different version - just enough different to force a major install, so any slightly backlevel should do
    • assign the appropriate updating policy to the dubious computers' group (it's probably better to do it for one "multi-identity" at a time)
    • locate the computer in the database, make note of its IdentityTag and when it complies with the new policy  delete (SQL DELETE) its record; it also disappears from the group
    • it takes some time after which more than one computer should appear in the Unassigned group
    • watch for the known identity to reappear (or, unlikely, a computer to reappear in the console group) - if so, repeat the applicable steps

    I can only guess how it works so I'll spare you my explanation (unless you really want it). I hope my recollection is correct. Use at your own risk.

    Christian

    :36973
  • I see the trick... but I don't feel quite at ease yet to apply your solution... 

    I've not even played with SQL queries, let alone DELETE !!

    Could you give me a query that will list IDs and names (or IPs) at first ? Or did I miss something and that is not possible ?

    Our problem is "just" to locate those PCs, really. There can't be that many for the moment. And we know what we did wrong (cloning and let go).

    Thanks.

    :36975
  • Understandable ... as the duplicates just update the same row in the database the only "conservative" approach I know is to watch the console and write down the name/IPs you see.

    Well, have to leave (got 40cm of snow and ...), gives you time to think :smileywink: - more about DELETE tomorrow

    Christian

    :36977
  • Thanks !

    Also expecting snow here in Britanny (4 cm would please me ! :smileyhappy:).

    :36979
  • Here's what I did : I put the computer in its own folder and I watch it changing its name through the day.

    7 identities so far !

    What's weird is that I stil see a computer I renewed the ID yesterday...

    I'll check the local ID file on monday...

    :37031
  • Hello PJ,

    now I've checked what I have on documentation available which is mostly my memory :smileywink:. Found that you can do without fiddling with the database if you choose a version with a backlevel RMS component (10.0 and 10.2 seem to be identical so it'd have to be 9.7).

    Just subscribe to the backlevel version, assign it to the "multiples" group and force compliance whenever you see the "Differs" in SEC. Sooner or later "new" computers should appear in the Unassigned group. Simply move them to the group(s) they should be in - they likely won't upgrade for a while due to reboot requirements but eventually they should be fine. Protection is not compromised during this time (if you use SCF they should be rebooted ASAP though).

    So maybe this helps. If not, you could still try fiddling with the database - for now I've decided to keep it for myself. Anyway I'll mention some basic queries related to this problem. Guess you don't have the SQL Management Studio installed. You can work with the database using SQLCMD from the (elevated) command prompt.

    sqlcmd -E -S <server>\<instance> -d <database> -Q "<query>"

     where <server>\<instance> is .\SOPHOS and <database> corresponds to your SEC version. <query> is the command (not necessarily a query) you want the database to perform.

    Thus to get the ID and IdentityTag for a certain computer name the query would be: 

    SELECT ID, Name, IdentityTag FROM dbo.ComputersAndDeletedComputers WHERE  Name ='name'

    Note the quotes, name is of course the one you currently see in SEC for one of the "multi" computers. If this returns nothing then meanwhile one of the other "instances" has reported to SEC (or you have mistyped the name) and you'd have to repeat the query with the "new" name. If it returns more than one line you have computer with identical names as well. Make note of the ID and IdentityTag and repeat the command now using  WHERE ID=id (note: no quotes!) several times. If more than one of the multi computers is active you will sooner or later get a different name (and also see the change in the console).

    That's it, HTH

    Christian

    :37033