Database delivery log

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Database delivery log

Post by martin » 2009-01-20 13:05

The delivery log script is used to log every delivered email message to the database. External software can then connect to the hMailServer database and access the message information. This external software also must empty the delivery log database table from time to time, since you may run out of disk space otherwise. Also, if the table is never emptied, the database will become big and backups and other operations on the database will become increasingly slower.

The feature existed in hMailServer 4.x and previous versions but was removed in v5.0 since a lot of users enabled the functionality without knowing what it actually did, which caused problems to these users. The script in this thread re-implements the database delivery log feature.

The zip file contains a script which will extract information from messages as they are received and store this information in the database. The zip also contains SQL scripts to create the tables for the supported databases. If you've upgraded from v4, you will already have the tables in your database.
Attachments
DeliveryLog.0.1.zip
(1.96 KiB) Downloaded 2923 times

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Re: Database delivery log

Post by westdam » 2009-01-21 13:41

is it possible to choose another database to store all this info?
like a .ini file to configure the db and or the username/password?

so this wont hung or mess the hmailserver database.

i'm only asking if it's possible..

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Database delivery log

Post by martin » 2009-01-21 13:44

Not using this script in its current form. The script could be modified so that it opens a connection to another database and inserts the data into that one. The current version of the script assumes that you want to insert into the hMailServer database.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Re: Database delivery log

Post by westdam » 2009-01-21 18:27

mmm, ok i've understand. so next question it's quite related.
will this change to your script slow-down the server?
i mean, hmailserver had to write into 2 database and in the delivery log there's a lot of stuff to handle.

i think will affect the performance a bit..


anyway i like your idea martin. :)

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Database delivery log

Post by martin » 2009-01-21 18:54

So what you're asking is "will doing more take longer time than doing less?", and the answer to that question is yes.

cristian_pop
New user
New user
Posts: 4
Joined: 2009-05-05 17:37

Re: Database delivery log

Post by cristian_pop » 2009-05-11 01:11

Have tried the script and it does not work. (on version 5.1)

I've updated CreateTablesMYSQL file to create the 2 new tables (and it does).
I've updated EventHandlers file with the script you posted.
I've enabled scripts, saved and reload the scripts, in hMS.

Whenever a user gets a new msg, info about it can be seen in hm_messages but there is no info in hm_deliverylog table.
And the reason I`m posting is because I need the content of 'subject' and 'body' fields to be dynamically updated in the database.

I have also tried with an older version of hMS (4.4.2) and no extra scripting, and still no success.
Please give me a hint.

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2009-05-11 07:22

cristian_pop wrote:Have tried the script and it does not work. (on version 5.1)
The script works for me

Ver 5.1.1 341

Is there any error log?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Khurram
Normal user
Normal user
Posts: 38
Joined: 2007-12-02 09:21

Re: Database delivery log

Post by Khurram » 2009-06-02 08:51

I believe this script is for incoming messages only. Am I right? If so, is there a script for outgoing messages?

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2009-06-02 09:35

Mine logs all message, both incoming, and outgoing
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Onni
New user
New user
Posts: 6
Joined: 2008-04-24 09:42

Re: Database delivery log

Post by Onni » 2009-08-08 15:02

I cant get it to work.

I upgraded from 4.x to 5.2 and put the *.txt file in the Events dir.
Changed the admin password in the file and hit reload scripts.
When that didnt work I changed it to *.vbs but that didnt work either.

Any suggestions?

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Database delivery log

Post by ^DooM^ » 2009-08-08 15:36

Copy and paste all that code to your EventHandler.vbs file that already exists for hMail.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

Onni
New user
New user
Posts: 6
Joined: 2008-04-24 09:42

Re: Database delivery log

Post by Onni » 2009-08-08 22:18

Thank you kindly! That works ;)

luci
Normal user
Normal user
Posts: 70
Joined: 2008-02-29 14:29
Location: Romania

Re: Database delivery log

Post by luci » 2009-09-15 17:23

There is a simple way to get a message SENT status ?

I am thinking to create such script in PHP and integrate it in hmailadmin. This would list all incoming/outgoing messages of the logged in user with a status indicator. If the message failed to be sent, a explanation message taken from the log would inform the user about the problem.

This will inform the user about the status of a certain message. (especially useful for sent messages).

The only way I can think of it is to parse the daily SMTP log.
Can you help me with a few infos about the tab delimited fields? Can I uniquely identify a message ?
Radical Image Optimization Tool developer
Project Manager at CRIOSWEB

anuragsharma9
New user
New user
Posts: 1
Joined: 2013-01-14 21:05

Re: Database delivery log

Post by anuragsharma9 » 2013-06-30 19:50

Dear Sir

I am facing a problem in delivery log. i got so many information in this script but i also want to add eml file location where it finally save . my purpose is i can catch if a problem find in mail system


Please help if you have any solution for it.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Database delivery log

Post by percepts » 2013-06-30 22:56

you will need to modify the code yourself.

User avatar
Nime
Normal user
Normal user
Posts: 116
Joined: 2009-03-12 11:50
Contact:

Re: Database delivery log

Post by Nime » 2013-07-11 07:46

I wrote a query which one reports the usage statistics. It counts the daily outgoing mails by accounts.

Code: Select all

SELECT
        deliveryaccount,
        DATEDIFF(d,[deliverytime], getdate()) AS [ago],
        COUNT(DISTINCT [hm_deliverylog].[deliveryid]) AS [session]
        ,COUNT( [hm_deliverylog_recipients].[deliveryid]) AS [recipients]
 
 
FROM [hm_deliverylog]
 
LEFT JOIN [hm_deliverylog_recipients] ON [hm_deliverylog_recipients].[deliveryid] = [hm_deliverylog].[deliveryid]
 
WHERE DATEDIFF(d,[deliverytime], getdate()) < 3
--and [deliveryaccount] = 'info@domain.com'
 
GROUP BY
        [deliveryaccount],
         DATEDIFF(d,[deliverytime], getdate())
         
ORDER BY [ago], [recipients] DESC
I've renamed the table names & included the necessary indexes to make query execution way faster.

Image

jollero
New user
New user
Posts: 7
Joined: 2014-05-27 12:46

Re: Database delivery log

Post by jollero » 2014-06-20 09:28

Hi,

This thread seems to be quite old. But has anyone been able to make this work on the latest version of hmailserver? I was not able to.

Thanks a lot!

Regards,
Joseph

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-06-20 10:48

It should work on latest version

Are you getting any errors or is there an error log generated?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Steviebones
New user
New user
Posts: 18
Joined: 2014-07-16 21:42

Re: Database delivery log

Post by Steviebones » 2014-07-22 16:57

I am trying to fire your script so I can get a handle on editing it... Whenever the script fires its returns this error

"Script Error: Source: Microsoft VBScript compilation error - Error: 800A0400 - Description: Expected statement - Line: 6 Column: 0 - Code: End Settings"

below is a copy of the script file as loaded...

My VBS is rusty to say the least but I copied the script as downloaded... what am I missing?

Code: Select all

Option Explicit

Settings
Private Const setting_username = "Administrator"
Private Const setting_password = "********"
End Settings


'   Sub OnClientConnect(oClient)
'   End Sub

'   Sub OnSMTPData(oClient, oMessage)
'   End Sub

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oMessage)
   
End Sub


'   Sub OnDeliveryStart(oMessage)
'   End Sub

'   Sub OnDeliverMessage(oMessage)
'   End Sub

'   Sub OnBackupFailed(sReason)
'   End Sub

'   Sub OnBackupCompleted()
'   End Sub

'   Sub OnError(iSeverity, iCode, sSource, sDescription)
'   End Sub

'   Sub OnDeliveryFailed(oMessage, sRecipient, sErrorMessage)
'   End Sub

'   Sub OnExternalAccountDownload(oFetchAccount, oMessage, sRemoteUID)
'   End Sub

Sub CreateDeliveryLogEntry(oMessage)
   Dim obDatabase
   Set obDatabase = GetDatabaseObject
   
   Dim sFrom, sFilename, sTime, sSubject, sBody
   
   sFrom = oMessage.From
   sFilename = oMessage.Filename
   sTime = GetTimestamp(obDatabase)
   sSubject = oMessage.Subject
   sBody = oMessage.Body
   
   sFrom = Mid(sFrom, 1, 255)
   sFilename = Mid(sFilename, 1, 255)
   sSubject = Mid(sSubject, 1, 255)
   
   if (Len(sBody) > 1000000) Then
      sBody = Mid(sBody, 1, 1000000)
   End If
   
   sFrom = Escape(obDatabase, sFrom)
   sFilename = Escape(obDatabase, sFilename)
   sSubject = Escape(obDatabase, sSubject)
   sBody = Escape(obDatabase, sBody)
   
   Dim sSQL
   sSQL = "insert into hm_deliverylog (deliveryfrom, deliveryfilename, deliverytime, deliverysubject, deliverybody) values ('" & sFrom & "', '" & sFilename & "', " & sTime & ", '" & sSubject & "', '" & sBody & "')"
   
   dim iID
   iID = obDatabase.ExecuteSQLWithReturn(sSQL)
   
   dim obRecipients
   set obRecipients = oMessage.Recipients
   
   dim iRecipientCount
   iRecipientCount = obRecipients.Count
   
   dim i
   for i = 0 to iRecipientCount -1
      dim sRecipientAddress
      sRecipientAddress = obrecipients.Item(i).Address
      sRecipientAddress = Escape(obDatabase, sRecipientAddress)
      
      sSQL = "insert into hm_deliverylog_recipients (deliveryid, deliveryrecipientaddress) values (" & CStr(iID) & ", '" & sRecipientAddress & "')"
      
      Call obDatabase.ExecuteSQL(sSQL)
   Next
End Sub

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

	Call obApp.Authenticate(setting_username, setting_password)
   
   Set GetDatabaseObject = obApp.Database
End Function

Function Escape(obDatabase, value)

   value = Replace(value, "'", "''")

   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         value = Replace(value, "\", "\\")
      Case 3: ' PGSQL
         value = Replace(value, "\", "\\")
   End Select
   
   Escape = value
End Function

Function GetTimestamp(obDatabase)
   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         GetTimestamp = "NOW()"
      Case 2: ' MSSQL
         GetTimestamp = "GETDATE()"
      Case 3: ' PGSQL
         GetTimestamp = "current_timestamp"
      Case 4: ' SQL CE
         GetTimestamp = "GETDATE()"
   End Select
End Function

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-07-22 23:13

In the original script the words 'settings' and 'End Settings' were commented out

They are NOT VB commands

They should be commented out.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 3576
Joined: 2006-08-21 15:38
Location: Denmark

Re: Database delivery log

Post by SorenR » 2014-08-18 20:29

Anyone else seeing this error?

Code: Select all

"ERROR"	2232	"2014-08-17 19:58:42.149"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x98\x84 h...' for column 'deliverybody'
It appears to be some sort of Emoticon used in emails from Twitter...
http://apps.timwhitlock.info/emoji/tables/unicode
https://www.drupal.org/node/1314214 Reported 3 years ago and aparently fixed 2 months ago - MySQL 5.5 InnoDB but aparently not without issues.

I use Martins original script (first post in thread) and did the quick solution and Googled :wink:

PHP have a very nice function that I found people to use
mixed preg_replace ( mixed $pattern , mixed $replacement , mixed $subject [, int $limit = -1 [, int &$count ]] )
and after some Googling I found a VBScript version. https://gist.github.com/wqweto/6569291
I only include two of the functions from the above into my EventHandler.vbs

Code: Select all

   Private Function preg_init(find_re)
      Set preg_init = New RegExp
      With preg_init
         .Global = True
         If Left(find_re, 1) = "/" Then
            Dim pos: pos = InStrRev(find_re, "/")
            .Pattern = Mid(find_re, 2, pos - 2)
            .IgnoreCase = (InStr(pos, find_re, "i") > 0)
            .Multiline = (InStr(pos, find_re, "m") > 0)
         Else
            .Pattern = find_re
         End If
      End With
   End Function

   Function preg_replace(find_re, replace_arg, text)
      preg_replace = preg_init(find_re).Replace(text, replace_arg)
   End Function
and I modified the original script here... to replace the emoticons with "--".

Code: Select all

   ...
   ...
   sBody = oMessage.Body
   If (Len(sBody) > 1000000) Then
      sBody = preg_replace("'/[\x00-\x08\x10\x0B\x0C\x0E-\x19\x7F]'" & "." &_
                           "'|[\x00-\x7F][\x80-\xBF]+'" & "." &_
                           "'|([\xC0\xC1]|[\xF0-\xFF])[\x80-\xBF]*'" & "." &_
                           "'|[\xC2-\xDF]((?![\x80-\xBF])|[\x80-\xBF]{2,})'" & "." &_
                           "'|[\xE0-\xEF](([\x80-\xBF](?![\x80-\xBF]))|(?![\x80-\xBF]{2})|[\x80-\xBF]{3,})/S'", "--", sBody)
      sBody = Mid(sBody, 1, 1000000)
   End If
   sFrom = Escape(obDatabase, sFrom)
   ...
   ...
I now get Twitter updates in the log.
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

aoverton07
New user
New user
Posts: 25
Joined: 2014-03-28 00:56

Re: Database delivery log

Post by aoverton07 » 2014-10-15 03:39

Thanks for the script! I have successfully implemented the delivery log for the latest build of hm (5.4).

****For any new visitors, the only thing that I had trouble with (mostly due to my own lack of knowledge lol) was making sure that you use "Administrator" for the setting_username constant, and NOT your account name that you would use to send an email.

One question:

I noticed it saves the filename for each message, yet the files are not present in the hmailServer/Data folder. They are deleted as soon as the message is delivered. Is there a way to keep these files around?

I realize I should use caution here because saving all those files can potentially take up a lot of disk space, but for my application it is necessary to save these files so that we can extract the attachments if need be.....

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-10-15 05:03

aoverton07 wrote:They are deleted as soon as the message is delivered. Is there a way to keep these files around? .
Your mail clients connect via POP3. IMAP keeps messages on the server.

If you want to keep all messages, then active the mirror feature
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Post Reply