Using SQL Server 2000 and XML to Monitor the Usage of your SMTP Server

,

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.

 

Field Name

Data Type

ClientHost

Varchar(255)

Username

Varchar(255)

LogTime

datetime

Service

Varchar(255)

Machine

Varchar(255)

ServerIP

Varchar(50)

ProcessingTime

Integer

BytesRecvd

Integer

BytesSent

Integer

ServiceStatus

Integer

Win32Status

Integer

Operation

Varchar(255)

Target

Varchar(255)

Parameters

Varchar(255)

 

 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:

 

<root>

<sql:query

xmlns:sql='urn:schemas-microsoft-com:xml-sql' >

Select Sum(processingtime) AS Total_Processing_Time,Sum(bytesrecvd) as

Total_Bytes_Received,Sum(bytessent) as Total_Bytes_Sent From inetlog FOR XML

RAW

</sql:query>

  </root>

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:

 

 

 

 

 

<?xml version='1.0'

encoding='UTF-8'?>

 <xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl'

>

  

      <xsl:template match =

'*'>

         <xsl:apply-templates

/>

      </xsl:template>

      <xsl:template match =

'row'>

         <TR>

            <TD

align="center"><xsl:value-of select =

               '@Total_Processing_Time'

/> seconds</TD>

           <TD

align="center"><xsl:value-of select =

               '@Total_Bytes_Received'

/> bytes</TD>

            <TD

align="center"><xsl:value-of select =

               '@Total_Bytes_Sent'

/> bytes</TD>

        </TR>

       </xsl:template>

      <xsl:template match =

'/'>

       <HTML>

 

 

          <HEAD>

             <STYLE>

body {font-family: Tahoma;

font-size: 8pt}

td {font-family: Tahoma;

font-size: 8pt}

</STYLE>

         </HEAD>

          <BODY>

             <TABLE 

width="70%" border="1" align="center" bordercolor="#000000" cellpadding="0"

cellspacing="0">

 

            <TR><TH colspan='3'>

               SMTP Server

Usage Stats

            </TH></TR>

             <TR><TH >Total

Processing Time</TH>

                <TH>Total

Bytes Received</TH>

             <TH>Total Bytes

Sent</TH>

             </TR>

            <xsl:apply-templates

select = 'root' />

     

            </TABLE>

         </BODY>

      </HTML>

   </xsl:template>

 

</xsl:stylesheet>

 

 

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

 

Rate

Share

Share

Rate