Configuring SMTP Logging to an Access Database
The first thing you will need to do is create an Access database, it does not matter what you call the database, for this article I have called it SMTPLOG.MDB. When you create the file I would recommend that you create a directory on your Exchange server and save the database in that directory.
In your database you will need to create a table that contains the following fields, again it does not matter what you call the table, but for this article I have simply called it SMTP.
These are the fields that you will need to create, it is important that you use the exact field names, type and size; attributes you can set in the Access database are optional.
Once you have created the database and saved it to the server you must configure an ODBC System Data Source for the database.
1. On your Exchange server go to Administrative Tools and select Data Sources (ODBC) from the menu, the ODBC Data Source Administrator will now be displayed.
2. Select the System DSN tab and click Add.
3. In the Create New Data Source dialog box, select Microsoft Access Driver (*.mdb), and click Finish.
Figure 1- Create New Data Source
4. The ODBC Microsoft Access Setup dialog box will now appear, enter an appropriate Data Source Name and Description, and click Select.
Figure 2 - ODBC Microsoft Access Setup
5. In the Select Database dialog box, navigate to the Access database that you created and saved earlier, click OK, click OK again to exit the ODBC Data Source Administrator.
Once you have configured the ODBC Data Source you will need to configure your Exchange server to send its SMTP logging to the Access database, to do this:
1. Open Exchange System Manager, and open the Properties for your SMTP Virtual Server, on the General tab, ensure that the Enable Logging checkbox is selected and that ODBC Logging is selected from the Active Log Format dropdown list.
Figure 3 - SMTP Virtual Server Properties
2. Click on Properties, the ODBC Logging Properties dialog box will now open, in the ODBC Data Source Name (DSN) field enter the name that you used when you created your ODBC DSN, in our example the name used was smtp, in the Table field enter the name of the table in Access that you want to log to, in our example the name used was smtp, you will also need to provide a username and password for an account that has write access to the Access database, click OK to close the ODBC Logging Properties dialog box, and then click OK to close the SMTP Virtual Server Properties dialog box.
Figure 4 - ODBC Logging Properties
3. In order for the changes you have made to the SMTP Virtual Server to take effect, you must stop and start your SMTP Virtual Server, once this has completed, logging should now take place in the Access database.
Once the data has been logged to the Access database, you can use Access to produce any number of queries and reports to help you monitor your SMTP traffic.