HOW TO: Read database Messages file to check the physical file exists

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
User avatar
jimimaseye
Moderator
Moderator
Posts: 8077
Joined: 2011-09-08 17:48

HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2015-01-13 17:30

The Data Directory Synchronizer , provided as the standard install of Hmailserver, checks physical email files in the data directory are all included in the database. If and email file is found that is not in the database then it provides the option to import the file into the database or delete the file from disk.

However, there is no feature to do the reverse - ie, check that physical files exist for every database record.

This script addresses that.

This script is for databases using an external MYSQL database and utilises both SQL commands and the Com API to ensure things are done whilst maintaining Hmailserver integrity. It should work with other flavours of external SQL database but you would have to make the necessary amendments to paths and executable in the BAT file to suit your system.

It comes in 2 parts:
1, the dedicated VBS script (called 'HMSdatabaseRecordCheck.vbs')
2, A calling BAT file ('HMSdatabaseRecordCheck.bat')

IMPLEMENTATION

A, Save both scripts (with the relevant VBS and BAT extensions) into the same storage directory of your machine where you would require them to run from.
B, Modify the BAT file and enter the necessary parameters at the top of the script (there are 7 to review).

Note: Modify all parameter values in the BAT script where marked " & :<----- INPUT blah ", and delete all whitespace/spaces and the actual " & :<----- INPUT blah " instruction from the line (after your parameter value).

C, Run the BAT script.

The code

First, the VBS script called HMSdatabaseRecordCheck.vbs (that will be called from a BAT file):

Code: Select all

Option Explicit

Const AdminUser = "administrator"

Dim Elog, FSO, DataDir, Mode, HMSpass
Dim MessageCount, ErrorCount,DeletedCount

Mode = 0
MessageCount = 0
ErrorCount = 0
DeletedCount = 0

Dim obApp, oDomain, oAccount
Dim oReadObj, oRead, SearchFile, MessageInputLine, SearchfileFolder, CheckFilePath, FoundIt, AccountAddr, MessageID

HMSpass = WScript.Arguments.Named("HMSpassword")
DataDir = WScript.Arguments.Named("DataDir")
Mode = WScript.Arguments.Named("ActionFlag")

Set FSO = CreateObject("Scripting.FileSystemObject")

Set Elog = CreateObject("hMailServer.EventLog")
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate(AdminUser, HMSpass)

Dim obDatabase, sSQL
Set obDatabase = obApp.Database

Elog.Write ("Start process hm_messages")
Elog.Write ("")
Set oReadObj  = CreateObject("Scripting.FileSystemObject")
Set oRead = oReadObj.OpenTextFile(DataDir&"\MessageList.txt", 1)
Do Until oRead.AtEndOfStream
	MessageInputLine = oRead.ReadLine
	MessageInputLine = Replace(MessageInputLine,vbTab," ")
	FoundIt = 0
	SearchFile = left(MessageInputLine,42)
	MessageID = trim(mid(MessageInputLine,44,10))
	AccountAddr = trim(mid(MessageInputLine,54))
	MessageCount = MessageCount + 1
	If MessageCount > 1 then ' ignores first HEADER row
		oDomain = mid(AccountAddr,InStr(AccountAddr,"@")+1)
		oAccount = left(AccountAddr,InStr(AccountAddr,"@")-1)
		SearchfileFolder = mid(SearchFile,2,2) 
		CheckFilePath = DataDir & "\" & oDomain & "\" & oAccount &"\"&SearchfileFolder&"\"&SearchFile
		If not (FSO.FileExists(CheckFilePath)) then
			Elog.Write ("File not found: " & SearchFile & ", Account: " & AccountAddr & ", MessageID: " & MessageID )
			ErrorCount = ErrorCount + 1
			If (Mode = 1) then
				sSQL = "DELETE FROM hm_messages WHERE messageid = " & MessageID
				Call obDatabase.ExecuteSQL(sSQL)
				DeletedCount = DeletedCount + 1
			End If
		End If
		If MessageCount / 1000 = INT(MessageCount / 1000) Then
			Elog.Write ("Message checked upto: " & MessageCount)
			wscript.echo ("Message checked upto: " & MessageCount)
		End IF
	End If
Loop
oRead.Close()  ' Close input file

Elog.Write ("")
Elog.Write ("Missing eml files : " & ErrorCount)
Elog.Write ("Deleted DB Rows : " & DeletedCount)
Elog.Write ("Rows checked : " & MessageCount - 1)
Elog.Write ("")
Elog.Write ("End process hm_Messages")

wscript.echo ("Missing eml files : " & ErrorCount)
wscript.echo ("Deleted DB Rows : " & DeletedCount)
wscript.echo ("Rows checked : " & MessageCount - 1)
wscript.echo ("See hmailserver_events.log for more detail on the missing files.")
Second the calling bat file called HMSdatabaseRecordCheck.bat :

Code: Select all

@echo off
rem ***************************************************************************************
rem
rem   Modify all parameter values below where marked   " & :<-----  blah blah "  and delete
rem   all whitespace/spaces and the " & :<-----  blah blah "  instruction from the line.
rem   There are 7 of them to review and update.
rem
rem   ENSURE ALL WHITESPACE/spaces are deleted at the end of your parameter value
rem
rem ***************************************************************************************
 
set ScriptPath=C:\Users\Administrator\Documents\BatchFiles      & :<---  Enter the path where your HMSdatabaseRecordCheck.vbs script resides
set HMSpassword=HMSsecretpassword                               & :<---  Enter Hmailserver admin password

rem ------------
rem MYSQL vars
rem ------------

set mysqlexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe"        & :<-- INPUT YOUR MYSQL.exe path/executable here
set mysqluser=root                            & :<-- INPUT YOUR MySQL db user
set mysqlpass=sqlpass                         & :<-- INPUT YOUR MySQL db password
set mysqldb=mymaildb                          & :<-- INPUT YOUR database name

rem ------------
rem Hmail Vars
rem ------------

set HMSProgDir=C:\Program Files (x86)\hmailserver

rem ------------------------------------------------------------------------
rem Do not modify below this line
rem ------------------------------------------------------------------------
cls
color 0e
set ActionFlag=0
set /p ActionFlag="Enter Action flag 0=Report Only, 1=Update (default=0): "

SET INIpath=""
If exist "%HMSProgDir%"\bin set INIpath="%HMSProgDir%"\bin
if %INIpath% == "" CLS & color 0c & echo.&echo. !!! Hmailserver bin directory not found! (%HMSProgDir%\bin) & goto eof
set INIFILE=%INIpath%\hmailserver.ini
FOR /F "eol=; eol=[ tokens=1,2* delims==" %%i in ('findstr /b /l /i DataFolder= %INIFILE%') DO set DataDir=%%~j

rem ' Main, squish sql controls
set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb%
rem ' Main, empty var space
set mysqlexe=
set mysqluser=
set mysqlpass=
set mysqldb=

rem ' Main, create sql script to collect file names and IDs of contents
rem - the following is one line, and [b]should not be word-wrapped[/b]

echo SELECT hm_messages.messagefilename,hm_messages.messageid,SPACE(8-length(hm_messages.messageid)),hm_accounts.accountaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_accounts.accountid;>%DataDir%\buildsql.sql
%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:%DataDir% /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

rem Cleanup
del %DataDir%\MessageList.txt
del %DataDir%\buildsql.sql
echo.
echo.
echo.
color 0a
@echo off
echo FINISHED!
pause
:eof
Notes on speed/timings:
I ran this and it took 17 seconds (on read only check) to check nearly 32900 database records. However, running it a second time, it took TWO seconds (probably due to caching). This should give some idea of timings based on your message count.

With 'Action Flag'=1 (ie, update mode), the time might be a little longer to run if it has records to delete as the Hmailserver API is considerably slow (it is only called on each missing message). Obviously the speed deficit depends on actually how many records it finds that need deleting. (Rough example, allow 1 second per deletion required).

Also, I have the Hmailserver data folder indexed (with windows INDEX OPTIONS). Maybe this adds added benefit in speed file lookups (although I cannot clarify either way).


FOOTNOTE:

Other scripts are also available throughout this forum. Noticeably, there is one here that simply uses SQL commands and not the Com API (but should be equally as quick). Caution should be taken with such methods that do not use the Com API for updates: viewtopic.php?t=19341
Last edited by jimimaseye on 2016-02-23 13:03, edited 7 times in total.
Reason: BAT script mod to auto-search for data directory
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

SniffTheGlove
Normal user
Normal user
Posts: 60
Joined: 2014-08-22 20:43

Re: HOW TO: Read database Messages file to check the physical file exists

Post by SniffTheGlove » 2015-02-17 09:00

Thanks Jim

After running the scripts I hade made a few modifications for my own needs.

I was writing a script in the EventHandlers.vbs to do some specific SPAM handling and messed up by creating 90,000 test messages.
See
So I ran Jim's script above and it worked great however I noticed that at some point hMailServer recreated the deleted messages by creating dummy Undeliverable eml files. I have no idea why it did but presume it is some function of hMailServer.
From: Postmaster
Subject: Message undeliverable
Date: Sat, 14 Feb 2015 08:33:04 +0000

The mail server could not deliver the message to you since the file C:\Program Files\hMailServer\Data\mail.localhost\ham\EE\{EEE653FE-EF6C-4AEE-A124-A4EDD0F1A4DB}.eml does not exist on the server.

The file may have been deleted by anti virus software running on the server.

hMailServer
So I modified the scripst to also seek out these undeliverable messages and delete those as well (physically from disk and database)


HMSdatabaseRecordCheck.bat

Code: Select all

@echo off
cls

set ScriptPath=C:\Users\Me\Documents\Server~1
rem ------------
rem MYSQL vars
rem ------------

set mysqlexe=C:\Bitnami\wampstack\mysql\bin\Mysql.exe
set mysqluser=root
set mysqlpass=********
set mysqldb= mymailserver

rem ------------
rem Hmail Vars
rem ------------

set DataDir=C:\Progra~1\hMailServer\Data

rem ------------------------------------------------------------------------
rem Do not modify below this line
rem ------------------------------------------------------------------------

rem ' Main, squish sql controls
set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb%
rem ' Main, empty var space
set mysqlexe=
set mysqluser=
set mysqlpass=
set mysqldb=

rem ' Main, create sql script to collect file names and IDs of contents
rem - the following is one line, and [b]should not be word-wrapped[/b]

echo Creating SQL to into %DataDir%\buildsql.sql

echo SELECT hm_messages.messageid, hm_messages.messagefilename, hm_accounts.accountaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_accounts.accountid ORDER BY hm_messages.messageid;>%DataDir%\buildsql.sql
echo Running SQL to dump into %DataDir%\MessageList.txt
%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt
echo Running %ScriptPath%\HMSdatabaseRecordCheckScript.vbs /DataDir:%DataDir%
cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheckScript.vbs /DataDir:%DataDir%

rem Cleanup
del %DataDir%\MessageList.txt
del %DataDir%\buildsql.sql

HMSdatabaseRecordCheckScript.vbs

Code: Select all

' ModIfied Script from
' https://www.hmailserver.com/forum/viewtopic.php?f=20&t=27646&p=170991#p170991
'
' This script checks that a physical file exist for every email that exists in the database (hm_messages).
' Set Debug to.....
'      = 0 to NOT provide a verbose output to the Event Log
'      = 1 to provide a verbose output to the Event Log
' Set Mode to 0 to provide a report in the Event Log
' Set Mode to.....
'      = 0 to provide a Full Report only in the Event Log. You must set Debug to 1
'      = 1 to delete the database record If the eml does not exist in the Data directories
'      = 2 to delete the database record and the eml If the eml is a dummy Undeliverable (See below)
'      = 3 to do Mode 1 and Mode 2
'
'Dummy Undeliverable eml file
'      From: Postmaster
'      Subject: Message undeliverable
'      Date: Sat, 14 Feb 2015 08:33:04 +0000
'
'      The mail server could not deliver the message to you since the file C:\Program Files\hMailServer\Data\mail.localhost\ham\EE\{EEE653FE-EF6C-4AEE-A124-A4EDD0F1A4DB}.eml does not exist on the server.
'
'      The file may have been deleted by anti virus software running on the server.
'
'      hMailServer

Option Explicit

Const AdminUser = "administrator"
Const AdminPassword = "***********"

Dim HMS_Database_Record_Check_Search_For_One, HMS_Database_Record_Check_Search_For_Two, Mode
Dim HMS_Database_Record_Check_Prefix
Dim MessageCount, ErrorCount, DeletedCount, UndeliverableCount, Comma_One, Comma_Two, Length_Of_MessageInputLine, File_Size, Debug
Dim obApp, oDomain, oAccount, FSO, objFSO, objFile, oFSO, oReadObj, oRead, EventLog
Dim DataDir, SearchFile, MessageInputLine, SearchfileFolder, CheckFilePath, AccountAddr, MessageID
Debug = 1
Mode = 0
MessageCount = 0
ErrorCount = 0
DeletedCount = 0
UndeliverableCount = 0

HMS_Database_Record_Check_Prefix = "HMS_Database_Record_Check: "
HMS_Database_Record_Check_Search_For_One = "Subject: Message undeliverable"
HMS_Database_Record_Check_Search_For_Two = "does not exist on the server"
DataDir = WScript.Arguments.Named("DataDir")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oReadObj  = CreateObject("Scripting.FileSystemObject")
Set oRead = oReadObj.OpenTextFile(DataDir & "\MessageList.txt", 1)
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate(AdminUser, AdminPassword)
Set EventLog = CreateObject("hMailServer.EventLog")
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Start process")
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Debug = " & Debug)
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Mode = " & Mode)
Do Until oRead.AtEndOfStream
	MessageInputLine = oRead.ReadLine
	MessageInputLine = Replace(MessageInputLine,vbTab,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine: " & MessageInputLine)
	Length_Of_MessageInputLine = Len(MessageInputLine)
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Length: " & Length_Of_MessageInputLine)
	Comma_One = InStr(1,MessageInputLine,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Comma_One: " & Comma_One)
	Comma_Two = InStr(Comma_One + 1,MessageInputLine,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Comma_Two: " & Comma_Two)
	MessageID = trim(mid(MessageInputLine,1,Comma_One - 1 ))
    If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Message ID: " & MessageID)	
	SearchFile = trim(mid(MessageInputLine,Comma_One + 1,Comma_Two - 1 - Comma_One ))
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine File: " & SearchFile)
	AccountAddr = trim(mid(MessageInputLine,Comma_two + 1,Length_Of_MessageInputLine - Comma_Two +1 ))
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Email Address: " & AccountAddr)
	MessageCount = MessageCount + 1
	If (MessageCount > 1) then 
		oDomain = mid(AccountAddr,InStr(AccountAddr,"@") + 1)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Domain: " & oDomain)
		oAccount = left(AccountAddr,InStr(AccountAddr,"@")-1)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Account: " & oAccount)
		SearchfileFolder = mid(SearchFile,2,2)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "SearchfileFolder: " & SearchfileFolder)
		CheckFilePath = DataDir & "\" & oDomain & "\" & oAccount & "\" & SearchfileFolder & "\" & SearchFile
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "CheckFilePath: " & CheckFilePath)
		If (FSO.FileExists(CheckFilePath)) then
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " exists in the Data directories")
			set objFile = objFSO.getFile(CheckFilePath)
			If (objFile.size > 0) then
				File_Size = Round(ObjFile.size / 1024, 2)
				If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " size is " & File_Size & "kb")
				If InStr(oFSO.OpenTextFile(CheckFilePath).ReadAll, HMS_Database_Record_Check_Search_For_One) > 0 and InStr(oFSO.OpenTextFile(CheckFilePath).ReadAll, HMS_Database_Record_Check_Search_For_Two) > 0 Then
					UndeliverableCount = UndeliverableCount + 1
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " is an undeliverable")
					If ((Mode = 2) or (Mode = 3)) then
						obApp.Domains.ItemByName(oDomain).Accounts.ItemByAddress(AccountAddr).Messages.DeleteByDBID(MessageID)
						objFile.DeleteFile CheckFilePath 
					End If
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from hMailServer")
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from Data Directory")
					DeletedCount = DeletedCount + 1
				End If
			Else
				If ((Mode = 2) or (Mode = 3)) then
					objFile.DeleteFile CheckFilePath
				End If
				If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from Data Directory")
				DeletedCount = DeletedCount + 1
			End If
		Else
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " does not exist in the Data directories")
			ErrorCount = ErrorCount + 1
			If ((Mode = 1) or (Mode = 3)) then
				obApp.Domains.ItemByName(oDomain).Accounts.ItemByAddress(AccountAddr).Messages.DeleteByDBID(MessageID)
			End If
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from hMailServer")
			DeletedCount = DeletedCount + 1
		End If
	End If
Loop
oRead.Close()  ' Close input file

If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Missing *.eml files : " & ErrorCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Undeliverable *.eml files : " & UndeliverableCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Deleted DB Rows : " & DeletedCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Rows checked : " & MessageCount - 1)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "End Process")


--
Sniff

hMailServer 5.6 B2145 on Windows 7 Pro Workstation (My Mini Server running Mail,Web and DNS)

9657d3c8658701f14294f87a8ee0d878

User avatar
jimimaseye
Moderator
Moderator
Posts: 8077
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2015-02-17 10:47

cheers sniff

For the avoidance of doubt to readers, the 'undeliverables' that you mentioned 'reappearing' (prompting you to rewrite the script) were NOT a consequence of the original script, and that the original script can be used for the purpose initially described. That is that the original script DOES NOT create these undeliverables so shouldnt worry about running the script. (Readers should note that the cause of this condition prompting the posted rewrite was due to another problem unrelated script poster "sniff" had run earlier that generated thousands of rogue spam mails.)
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
jimimaseye
Moderator
Moderator
Posts: 8077
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2016-02-19 16:01

Original script modified to use SQL delete command instead of ComAPI 'deletebyID' to prevent crash where hm_message records are *orphaned* from any existing account/domain.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
jimimaseye
Moderator
Moderator
Posts: 8077
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2016-02-23 21:41

BAT script modified to auto-search for data directory.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Marziano
New user
New user
Posts: 1
Joined: 2019-03-22 18:15

Re: HOW TO: Read database Messages file to check the physical file exists

Post by Marziano » 2019-03-22 18:20

I'm a little late at the party :-)

Sorry for the possibly apparently dumb question, but: will this script work even if i'm running microsoft SQL server instead of MySQL? Thank you

User avatar
jimimaseye
Moderator
Moderator
Posts: 8077
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2019-03-22 19:58

This script is for databases using an external MYSQL database and utilises both SQL commands and the Com API to ensure things are done whilst maintaining Hmailserver integrity. It should work with other flavours of external SQL database but you would have to make the necessary amendments to paths and executable in the BAT file to suit your system.
.... as i don't know how to.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Post Reply