PC Listed Twice: Unassigned Group & Correct GPO

Hi,

Can I ask what version of SEC are you running, it could be important and the problem may well be fixed by:

http://www.sophos.com/support/knowledgebase/article/111697.html

If however you are running the very latest version (4.5.1) or have patched the stored procedure in 4.5.0 and still having the problem I would suggest the results of the following SQL query could be helpful:

SELECT 	c.Name,
	c.Description, 
	c.DomainName, 
	c.OperatingSystem, 
	c.Managed, 
	c.Deleted, 
	c.Connected, 
	c.insertedat, 
	c.IdentityTag, 
	c.DNSName,
	c.IPAddress,
	c.MessageSystemAddress,
	cgm.GroupID
FROM [SOPHOS45].[dbo].[ComputersAndDeletedComputers] as c
    inner join ComputerGroupMapping as cgm on cgm.ComputerID = c.id
where c.Name in(
	SELECT  c.Name 
	FROM [SOPHOS45].[dbo].[ComputersAndDeletedComputers] as c
	GROUP BY c.name
	HAVING ( COUNT(c.name) > 1)
)
order by c.name

This should show how the duplicate records differ and possibly why there are 2 records for the machine.

If you don't have the SQL tools to run this query with a GUI.  I would suggest something like the following:

1. On the server where the database resides, save the above query as C:\sql.sql

2. In a command prompt run:

sqlcmd.exe -S .\SOPHOS-d sophos45 -i C:\sql.sql -o results.csv -s,

Where the above expects a local SOPHOS named instance.

This should result in a comma separated txt file you can view the details of in Excel for example.

Maybe you could post the results of any duplicates.

Regards,

Jak 

Jak, thanks for the advice.

We are running with version 4.5.1. I tried running the script and command you mentioned but cannot seem to get it working.

What exactly should be replacing the "Sophos named instance" you mentioned? Also I am getting this error in the .csv file once everything is finished running: "Sqlcmd: '-s': Missing argument. Enter '-?' for help."

Hi,

I'm not sure the name of the instance you are using so I put .\sophos to denote the local sophos instance on the same machine as the script.  This would be the default if everything is installed on the same machine, although even on the same machine a different instance could be used.

If you look in the key:

HKEY_LOCAL_MACHINE\SOFTWARE\Sophos\EE\Management Tools\DatabaseConnectionMS

On the management server, that string value will have:

Data Source=[Something]; at the end of the line.

The 'Something' is what you should put in as the -S argument as this is the SQL server and instance name.

As for the other error, did you leave the "," [comma] off the end of the command, that is the parameter to the -s (separator) switch.

Thanks,

Jak

That's what it was! I didn't realize the comma on the end was part of the command. Here are the findings from the output.

Name               Domain Name  Managed   Deleted   Connected   DNS Name                                      TERM0000      DOMAIN             1                  1               0                     TERM0000.Domain.com

TERM0000      DOMAIN             1                  0               1                     TERM0000-New.Domain.com 

TERM0000                                   0                 0               NULL             TERM0000.Domain.com    

I have another workstation that is being listed twice, it was also a machine name that was reused when replacing a dead workstation.

The computer name appears twice, once in the correct folder as active and working and another under the "Unmanaged" group. I have tried deleting the unmananged PC but it keeps re-appearing.

Hello mbjoe,

re-reading your previous (Jan 3rd) post I just now noticed that the computer name is in the database not only twice but thrice. While I think that as SAV is installed on the computer and it's reporting to SEC the problem can easily be resolved by "SQL-deleting" (i.e. removing from the database) all instances of this name (it will be re-added next time it talks to SEC) I suggest you take this to Support as it seems this is an unhandled but not unlikely case.

Christian

QC, thank you for the info. It led me into looking further and I found info on using the PurgeDB.exe file here on the forums.

I was able to purge the old duplicates using the following command and so far they have not yet re-appeared and are not showing up in the SQL database as duplicates.

PurgeDB.exe -action=purge -category=computers -HistoryLengthInDays=10