Controlled External Account Fetching

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Controlled External Account Fetching

Post by Bill48105 » 2010-05-15 06:35

Kudos & Thanks go out to:
Inspired by MrChrH: http://www.hmailserver.com/forum/viewto ... =7&t=18295
Script originally posted as Kick_hMail by Bladeinger: http://www.hmailserver.com/forum/viewto ... 14&t=12828
martin for hMail & everyone involved here

By default hMail external account checking happens with up to 15 concurrent connections with no apparent way to change that. MrChrH's issue is that his ISP limits to 4 POP3 connections so hMail sometimes tried more than that. My concern is I wanted more control over the timing of the checks, in particular to run the checks in order with a user definable delay between each. Luckily hMail has the DownloadNow method which can be used to tell it to "go!" but unfortunately hMail seems to provide no way to know if the check is running or when it completes using COM. Because of this you need to set the delay high enough to reduce chance of concurrent connections (hMail should still enforce the 15 limit but I'm not sure) but one side-effect is that the script could be waiting a long time after the download actually completed when it could be checking the next account instead. I plan to update this script to use ODBC connect to hMail data in MySQL so that a scan can be done for 'locked' (in-use) external accounts which will provide a means to watch for activity & move ahead based on that. For now this version should provide some better control of when external account checking happens.

NOTE: This does not get run by hMail in EventHandlers.vbs Instead you want to run it from CMD prompt or use Windows Task Scheduler to run it on a preset schedule.

WARNING: This script does not check to see if it is already running or not so if you run it again (or the schedule comes due) before the script completes you could end up running more than 1 at once! (Actually for me I got permission denied when trying to run more than once so maybe it's not an issue..) Odds are I'll fix that in next version but for now be sure to schedule scans far enough apart to not overlap or the script could be modified to be enclosed in endless loop instead then you'd just run once & it'd loop forever until you killed it.

IMPORTANT: For this to be useful you really need to set the "Minutes between downloads" for all external accounts to a pretty high number (perhaps 720 to ensure a daily check if script is not running or 9999 to make it wait 7 days). I am tempted to add in future version option to check current setting & change it to a custom value because that would probably be useful for people with tons of accounts..

Code: Select all

'Script originally posted as Kick_hMail by Bladeinger: http://www.hmailserver.com/forum/viewtopic.php?f=14&t=12828
'Updated by Bill48105 to enable logging, skip disabled/inactive domains/accounts & added delay between each
'Future version to include hMail database scanning to determine if time to move to next external account
'
'*** BE SURE TO SET USER/PASS & other settings below up until END OF SETTINGS SECTION! ***
'
' Original text & comments retained. New added as needed
' Script to force hMailServer to fetch mail from external accounts
' Feel free to use and modify this script for your own purposes
' Only active Domains, Accounts and external Accounts are affected by this script
' (thanks to martin!), so the "Active"-Flags of the objects have not to be considered
'(Contrary to original comments the script looped through diabled domains & accounts. It no longer does)

'User pass to login to hMail ** REQUIRED **
Dim hMailUser: hMailUser = "Administrator"
Dim hMailPass: hMailPass = "*** ADMIN PASSWORD ***"

'Debug logging True=enabled False=Disabled
Dim ExtFetchDebug: ExtFetchDebug = True

'Be sure to set the path to where to save logs if enabled above!! IT MUST EXIST if logging enabled
Dim StrSave: StrSave = "C:\Scripts"

'NOTE: Only Mode 1 supported at this time
'Mode 1 - Wait only - No ODBC Connector required - Just set FetchDelay below
'Mode 2 - ODBC Connector REQUIRED - Waits for no locked external accounts before proceeding
'Dim ExtFetchMode: ExtFetchMode = 1
'http://dev.mysql.com/downloads/connector/odbc

'Number of seconds to wait after starting download before moving to next account
Dim ExtFetchDelay: ExtFetchDelay = 30

'END OF SETTINGS SECTION

Dim obApp
Set obApp = CreateObject("hMailServer.Application")

if ExtFetchDebug Then Dim FSO: Set FSO = CreateObject("Scripting.FileSystemObject")
if ExtFetchDebug Then Dim FSOStream: Set FSOStream = FSO.OpenTextFile(StrSave & "\" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-ExtFetch.log",8,True)
if ExtFetchDebug Then FSOStream.WriteLine Now & " ExtFetch Starting"
if ExtFetchDebug Then FSOStream.WriteLine Now & " Authenticating with hMail.."
Call obApp.Authenticate(hMailUser, hMailPass)
Dim obExtAccount
Dim obAccount
Dim obDomain
' first, stepping through each domain
intDomainCount = obApp.Domains.Count
if ExtFetchDebug Then FSOStream.WriteLine Now & " Domains: " & intDomainCount
for i = 0 to (intDomainCount - 1)
	Set obDomain = obApp.Domains.Item(i)
	if ExtFetchDebug Then FSOStream.WriteLine Now & "  Domain: " & i+1 & " - " & obDomain.Name
	if obDomain.Active Then
		'then stepping through accounts of current domain	
		intAccountCount = obDomain.Accounts.Count	
		if ExtFetchDebug Then FSOStream.WriteLine Now & "   Accounts: " & intAccountCount
			for j = 0 to (intAccountCount - 1)
				Set obAccount = obDomain.Accounts.Item(j)
				if ExtFetchDebug Then FSOStream.WriteLine Now & "    Account: " & obAccount.Address
				if obAccount.Active Then
					'at last, stepping through each external account of current domain-account and force fetch	
					intExtAccountCount = obAccount.FetchAccounts.Count
					if ExtFetchDebug Then FSOStream.WriteLine Now & "     External accounts: " & intExtAccountCount
					for k = 0 to (intExtAccountCount - 1)
						Set obExtAccount = obAccount.FetchAccounts.Item(k)
						if ExtFetchDebug Then FSOStream.WriteLine Now & "      External account: " & obExtAccount.Name
						if obExtAccount.Enabled Then
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      Starting download.."
							obExtAccount.DownloadNow()
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      Waiting " & ExtFetchDelay & " seconds.."
							WScript.Sleep(ExtFetchDelay * 1000)
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      Waiting complete, proceeding"
						Else
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      External account disabled, skipping"
						End If
					Next
				Else
				if ExtFetchDebug Then FSOStream.WriteLine Now & "     Account disabled, skipping"
			End If
		Next
	Else
		if ExtFetchDebug Then FSOStream.WriteLine Now & "    Domain disabled, skipping"
	End If
	if ExtFetchDebug Then FSOStream.WriteLine Now & "  Domain done"
Next
if ExtFetchDebug Then FSOStream.WriteLine Now & " ExtFetch ending"
'close the log if enabled
if ExtFetchDebug Then FSOStream.Close
Attachments
ExtFetch.zip
(1.65 KiB) Downloaded 488 times
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Version 2! Controlled External Account Fetching

Post by Bill48105 » 2010-05-15 20:19

This updated version adds ability for script to scan the hMail database to see how many external download connections are active & pauses starting more if the pre-defined limit is reached.

ODBC needs to be installed for mysql connection to work:
http://dev.mysql.com/downloads/connector/odbc

If you prefer to not use ODBC use version 1 which just automatically waits a set amount of seconds between next Download.

Code: Select all

'V2 adds connection to hMail database to check current external account accounts & limit accordingly
'Script originally posted as Kick_hMail by Bladeinger: http://www.hmailserver.com/forum/viewtopic.php?f=14&t=12828
'Updated by Bill48105 to enable logging & skip disabled/inactive domains/accounts
'
'*** BE SURE TO SET USER/PASS & other settings below up until END OF SETTINGS SECTION! ***
'
' Original text & comments retained. New added as needed
' Script to force hMailServer to fetch mail from external accounts
' Feel free to use and modify this script for your own purposes
' Only active Domains, Accounts and external Accounts are affected by this script
' (thanks to martin!), so the "Active"-Flags of the objects have not to be considered
'(Contrary to original comments the script looped through diabled domains & accounts. It no longer does)

'User pass to login to hMail ** REQUIRED **
Dim hMailUser: hMailUser = "Administrator"
Dim hMailPass: hMailPass = "*** PASSWORD ***"

'Debug logging True=enabled False=Disabled
Dim ExtFetchDebug: ExtFetchDebug = True

'Be sure to set the path to where to save logs if enabled above!! IT MUST EXIST if logging enabled
Dim StrSave: StrSave = "C:\Scripts"

'Be sure to change Server, Database, User & Password to proper values.
Dim MySQLConnectionString: MySQLConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mail;User=MySQLUser; Password=MySQLPass;"

'Max number of active external account connections before proceeding
Dim ExtFetchMaxConn: ExtFetchMaxConn = 5

'Number of seconds to wait between database connection check
Dim ExtFetchDelay: ExtFetchDelay = 10

'END OF SETTINGS SECTION

Dim MySQLcn, MySQLrs, SqlCmd
Set MySQLcn = CreateObject("ADODB.Connection")
Set MySQLrs = CreateObject("ADODB.Recordset")
'This is query looking for # of locked (active) external accounts downloading
SqlCmd = "SELECT count(*) FROM hm_fetchaccounts where falocked = " & Chr(34) & "1" & Chr(34)
MySQLcn.ConnectionString = MySQLConnectionString
MySQLcn.Open

Dim obApp
Set obApp = CreateObject("hMailServer.Application")

if ExtFetchDebug Then Dim FSO: Set FSO = CreateObject("Scripting.FileSystemObject")
if ExtFetchDebug Then Dim FSOStream: Set FSOStream = FSO.OpenTextFile(StrSave & "\" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-ExtFetch.log",8,True)
if ExtFetchDebug Then FSOStream.WriteLine Now & " ExtFetch Starting"
if ExtFetchDebug Then FSOStream.WriteLine Now & " Authenticating with hMail.."
Call obApp.Authenticate(hMailUser, hMailPass)
Dim obExtAccount
Dim obAccount
Dim obDomain
' first, stepping through each domain
intDomainCount = obApp.Domains.Count
if ExtFetchDebug Then FSOStream.WriteLine Now & " Domains: " & intDomainCount
for i = 0 to (intDomainCount - 1)
	Set obDomain = obApp.Domains.Item(i)
	if ExtFetchDebug Then FSOStream.WriteLine Now & "  Domain: " & i+1 & " - " & obDomain.Name
	if obDomain.Active Then
		'then stepping through accounts of current domain	
		intAccountCount = obDomain.Accounts.Count	
		if ExtFetchDebug Then FSOStream.WriteLine Now & "   Accounts: " & intAccountCount
			for j = 0 to (intAccountCount - 1)
				Set obAccount = obDomain.Accounts.Item(j)
				if ExtFetchDebug Then FSOStream.WriteLine Now & "    Account: " & obAccount.Address
				if obAccount.Active Then
					'at last, stepping through each external account of current domain-account and force fetch	
					intExtAccountCount = obAccount.FetchAccounts.Count
					if ExtFetchDebug Then FSOStream.WriteLine Now & "     External accounts: " & intExtAccountCount
					for k = 0 to (intExtAccountCount - 1)
						Set obExtAccount = obAccount.FetchAccounts.Item(k)
						if ExtFetchDebug Then FSOStream.WriteLine Now & "      External account: " & obExtAccount.Name
						if obExtAccount.Enabled Then
							'Send mysql query
							MySQLrs.Open SqlCmd, MySQLcn, 3
							' wait here until less connections then continue
							Do while CInt(MySQLrs(0).Value) >= ExtFetchMaxConn
								'close the previously opened query
								MySQLrs.Close
								if ExtFetchDebug Then FSOStream.WriteLine Now & "      Limit reached, waiting " & ExtFetchDelay & " seconds.."
								WScript.Sleep(ExtFetchDelay * 1000)
								MySQLrs.Open SqlCmd, MySQLcn, 3
							Loop
							'close the previously opened query
							MySQLrs.close
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      Less than " & ExtFetchMaxConn & " connected, proceeding.."
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      Starting download.."
							obExtAccount.DownloadNow()
						Else
							if ExtFetchDebug Then FSOStream.WriteLine Now & "      External account disabled, skipping"
						End If
					Next
				Else
				if ExtFetchDebug Then FSOStream.WriteLine Now & "     Account disabled, skipping"
			End If
		Next
	Else
		if ExtFetchDebug Then FSOStream.WriteLine Now & "    Domain disabled, skipping"
	End If
	if ExtFetchDebug Then FSOStream.WriteLine Now & "  Domain done"
Next
if ExtFetchDebug Then FSOStream.WriteLine Now & " ExtFetch ending"
'close the log if enabled
if ExtFetchDebug Then FSOStream.Close

'Close mysql connection
MySQLcn.Close
Attachments
ExtFetchV2.zip
(1.9 KiB) Downloaded 483 times
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

MSSQLCE UPDATE - Controlled External Account Fetching

Post by Bill48105 » 2010-05-16 07:34

OK super good news! I found simple changes to v2 to make it work with MSSQL CE :D
Change this:

Code: Select all

Dim MySQLConnectionString: MySQLConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mail;User=MySQLUser; Password=MySQLPass;"
To this:

Code: Select all

Dim MySQLConnectionString: MySQLConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Progra~1\hMailServer\Database\hMailServer.sdf;SSCE:Database Password=UnencryptedPassword;SSCE:Max Database Size=4000;SSCE:Flush Interval=10;"
*** Be sure to set correct path to your sdf file & your unencrypted password found using the instructions at:
http://www.hmailserver.com/documentatio ... t_to_mssql
** That came straight from hMail itself so thanks martin

Also change this:

Code: Select all

SqlCmd = "SELECT count(*) FROM hm_fetchaccounts where falocked = " & Chr(34) & "1" & Chr(34)
To this:

Code: Select all

SqlCmd = "SELECT count(*) FROM hm_fetchaccounts where falocked = 1"
** The only reason I had the chr(34)'s was because on mysql i kept getting empty results without them but seems MSSQL doesn't like them so they need to be removed. The odd thing is now testing with mysql they don't seem to be needed there either.. Got me.. lol
Hope someone finds it useful. :)
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

Post Reply