Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

By Kunal Das,

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

 

Total article views: 3544 | Views in the last 30 days: 1
 
Related Articles
FORUM

Where is the SQL Server project template?

Where is the SQL Server project template? I need to create a CLR stored procedure!

ARTICLE

Creating templates for use in BIDS (SQL Spackle)

Learn how to create and use templates in BIDS in this short SQL Spackle article.

ARTICLE

SQL Server Profiler Part 2: Custom Templates and Saving Data

In Part 2 of our series we will examine how to create custom templates and how to save collected tra...

FORUM

Trace using Tuning template turn server unavailable

Trace using Tuning template turn server unavailable

BLOG

Using Templates

Templates are one of those really handy tools that most DBAs I know of have either never heard of or...

Tags
basics    
monitoring    
sql server 7    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones