Old computers in my EC

Hello FGamer,

I expect Jak to come up with a (more than) perfect solution :smileywink - also I do not yet make use of the recently announced Sophos Reporting Interface.

Meanwhile a simple query:

sqlcmd -E -S .\SOPHOS -d SOPHOS45 -Q "SELECT ID,Name,LastMessageTime,Deleted      
  FROM [SOPHOS45].[dbo].[ComputersAndDeletedComputers]
  WHERE COALESCE(LastMessageTime,0) < DATEADD(day,-90,GETDATE())

Use the applicable values for instance (.\SOPHOS) and database (SOPHOS45) and the interval (-90). Note: Computers with Deleted=1 are not visible in SEC but still in the database.

Using DELETE FROM instead of SELECT ... FROM will completely remove them and their associated data (errors and events) from the database. So use with consideration (and of course at your own risk).

Christian

Christian thank you!

In my SQL I got may computers with the status 0 and 1. I understood that 1 means that the computers was deleted from the SEC but still in my DB and the 0 are the computers that didnt connect in my network in the last 90 days correct?

How can I list only the 0?

Thank you.

The list shows only computers which didn't connect in the last 90 days. Deleted=0 are the ones not deleted, i.e. visible in SEC. Othre flags are

Managed: Managed=0 are the "grey" computers (the ones which have been found by some means but have not yet been protected).

Connected: When Connected=0 there's the red cross on the computer icon. Note that this is not always correct (old computers might still show as connected).

How can I list only the 0?

WHERE COALESCE(LastMessageTime,0) < DATEADD(day,-90,GETDATE()) AND Deleted=0

You can expand the expression as desired. Use () to group expressions.

Christian

Great Christian -

Just a last question before move forward with it. 

I figured out some computers flagged as 0 and 1 that is visible in my SEC, when I delete it if they really are in my network they will show up again in the next sync?

Thank you.

Hi,

PurgeDB.exe is another option, this can purge computers also.

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

Regards,

Jak

Jak - is it correct? 

PurgeDB -action=purge -HistoryLengthInDays=90

Thank you

You need the:

-category=computers

switch in there also.

Thanks

Jak

I getting this error:

C:\Program Files\Sophos\Enterprise Console>PurgeDB -action=purge -category=computers -HistoryLengthInDays=180log4net:ERROR [RollingFileAppender] Unable to acquire lock on file C:\Documentsand Settings\All Users\Application Data\Sophos\Sophos Endpoint Management\4.0\log\sophos-management-data.log. The process cannot access the file 'C:\Documents and Settings\All Users\Application Data\Sophos\Sophos Endpoint Management\4.0\log\sophos-management-data.log' because it is being used by another process.PurgeDB: 0 rows have been affected.

HI,

It's more of a warning I believe  If you were to stop the Sophos Management Service it wouldn't error I would think.  I figure it's trying to write to the same log the management service has open but the command is still going to affect the DB.

I think this might have been fixed in a later version.  What version of SEC are you running?

Regards,

Jak

Jak - I'm using version SEC40. I believe it works fine, because in the first time when I ran it affected 400 lines and now 0. I didnt see any impact in my SEC, it still showing me the same number of computers. it that correc: