Step 3: Analyze Exchange Server SMTP traffic bandwidth consumption in Microsoft Excel

Step-by-Step Guide: How to analyze Exchange Server SMTP log files using Microsoft Excel -- part 3 of 4.

You can use your SMTP log files for to determine how much bandwidth your SMTP traffic is consuming. Keep in mind though that SMTP data is logged on a virtual server basis. So if you have more than one virtual server, you will need to combine the data from all your virtual servers to measure the true impact of SMTP traffic.

There are a couple of different ways that you can analyze the amount of bandwidth being consumed by SMTP data:

  • Look at a cumulative total for the day (assuming that you have Exchange Server configured to create a new log file each day).

  • Cross reference the bandwidth consumed by the time of day so that you can see what your peak traffic periods for the day are.

Calculate the total daily amount of Exchange Server SMTP data

Figuring the total amount of SMTP data that has passed through the virtual server is simple. You just have to combine the totals from the SC-BYTES and CS-BYTES fields.

In Figure G, the SC-BYTES field occupies column N. The CS-BYTES field is in column O. But for the sake of simplicity, I will be ignoring the CS-BYTES field for the remainder of this tutorial, since my example log file doesn't have any data in this field.

If you wanted to perform calculations on the CS-BYTES field for your own log file, the technique would be identical to the one that I outline for the SC-BYTES field.

Figure G
Figure G: In this log file, the SC-BYTES field occupies column N.

Granted, my example SMTP log file is pretty short, but this technique will work regardless of the SMTP log's length. If you want to know the total number of bytes logged by the SC-BYTES column, you just have to create a simple formula that tallies the column.

Notice in Figure G that the actual data for the SC-BYTES field starts in cell N2. The spreadsheet has a total of 20 rows, so the last cell of the SC-BYTES field containing data is cell N20.

Now that you know the first and last cells of data used by the SC-BYTES field, you can calculate the total. To do so, move the cursor to an empty cell and enter the following formula: @sum(n2..n20). The cell's contents will change to reflect the total number of bytes that were logged in the SC-BYTES field, as shown in Figure H.

Figure H
Figure H: A simple formula reveals that 842 bytes were logged in the SC-BYTES field.

Analyze Exchange Server SMTP traffic based on time of day

To look at the traffic based on the time of day:

  1. Click on the letter above the SC-BYTES field (in my case N) to select the entire column.

  2. Hold down the CTRL key and click the letter above the Time column (in my case this is the letter A) to select that column. Both the Date column and the SC-BYTES column should now be selected.

  3. Select the Insert -> Chart command.

  4. You will now be prompted to select a chart type. Select a line graph and then click Next three times, followed by Finish, to view a graph that indexes your SMTP data with the time of day.

Figure I
Figure I: You can graph SMTP traffic patterns based on time of day.

If you look at the graph shown in Figure I, you will see that it isn't very useful in its present state. There is a separate entry on the graph for every transaction. The entire period of graphed activity is only lasts eight tenths of a second, and yet there are 19 entries on the graph.

This is only a problem because we are looking at such a small slice of time. If we were to look at a graph for a full day's activity, each transaction would still have its own entry on the graph, but the transactions would be pushed much closer together. This would make it a lot easier to see at a glance what the busiest parts of the day are.

Figure J shows a graph containing the same data used in the previous figures, plus a little extra data collected since that time. Notice how the labeling becomes less frequent. You can tell in the graph that there was more activity in the 5:02 time frame than there was in the 2:15 time frame, because of the amount of space that the 5:02 entries take up horizontally on the graph.

Figure J
Figure J: You can tell how busy a period of time is by the amount of horizontal space that time frame occupies on the graph.


STEP-BY-STEP GUIDE: HOW TO ANALYZE SMTP LOG FILES IN EXCEL

 Home: Introduction
 Step 1: Convert the Exchange Server SMTP log file to a text file
 Step 2: Import the Exchange Server SMTP log file into Excel
 Step 3: Analyze SMTP traffic bandwidth consumption in Excel
 Step 4: Related resources from SearchExchange.com

ABOUT THE AUTHOR:   
Brien M. Posey, MCSE
Brien M. Posey, MCSE, is a Microsoft Most Valuable Professional for his work with Exchange Server, and has previously received Microsoft's MVP award for Windows Server and Internet Information Server (IIS). Brien has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal Web site at http://www.brienposey.com.

Dig Deeper on Exchange Server setup and troubleshooting

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchSQLServer

SearchEnterpriseDesktop

SearchVirtualDesktop

Close