Believe it or not, an Exchange Server SMTP log file is basically ready to import into Microsoft Excel as it is. The trick is to get rid of the garbage at the beginning of the SMTP log file before you start the import process:
- Navigate to the \%SYSTEMROOT%\system32\LogFiles\SMTPSVC1 folder and locate the log file that you want to import.
- Right click on the log file and select Copy.
- Right click on an empty area of the window and select the Paste command. This will cause Windows to make a copy of the SMTP log file that you want to import (it will have a file name similar to "Copy of ex060322.log").
Now that you have made a copy of the SMTP log file that you want to import into Microsoft Excel, you need to convert it to a text file. The SMTP log file is actually already a text file, so all you actually need to do is rename it to make it a little easier to open in Notepad.
- Right click on the file that you have created and select the Rename command.
- Get rid of the "Copy of" portion of the file name, and change the extension to .TXT.
- When Windows asks you if you are sure that you want to change the file extension, click Yes.
- Double click on the text file and Windows will open it in Notepad.
Now it's time to get rid of the text that's standing in our way of importing the file into Microsoft Excel. If you go back and look at Figure A again, you will see that the fourth line of text reads
- #Fields: date time… Date, Time. Everything that comes after that is field names for the data contained in the rest of the log file. Everything prior to the word "Date" is text that Microsoft Excel doesn't know what to do with.
- Delete the first three lines of text, and remove the #Fields: text from the fourth line. After doing so, your log file should look like the one shown in Figure B.
Figure B: This is what the altered SMTP log file should look like.
STEP-BY-STEP GUIDE: HOW TO ANALYZE SMTP LOG FILES IN EXCEL
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.
This was first published in February 2007