interface
Chapter 21 Message Audit Database
This chapter describes the audit database, which provides a central repository for information about messages passing through your system. It enables you to monitor and manage areas of congestion and where errors have occurred, from a remote location.
21.1
What does it do?
Information about the messages passing through your system is stored in a Database Management System (DBMS). This is an SQL database. You can use the same database to hold information from multiple Isode MTAs, giving you an overview of all the traffic through your messaging systems.
The information in the database can then be viewed and managed using MConsole or web-based applications (Webapps) which use a browser as the User Interface. The applications provide the following features.
• Message Statistics (Webapps only)
This application displays statistical information about all of the MTAs providing data to the audit database. You can view summary information or perform more sophisticated searches based on specified recipients or viruses.
• Message Viewing (MConsole only)
MConsole is capable of accessing the MTA message archive, Quarantine or MTA queue, and display the content of the message. Please note that the content of the message is never stored in the Message Audit Database. If the message is not in the Quarantine or the MTA Queue, it will only be available if you have set up message archiving. • Message Tracking
This application can be used to follow messages through your system. You can search using most message attributes; for example, subject or originator.
• Acknowledgement Tracking (MConsole only)
This application can be used to correlate messages and acknowledgements (reports and read receipts) in your system. You can search using time period; message type; acknowledgement status.
• Message Quarantine
Message Quarantine is used to manage messages that have been quarantined (usually by an MTA used to filter messages that are spam or contain viruses or other malware). Information describing the configuration necessary to identify and quarantine suspected spam messages is in Chapter 20, Content Checking.
With the Message Quarantine application, messages that have been placed in quarantine can be viewed, deleted or released for subsequent delivery to the intended recipient. Other Webapps are available which provide facilities for managing Internet Message Users and browsing the X.500/LDAP Directory. The former are described in Chapter 6,
Managing Internet Messaging Users and the M-Box Administration Guide. The latter are described in the M-Vault Administration Guide.
21.2
How does it work?
The MTA records audit information in audit log files (sometimes known as the stat log). The contents of these files are read by the Log Parsing daemon, which populates an SQL database with the information contained in the audit logs.
Information from the Message Audit Database can be viewed in two ways:
• using a standard web browser such as Internet Explorer or Firefox to connect to the database client Webapp (a Tomcat servlet) which reads information from the database. • Using the Isode MConsole which has the following views:
• Message Tracking View which allows information about transfer and delivery of messages from the Message Audit Database to be displayed.
• Quarantine View which allows information about quarantine from the Message Audit Database to be displayed.
• Acknowledgement View which allows correlation information about messages and their acknowledgements held in the Message Audit Database to be displayed.
Figure 21.1, “The audit database (overview)” shows the relationships between the various components and the way that information is passed between them. The MTA, MTA log files and the Log File Parser must be physically located on the same machine as the MTA, as information is passed between them using simple file access. The other components (the audit database, Tomcat and the web browser) can be located on different systems if required.
Figure 21.1. The audit database (overview)
If you intend to run the Quality of Service Notification without the Quarantine Service, the setup is more simple as in Figure 21.2, “The audit database (Quality of Service)”:
Figure 21.2. The audit database (Quality of Service)
21.2.1
Quarantine overview
When content checking (see Section 20.5.3, “Placing messages in quarantine”) results in a message being placed in quarantine, it is given a status of Quarantined in the Audit DB. Using the Message Quarantine web application or MConsole Quarantine View, this can be changed either to Freed – for the message to be resubmitted for delivery to the original recipients – or Marked for deletion.
The Quarantine Manager Daemon periodically reads MTA user information from the directory, generating reports with a list of all new quarantined messages (for each user) and sends notification emails at scheduled times.
Notification messages contain links to the IMAdmin-Personal (Personal Internet Messaging Administrator) web application, allowing the notification recipient to release quarantined messages.
• Messages with a status of Freed are resubmitted to the MTA. The daemon changes their status to Resubmitted or, if a problem is encountered, Error on resubmission. • Messages with a status of Marked for deletion are deleted from quarantine and
their status is changed to Deleted.
Note: Only operators, not end-users, can currently delete messages from quarantine.
21.2.2
Quality of service overview
Messages which are handled by the MTA can request Delivery Reports and/or Read Receipts. These can be positive (the MTA has delivered the message or the Recipient has read the message), or negative (the message failed to be delivered, or the recipient has declined to acknowledge receipt of the message).
If you have a requirement to be notified that messages which are expected to have been acknowledged by certain time, this can be provided by the Quality of Service Notification daemon. Configuration of this service is described in Section 21.11.1, “Quality of Service Daemon configuration”.
21.3
What is needed to run the Audit DB
A Basic system requires:
• An SQL DataBase Management System. Supported products are: • PostgreSQL
• Microsoft SQL Server 2008
• HSQLDB is provided in embedded form as part of the Isode product set for evaluations. • Java.
PostgreSQL and Microsoft SQL Server are not provided by Isode and must be obtained separately.
For notification the following is also required:
• Tomcat – you must install Java before Tomcat. (For evaluations an embedded version of Tomcat is included. However any changes to WebApps will be lost when the Isode packages are upgraded, so this may be unsuitable for actual deployments).
• A web browser (such as Firefox or Internet Explorer)
The release notes for each package give full installation instructions for these third-party packages. You can accept all default values unless specifically instructed otherwise.
Note: When installing PostGreSQL on Windows, you will be asked if you want to connect from hosts other than local hosts. Answer Yes.
21.4
Configuring the software
The core component of the Message Audit Database system is an SQL DBMS (Database Management System) where the Audit Database is stored and processed.
The DBMS has to be configured first, as all other Audit Database components are dependent on it.
21.4.1
HSQLDB
Setting up the embedded SQL database has for evaluations takes place automatically when setting up Tomcat.
21.4.2
PostgreSQL
21.4.2.1
Initialisation and configuration
After successfully installing PostgreSQL, you now need to create an appropriate configuration to make it suitable for Audit DB use. Instructions are provided separately for UNIX and Windows systems.
21.4.2.1.1 Configuring PostgreSQL 9 on UNIX
1. Initialise PostgreQSL. This is carried out automatically by the installer. The database is started.
2. Edit /var/lib/pgsql/data/pg_hba.conf to grant access for specific hosts to audit database. Adding:
host auditdb postgres 127.0.0.1/32 trust
enables local applications (and daemons) to access the database as a "postgres" user. To enable other hosts simply add more entries with appropriate IP addresses. For example:
host auditdb postgres 127.0.0.1/32 trust host auditdb postgres 172.16.0.134/32 trust
The /32 parameter means network mask - changing this gives access to address group (network) rather than single host. For example:
host auditdb postgres 127.0.0.1/32 trust host auditdb postgres 172.16.0.134/16 trust
The above allows any system with the IP address 172.16.*.* to connect. 3. Restart PostgreSQL to use the new settings
# /etc/init.d/postgresql restart
4. An important maintenance task is the periodic vacuuming of the database. The easiest way to do this is by turning on the auto-vacuum daemon which is disabled by default. To enable, ensure the following configuration entries are set in
/var/lib/pgsql/data/postgresql.conf
autovacuum = on
1. To create a database you must switch to user, to the PostgreSQL user (usually created when the Postgres package was installed).
# su postgres
(You may need to set the postgres user password when you are root, as you will be asked for it at this point)
# createdb -E utf8 auditdb
1. To delete the audit database you have to stop all applications and daemons accessing it.
2. Next as the postgres user run the following command:
dropdb auditdb
If you need to clear the Audit Database the most effective way is just to drop and recreate the database as described above.
21.4.2.1.2 Configuring PostgreSQL 9 on Windows
1. Install PostgreSQL
During installation you will be asked for the database superuser password, which will be required later to access the database.
2. Edit the pg_hba.conf to grant access for specific hosts to audit database. Adding:
host auditdb postgres 127.0.0.1/32 trust
enables local applications (and daemons) to access the database as a "postgres" user. To enable other hosts simple add more entries with appropriate IP addresses. For example:
host auditdb postgres 127.0.0.1/32 trust host auditdb postgres 172.16.0.134/32 trust
/32 parameter means network mask - changing this gives access to address group (network) rather than single host.
3. Reload the PostgreSQL configuration to use the new settings. This can be accessed by selecting Programs and then PostgreSQL from the Start menu.
4. An important maintenance task is the periodic vacuuming of the database. The easiest way to do this is by turning on the auto-vacuum daemon which is disabled by default. To enable, ensure the following configuration entry is set in postgresql.conf
autovacuum = on
1. Open the pgAdmin III tool from Start → Programs → PostgreSQL
2. Connect to the database (You will be asked for the password that you provided during installation)
3. Go down the tree and click on the Databases item with your right mouse button and select New Database....
4. Enter auditdb as a database name.
5. On the Definition tab, ensure that the selected encoding is UTF8 and click the OK button.
1. To delete an audit database you have to stop all applications and daemons accessing it first.
2. Open the pgAdmin III tool from Start → Programs → PostgreSQL