Message Tracking Logs Report Script and Database                                Download Files

Updated 07/2004 added script to import logs into database without using WMI

Message Tracking Logs in Exchange are a very powerful diagnostic and trending tool but they are often overlooked because although the message tracking center in Exchange is a good tool its presentation of data is very basic and 1 dimensional. This article gives a method that I've used to analyze and report on this log data. Some of the questions I'm trying to answer with this type of analysis is how much mail is being sent Internally and Externally, How much email is being sent and received by each of the users, who are my top senders and receivers, what days are most busiest and which time period are the most busiest. All these questions (and more) can be answered from the data in the Message Tracking Logs but not in the format its stored in. Message Tracking Logs are primarily a diagnostic tool and this is how they work at defined points of the message transfer process in Exchange different events are logged into the message tracking logs which is a tab separated flat file (for a full list of these event ids have a look here). For the purpose of the report not every event is of interest in fact with the logic I've used only 2 event ID's are of use to me. Event ID 1020 is logged when a message is sent over the wire by SMTP. This means this event usually gets logged when mail is being transferred though a server (in the case of a connector server) or email is being sent to a user externally or on another mail server (Internally multi server environments). Event ID 1028 is logged when the Store driver successfully delivered a message (logged by store driver). This means this event gets logged when a message is delivered from either a external or internal source to a users mailbox.

Stored in a flat file format however this information is not very flexible so the first part of this report is a VBS script that exports that data stored in the Message tracking logs (via WMI) and inserts that data into a Access Database. Once the data is in the database we can then perform a lot of different types of aggregations using a combination of "group by" SQL queries, VBA functions and sub queries. To present this data I've created a front-end to this database using some ASP pages and a java graphing applet. The reports show a breakdown of how much external and Internal mail was sent by date (screen1), The next screen shows a breakdown of how much each user (that received email) has sent and received though this server (screen2). The third screen shows another breakdown of how much each user has sent and received this time in a trend format showing the trend across the last 30 days (screen 3). The fourth and fifth screens show the TOP 20 external destination and sender email address (screen 4). The sixth screen uses a bar graph java applet to show a visual representation of the amount and size of email that has been sent externally and internally over the selected date period (Screen6). The seventh screen also uses a bar graph to represent the size and amount of email that is sent and received by hour. (Screen 7). Each screen has a supported drill down screen that allows you to view the segment of the log that has be aggregated (Screen8).

Requirements

The main requirement for this script to work is to have Message Tracking Logging enabled on the server (this is disabled by default on Exchange 2000/2003) see this Qbase article for more detail. I recommend you also enable subject tracking when you do this as this makes the tracking log data a lot more meaningful. Before you enable messaging tracking think carefully about the amount of email traffic your server gets and where your going to store the log files. The other requirement is that it requires WMI and Exchange 2000 SP2 or greater (works okay on 2003) as this provides the WMI interfaces that the script requires.

A Warning

You should never run any scripts on a production server unless you know what your going to be in for and this script is no acceptation.  Depending on the amount of traffic your server gets and your log retention settings (the default is 7 days) your message tracking logs could be a considerable size. If your message tracking logs are a considerable size this script will take a very long time to run and it can cause excessive processor utilization if your machine is already sufficiently loaded.  The full population script I've included has no time bounds if you do have a considerable amount of logs to process you should only run the script when your server is not busy and keep a close eye on the utilization if you do. The other option which may be safer in this regard is to use the incremental population script which is designed to be run every 15 minutes through a  scheduled task which populates the database with the last 15 minutes of data. Both these population scripts don't have to be run on your actually Exchange server as WMI supports connection over the network from another machine.

How it Works

Database population script  ( WMI Version)  (For non-WMI Version click here)

As I mentioned above  I've included two versions of the database population script the first fullpopt.vbs is designed to get all the data available from all available message tracking log files. The second script incpopt.vbs is designed just to get the last 15 minutes of log data this is to allow for live monitoring of data during the day. With both these scripts I've used the samples from the Exchange SDK as the base and added in some ADO and processing code.

The first part of the code sets up some connection parameters for WMI, opens up the database and confronts the first challenge of dealing with Message tracking logs which is that the logs are stored in GMT time. To work out what the GMT time offset is (how many hours we are ahead or behind GMT) the ActiveTimeBias registry entry is used this registry entry lists the number of minutes  UTC is offset from local time. This time offset value is then used later in the code to covert the date\time retrieved from the log to local time.

strComputerName = "Your Server"
Const cWMINameSpace = "root/MicrosoftExchangeV2"
Const cWMIInstance = "Exchange_MessageTrackingEntry"
set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
Set Cnxn1 = CreateObject("ADODB.Connection")
strCnxn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Trackinglog.mdb;"
Cnxn1.Open strCnxn1

The next part of the code then queries WMI using the ExecQuery method and using a "where" SQL predicate that limits the entries returned to eventid 1020 and 1028. The rest of the code deals with any Nulls there may be which will cause errors during the database insert there is also some code that handles converting the WMI date/time value that you get back (which is a string) into a proper date/time value. The date/time value is then converted from GMT to local time using the dateadd function and the gmtoffset value that was determined at the start of the script. (note I've only tested this for Sydney time)

For each objExchange_MessageTrackingEntry in listExchange_MessageTrackingEntries
    for i = 1 to objExchange_MessageTrackingEntry.RecipientCount
   	ClientIP = objExchange_MessageTrackingEntry.clientip
   	if (isnull(ClientIP)) then ClientIP = "N/A"
   	Entrytype = objExchange_MessageTrackingEntry.entrytype
    	if (isnull(Entrytype)) then Entrytype = "N/A"
    	Subject = objExchange_MessageTrackingEntry.Subject
   	if (isnull(Subject)) then Subject = "N/A"
   	OriginationTime = objExchange_MessageTrackingEntry.OriginationTime
    	if (isnull(OriginationTime)) then
		OriginationTime = "N/A"
	else
		odate = dateadd("h",toffset,cdate(DateSerial(Left(OriginationTime, 4), Mid(OriginationTime, 5, 2), Mid(OriginationTime, 7, 2)) _
	               & " " & timeserial(Mid(OriginationTime, 9, 2),Mid(OriginationTime, 11, 2),Mid(OriginationTime,13, 2))))
	end if
   	RecipientAddress1 = objExchange_MessageTrackingEntry.RecipientAddress((i-1))
    	if (isnull(RecipientAddress1)) then RecipientAddress1 = "N/A"
    	RecipientCount = objExchange_MessageTrackingEntry.RecipientCount
    	if (isnull(RecipientCount)) then RecipientCount = "N/A"
    	Subject = objExchange_MessageTrackingEntry.Subject
    	if (isnull(Subject)) then Subject = "N/A"
    	SenderAddress = objExchange_MessageTrackingEntry.SenderAddress
    	if (isnull(SenderAddress)) then SenderAddress = "N/A"
    	size1 = objExchange_MessageTrackingEntry.size
    	if (isnull(size1)) then size1 = "N/A"

The Last section of code performs the database insert and also it handles converting the date/time value into a serial date format which I find is more flexible when it come to aggregating and reporting. The subject field is first truncated if necessary to 254 characters so it can be inserted into a text field in the access database and also any ' characters are removed because they cause problems with the database insert.

wtowrite = "('" & condate(odate) & "','" & formatdatetime(odate,4) & "','"  & ClientIP & "','"  & EntryType & "','" _
& RecipientCount & "','" & SenderAddress & "','" & RecipientAddress1 & "','" & left(replace(subject,"'"," "),254) _
& "','" & size1 & "')"
sqlstate1 = "INSERT INTO TrackingLogRaw ( [Date], [Time], [client-ip], [Event-ID], NoRecipients, [Sender-Address]," _
& "[Recipient-Address], [Message-Subject], [total-bytes] ) values " & wtowrite
Cnxn1.Execute(sqlstate1)
next
Next
df = msgbox("done")
Cnxn1.close
set objWMIExchange = nothing
set listExchange_MessageTrackingEntries = nothing

The Other version of this script which is included in the download is basically the same as the one above except for the front section. Because I only want the last 15 minutes of the log file I have to expand the "where" predicate in my WMI query to include a start and a end date. When your querying WMI based on date you must specify it in a strict format so this code handles firstly getting the GMT time that is 15 minutes later then current GMT time and then converting it into a string that can be used in the query.

set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
dtListFrom = DateAdd("n", minTimeOffset, now())
dtListFrom = DateAdd("n",-15,dtListFrom)
strStartDateTime = year(dtListFrom)
if (Month(dtListFrom) < 10) then strStartDateTime = strStartDateTime & "0"
strStartDateTime = strStartDateTime & Month(dtListFrom)
if (Day(dtListFrom) < 10) then strStartDateTime = strStartDateTime & "0"
strStartDateTime = strStartDateTime & Day(dtListFrom)
if (Hour(dtListFrom) < 10) then strStartDateTime = strStartDateTime & "0"
strStartDateTime = strStartDateTime & Hour(dtListFrom)
if (Minute(dtListFrom) < 10) then strStartDateTime = strStartDateTime & "0"
strStartDateTime = strStartDateTime & Minute(dtListFrom)
if (Second(dtListFrom) < 10) then strStartDateTime = strStartDateTime & "0"
strStartDateTime = strStartDateTime & Second(dtListFrom) & ".000000+000"

Installing and Running the population script

There are two hard coded references in these scripts you need to be aware of the first reference is to the database being located on the d:\ of the server. (Erstwhile the database needs to be located there or the script won't function)

strCnxn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Trackinglog.mdb;"

The Second is the name of the server which you must change to your own server name

strComputerName = "Your Server"

Once these two references have been verified your ready to run the script, as I mentioned before there are two versions of the script one is the run-once population script (fullpopt.vbs) which will capture all the data that is stored in the message tracking logs and insert it into the database, (if you run this any  more then once you end up with duplicates in your database). The second version (incpopt.vbs) is designed to capture data during the day and can be run via a scheduled task (eg schedule for every 15 minutes which corresponds to the way the query is designed to work getting the last 15 minutes form the message tracking logs).

 

Next Page -Report Database

							Download Files