Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 2 replies
  • Subscribers 1 subscriber
  • Views 6157 views
  • Users 0 members are here
Options
Suggested
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

SQL script to purge disconnected computers

Smile

Hello,

Does someone have a short  SQL script to purge disconnected computers from the Sophos Enterprise database.

Since we upgraded tot SEC51 and placed the database on an external SQL server, the purgedb tool  is useless. We used the parameters : PurgeDB.exe -action=delete -category=computers -HistoryLengthInDays=60

Regards

:47589


This thread was automatically locked due to age.
Parents
  • 0

    HI,

    When you say it's useless, what does that mean?  That it fails to access the remote SQL instance?  

    PurgeDB.exe reads the connection string from the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseConnectionMS) unless defined in purgedb.exe.config (same directory).

    Note: The account purgedb is run as should be a member of the Sophos DB Admins group.

    Also of relevance, the management service run as task every 24 hours to "hide" (set the deleted flag) computers with a lastmessage time older than 24 hours. Details here: http://www.sophos.com/en-us/support/knowledgebase/113293.aspx.  This just marks the computers as deleted so if they do message in at a later date the deleted flag is toggled and the computer is revived in the correct group as it's never moved and deleted just hidden.

    That being said, you could delete computers with as last message time older than 60 days with the SQL:

    Show:

    select * from computersanddeletedcomputers where connected = 0 AND DATEDIFF(dd, LastMessageTime, getutcdate()) > 60


    Delete:

    delete from computersanddeletedcomputers where connected = 0 AND DATEDIFF(dd, LastMessageTime, getutcdate()) > 60

    but I would recommend using purgedb.

    Regards,

    Jak

    :47613
Reply
  • 0

    HI,

    When you say it's useless, what does that mean?  That it fails to access the remote SQL instance?  

    PurgeDB.exe reads the connection string from the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseConnectionMS) unless defined in purgedb.exe.config (same directory).

    Note: The account purgedb is run as should be a member of the Sophos DB Admins group.

    Also of relevance, the management service run as task every 24 hours to "hide" (set the deleted flag) computers with a lastmessage time older than 24 hours. Details here: http://www.sophos.com/en-us/support/knowledgebase/113293.aspx.  This just marks the computers as deleted so if they do message in at a later date the deleted flag is toggled and the computer is revived in the correct group as it's never moved and deleted just hidden.

    That being said, you could delete computers with as last message time older than 60 days with the SQL:

    Show:

    select * from computersanddeletedcomputers where connected = 0 AND DATEDIFF(dd, LastMessageTime, getutcdate()) > 60


    Delete:

    delete from computersanddeletedcomputers where connected = 0 AND DATEDIFF(dd, LastMessageTime, getutcdate()) > 60

    but I would recommend using purgedb.

    Regards,

    Jak

    :47613
Children
No Data