Page 1 of 1

hMailServer Deleted Files DB Updater

Posted: 2010-10-13 14:20
by este
Hi,
I am not a developer so you can correct everything i say here!
But i needed to correct a problem on my web server and i asked some help to a developer friend who wrote this script for me (he did not know hmailserver and vbscript at all, so there must be better ways to do it).

I needed to check whether a deleted EML file in the /hMailServer/Data folder was still recorded in hMailServer DB: if it was still there, I wanted to delete this useless record, to keep the DB updated and consistent.
So the script simply updates the DB by deleting records to files that do no exist anymore (files that were manually deleted for some reason/mistake or deleted by an antivirus, which is the main reason i wanted to do this).

You can save the script on a .vbs file and run it from a command line by symply typing: cscript myscript.vbs

This script was only tested with hMailServer 4.4.1 B273 (on Windows Server 2008).
Since we did not know on what hMailServer event run it, I just added it on the Windows Task manager (it runs every hour). No external antivirus is set, but only the Kaspersky resident protection.
sub log(logfile, message)
wscript.echo(message)
logfile.writeLine(message)
end sub

'declare the variables
Dim ConnectionString
Dim Recordset
Dim SQL
Dim cn

set objFSO = CreateObject("Scripting.FileSystemObject")
set LogFile = objfso.opentextfile("mail-check.log",2,True)

'declare the SQL statement that will query the database
SQL = "SELECT MESSAGEID, MESSAGEFILENAME FROM HM_MESSAGES"

'define the connection string, specify database driver
set cn = CreateObject("ADODB.Connection")
set Recordset = CreateObject("ADODB.Recordset")
cn.connectionstring = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3307;DATABASE=HMAILSERVER;UID=root;PASSWORD=<YOURPASSWORD>"
cn.open

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,cn

call log(LogFile, "START CHECKING MAILS AND FILES")

'first of all determine whether there are any records
If Recordset.EOF Then
call log(LogFile, "No records returned.")
Else

'if there are records then loop through the fields
Do While NOT Recordset.Eof
id = Recordset("MESSAGEID")
filename = Recordset("MESSAGEFILENAME")
if Not objFSO.FileExists(filename) then
deleteSQL = "DELETE FROM HM_MESSAGES WHERE MESSAGEID = '" & id & "'"
call log(LogFile, "ID: " & id & " - " & filename & " > DELETED")
'cn.execute deleteSQL
end if
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects freeing up resources
Recordset.Close
Set Recordset=nothing
cn.Close
Set cn=nothing

call log(LogFile, "DONE")

If you run the script as it is here, NO RECORDS will be deleted from your DB, because the following line is commented:
'cn.execute deleteSQL
Once you uncomment this line, all the records to inexistent files will be deleted without asking confirmation.

**************************************************
It worked perfectly for me on a production server but:
PLEASE DO NOT USE ON PRODUCTION SERVER BEFORE TESTING!
**************************************************

If you have critics and suggestion, or if some good developer out there wants to improve it and make it more useful for the crowd... well, it would be nice.

thanks

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-13 23:01
by mattg
This script is in the hMailserver 5 scripts section - same goal >> http://www.hmailserver.com/forum/viewto ... 20&t=15514

But thanks for sharing.

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-14 13:32
by este
O yes, I see! That one is in Php and is surely much more powerful.

The only good thing of this little vbscript version is that you can use it as a task on windows.
Currently I am using it with the task manager (in windows 2008). It runs every 4 hours the following actions:
1 - runs kaspersky antivirus that makes a thorough scan of all Data folder and deletes all files with viruses
2 - stops hmailserver service
3 - runs this script (very fast actually, at least on my server)
4 - starts hmailserver service
Hmailserver is off for no more than 4 seconds (every 4 hours). It is an alternative to the "External virus scanner" which always gave me some problems with long queues and cpu overload (maybe someone can consider this option).

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-14 15:46
by ^DooM^
I would recommend using Nico's ClamAV http://hideout.ath.cx/clamav/ as an external scanner, It is an awesome bit of kit, low on resources and easy to setup. It is a much cleaner solution to stopping the mailserver and cleaning up DB records.

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-14 19:20
by este
^DooM^ wrote:I would recommend using Nico's ClamAV http://hideout.ath.cx/clamav/ as an external scanner, It is an awesome bit of kit, low on resources and easy to setup. It is a much cleaner solution to stopping the mailserver and cleaning up DB records.
Thanks Doom. Is it a safe solution, in your experience, also for a production server? Even if it is a free av?

And what do you use as resident protection?

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-15 00:09
by ^DooM^
I use it on my production server yes. I perform no on access scanning at all. All files entering the server from outside (via mail or uploaded via http / flash / ftp) are scanned with clamav.

Re: hMailServer Deleted Files DB Updater

Posted: 2010-10-15 20:16
by este
^DooM^ wrote:I use it on my production server yes. I perform no on access scanning at all. All files entering the server from outside (via mail or uploaded via http / flash / ftp) are scanned with clamav.
Thank you! I am trying it at the moment.. it works very well.