Overview
Reporting functionality is included with all the MailEssentials product editions and is not a separately licensed feature. Reports can always be generated when any part of the product is licensed. MailEssentials will, however, stop logging new email data in the reporting database when the Evaluation expires, User limit reached, or if the Subscription becomes inactive.
In all cases, the Reporting tool will allow reports to be generated on the data that has already been gathered, even when the above conditions are encountered.
If for any reason users are unable to generate reports and spam digests, this article provides some troubleshooting guidelines on possible reasons and resolutions.
Introduction
Firebird and Microsoft SQL Server are the supported database management systems that can be configured as the backend for MailEssentials reporting data.
The next section looks at some of the common issues with the reporting framework and how to troubleshoot and resolve issues when they are encountered.
We will look at the following components that affect the correct operation of the Reporting module:
Description
Microsoft Message Queues (MSMQ)
MailEssentials Reporting module leverages Microsoft Message Queuing (MSMQ) technology which enables MailEssentials to cache data that needs to be written asynchronously to the reporting database.
Message Queuing is implemented as a Windows service (Service name: MSMQ) which needs to be always running for the various reports and notifications to be generated.
Every email processed by MailEssentials is sent to the messaging queue then processed by GFI MailEssentials Backend service, which writes the corresponding report entry to the reporting database.
Message Queues can be accessed by navigating to Start > Run > compmgmt.msc (Enter) then to Services and Applications > Message Queuing > Private Queues. The relevant queues for used for Reporting are named gfimereportq@*.
It is normal that during peak hours with high mail flow, the number of messages in the queues starts to increase, but return to zero after a short time.
However, there may be situations when either the queues have filled up (contain the maximum number of messages allowed by Windows MSMQ), are malfunctioning or the information is not needed any longer. For these situations, the queues can be purged by expanding the Queue messages folder, right-clicking, selecting All Tasks, then Purge, as seen in the below screenshot:
The queues will be recreated automatically in case any of them malfunctions and has to be deleted manually.
Reporting data flow
This section describes how reporting data finds its way into the reporting database. A good understanding of this data flow is an important step in troubleshooting issues related to the reporting database:
Reporting data flow process:
- Exchange or IIS delivers copies of emails to MailEssentials through the Exchange 2010/2013/2016/2016/2019 Agents or IIS SMTP Sinks.
- The two scanning engines (GFIScanS for AntiSpam and GFIScanM for EmailSecurity) receive, scan and write the resulting data to the MSMQ (queues). Access to MSMQ is handled by MiddleMan module. The message is submitted to one of 9 reporting queues in RR (round-robin) manner.
- The MEC.ReportingActivity checks the root folder for config.mdb to retrieve the database settings and the config.mdb from the Antispam folder to check if Reporting is enabled. If enabled, the MiddleMan module reads the above-mentioned queue and forwards it to the MailEssentials.Reporting module. MEC.ReportingActivity uses 50 threads by default for processing MSMQ messages.
- The MailEssentials.Reporting through the ReportWriter then writes the data from the Recovery queue first and then from the normal queues to the reporting database, which can either be the default Firebird database or the user-selected SQL Server database
- Should the ReportWriter encounter any errors while writing the data to the reporting database (for example the SQL server is not reachable), the Reporting Activity will notice and stop sending the data that returned the error to Reporting. The data will remain in the Recovery Queue for later processing when the database becomes available.
The possible reasons the ReportWriter can throw an error include:
- Schema Check Failure: Every time a database schema check fails, a notification email will be sent to the administrator. Schema checks are run when the service is started or when a refresh context is required.
- SQL Connection Failure: When the SQL connection is dropped, a notification email will be sent to the administrator. This notification is sent every time the connection is dropped after it was successfully opened. This way, the notification won’t be sent every time the connection is being retried.
- Firebird Database reaches 7GB: a notification email will be sent to the administrator with a warning that the database is reaching its limit when Firebird database is in use. When this happens, it is recommended to roll over manually by starting with a new database or enabling auto-purge.
Data Migration from older versions
Older versions of MailEssentials prior to version 2014 R2 Build 20140627 that became End of life as of June 2015 were utilizing a Microsoft Access database for storing reporting data. The older versions have known issues that were resolved in subsequent versions and it is recommended to always be running the latest versions and recommended patches. The latest stable build can be obtained from the GFI Upgrade center.
If upgrading from older versions using Access database for report data, MailEssentials provides a tool for importing reporting data from Microsoft Access databases into the new Firebird database.
The migration utility named ReportsMigration.exe can be found under GFI\MailEssentials\Backend\bin\ and is executed via the command prompt by navigating to the given location and providing the path to the Access database that stores the old reporting data:
Example:
C:\Program Files (x86)\GFI\MailEssentials\Backend\bin> ReportsMigration.exe "C:\reportsSmall.mdb"
