Automate removal of VM/Computers from Sophos Console?

PurgeDB.exe is fully supported.  https://community.sophos.com/kb/en-us/109884

Regards,

Jak

Good to here that is supported. It does seem, however, that due to the parameters of PurgeDB, you cannot remove just a single host?

By default, the action is performed on all categories.
If <category> is specified, <history length> must also be specified.

I only want to remove a single device, not all devices in a category, or all devices past X days history.

Is there any further functionality that PurgeDB (or any other process) can provide?

Thanks,

Tanner

In that case I think you only have direct database queries left.

Would it be OK to just hide computer?  This seems the least invasive. In SEC when you delete a computer you really just hide it.  This way you can still get past report data and if the computer does come back online the existing record will be revived with the computer in the same group.

To hide a computer:

sqlcmd -E -S .\sophos -d sophos40 -Q "update computersanddeletedcomputer set deleted=1 where name='computername'"

Adjust the instance,database name and computer name as appropriate.  You could even incorporate the domain name column if you felt there was a chance of hiding the wrong computer by just name.  Maybe that could help?

Regards,
Jak

I think his may work, I will need to talk with our Sophos Admin (I am more of the developer). Is it recommended to do DB backups before using this command, like it is for PurgeDB?

Thanks for the help on this. I appreciate it!

- Tanner

I guess that might be overkill depending on how often you are deleting computers. That said, I suppose there is no harm in running the task shortly after any existing backup job?

It might be worth looking into the Stored Procedures that the Sophos Management Service calls when "deleting" a computer.  As the Management service performs most if not all operations on the data via stored procedures.   If you have SQL Profiler installer, I'd suggest deleting a computer and see which Stored Procedure the Sophos Management calls and with what parameters.

I don't have SEC handy to check right now but I have just downloaded the latest SEC installer (sec_550_sfx.exe) to get the SQL files from "C:\sec_550\ServerInstaller\DB\Core\". In "C:\sec_550\ServerInstaller\DB\Core\Computers.sql" there is a stored procedure called dbo.ComputersDelete   This essentially just calls 
"UPDATE dbo.ComputersAndDeletedComputers SET Deleted = 1" as previously suggested.  Only difference being it takes a list of computer ids and it goes on to remove the computer id from the  EMLibraryServers table as well.

Maybe you can test the call that is made if you have SQL Profiler.

Regards,
Jak

Thank you for your help on this. I will be working to see what is most feasible to us.

If I do get a script crafted, I will post it here for everyone to reference.

Thanks,

Tanner

Alright, I'm finally back to looking at this issue. Do you know if directly calling a SQL stored procedure is a supported function?

If its not, Id rather find a different interface (maybe purgedb) to delete/hide the computer.

Thanks,

Tanner

Hello Tanner,

no direct manipulation of the database is supported (unless explicitly instructed by Support).
As PurgeDB isn't up to the job and there are AFAIK no other interfaces you can either try to coerce Support to instruct you, bite the bullet and do something unsupported (note, if used responsibly its safe), or pass on it. The additional function of the SP that jak has mentioned should no longer be needed, performance shouldn't really suffer if you do occasional UPDATEs (or DELETEs) individually.

Christian   

Thanks for the information! I will talk with my management on whether they want to automate via SQL, or if sending a notification email is good enough.