This article describes how to monitor Microsoft SMTP usage using SQL Server 2000

and XML. I am not sure whether this will be particularly useful to anyone, but

it is relatively elementary and it a good exercise if you want to get familiar

with SQL Server 2000’s basic XML features.

The Windows 2000 Server CD comes with Add-In Components

such as Internet Information Services 5.0. Within IIS 5.0, there is a sub

component you can install called SMTP Server which is the Simple Mail Transfer

Protocol. The Microsoft SMTP Services uses the internet standard simple mail

transfer protocol to transport and deliver messages based on specifications in

Request for Comments (RFC) 821 and RFC 822.

Before attempting to duplicate what is described in this

article, please make sure that you have Microsoft SMTP Service installed. Once

your SMTP Service is installed and running correctly, do the following to enable

ODBC logging of server usage:

First you must set up an ODBC compliant database which will

contain your data.

To set up an ODBC-compliant

database
Create an ODBC-compliant database using a

database program…for our example, please use Microsoft SQL Server 2000. Create a

table in the database that contains the fields listed in the following table.

 Secondly you must select ODBC logging as the preferred

format of logging

1. In Microsoft Management Console,

   select the SMTP virtual server, and then click Properties on the

   Action menu.

2. On the General tab, select

   Enable logging.

3. In the Active log format

   drop-down menu, select ODBC logging

When

you click ODBC properties, you must enter the DSN name of your database, the

name of the table that you just created, and the appropriate credentials

required.

The

first boring part ends there. You have an SMTP Server running and its usage is

being logged.

Now

for the fun part, using SQL Server 2000 to retrieve the usage in XML format and

viewing the data in a nice HTML table.

I

assume the table you created earlier resides on a database on SQL Server 2000

that you have sufficient access to. Let’s start by configuring a virtual

directory for the file that we will use to retrieve our information.

Create a folder somewhere on your machine called “smtp_log”. For ease of use,  I

created mine in C:\Inetpub\ . Inside the smtp_log folder, go ahead and create a

folder called “templates”. This is where we will store the file that has our SQL

query that we will retrieve directly via the browser.

Now

open the “SQL-XML Support in IIS” utility located in the Microsoft SQL Server

2000 Program group.  Right click your default web site and select “New” and then

Virtual Directory.

A new

window should pop up called “New Virtual Directory Properties”

Under the General Tab: Give your virtual

directory a name, I called mine smtp2. Now browse to the local path where your

files will be stored. Select the folder we created earlier called “smtp_log”

Under the Security Tab: Provide the

appropriate credentials required to access the database

Under the Data Source Tab: Select the SQL

Server where your database is located i.e. the database that contains the table

we created earlier. Below that type in the table name of the table that holds

the logging information. Mine is called inetinfo.

Under the Settings Tab: Make sure the

“Allow template queries” is checked

Now,

lets create a virtual name for the directory that will hold our templates. If

you remember, we created a folder within the “smtp_log” folder called

“templates”

Click

on the Virtual Names tab under Virtual Directory properties:

On

the lower half of the window under “Defined Virtual Names” select “New” and

enter the following:

Name:

tpx

Type: Templates

Path: Browse to the “template” folder we created which is located inside the

smtp_log folder we created at the start of this exercise.

That’s all to that. The virtual directory is created and we are all set.

Now,

browse to your templates folder and create a new file and call it totals.xml

In

totals.xml enter the following code:

Please note: I have used XML RAW because XML Auto does not compute Aggregate

functions.

Now

go back to the root folder i.e. smtp_log and create a file called stylesheet.xsl

and enter the following code. This is the style sheet that will interpret the

xml we retrieve:

That’s it. If you have done the above steps correctly you should see the

statistics we requested by entering the following line in your browser:

     http://YourServerName/smtp2/tpx/totals.xml?xsl=stylesheet.xsl

I have included totals.xml and

stylesheet.xml along with this article. Try it out, if you have some issues,

post them on the message board or email me at

kdas@it-enterprises.com