How it Works

Database population script    (Non WMI Version) (for WMI version Click here)

From feedback I've had from people while this article has been available on the web, sometimes its necessary to be able to do a bulk import of old (or archived) transaction logs into the reporting database so they can be reported on. For this type of bulk import using WMI is not that applicable and some form of direct access to the log file is a better approach. Out of this feedback the following script was developed that will firstly enumerate all the tracking log files in a directory and then open each one and import the contents into the database without needing to use WMI. This means this script and database can be run offline on a disconnected server or workstation.

The first part of the code  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.

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 creates an object that represents the directory where the log files are located and then using a for-next loop it enumerates each file within this log file directory and calls the importtodb function for each log file.

set f = fso.getfolder("c:\logdir")
Set fc = f.Files
For Each file1 in fc
importtodb(file1.path)
next
wscript.echo "Done"

The Next section of code is a function that is designed to except one parameter which is the name and path of the message tracking logs file. This function then opens up this file goes though the file one line at a time and using the Split function to separates the tracking log file data. Only certain rows are used hence not every array element is put to use. 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 date/time value in the log 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)

function importtodb(fname)
set wfile = fso.opentextfile(fname,1,true)
for i = 1 to 5
	wfile.skipline
next
nline = wfile.readline
do until wfile.AtEndOfStream
if instr(nline,"	") then
inplinearray = Split(nline, "	", -1, 1)
if inplinearray(8) = "1020" or inplinearray(8) = "1028" then
	ClientIP = inplinearray(2)
   	if (isnull(ClientIP)) then ClientIP = "N/A"
   	Entrytype = inplinearray(8)
    	if (isnull(Entrytype)) then Entrytype = "N/A"
    	Subject = inplinearray(18)
   	if (isnull(Subject)) then Subject = "N/A"
   	RecipientAddress1 =  inplinearray(7)
    	if (isnull(RecipientAddress1)) then RecipientAddress1 = "N/A"
    	RecipientCount = inplinearray(13)
    	if (isnull(RecipientCount)) then RecipientCount = "N/A"
    	SenderAddress = inplinearray(19)
    	if (isnull(SenderAddress)) then SenderAddress = "N/A"
    	size1 = inplinearray(12)
    	if (isnull(size1)) then size1 = "N/A"
	datearray = split(inplinearray(0),"-",-1,1)
	timearray = split(inplinearray(1),":",-1,1)
	odate = dateserial(datearray(0),datearray(1),datearray(2))
	otime = timeserial(timearray(0),timearray(1),0)
	odate = dateadd("h",toffset,cdate(odate & " " & otime))

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 & "','" & replace(SenderAddress,"'","") & "','" _
	& replace(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)
end if
end if
nline = wfile.readline
loop
wfile.close
set wfile = nothing
end function

Note this script is only for  bulk inserts into the database of tracking log data if you looking for ongoing incremental inserts use the incrpopt.vbs which is explained on this page.

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 Directory where the log files are located you need to make sure that only message tracking logs file are located in this directory or this will cause the script to error out.

set f = fso.getfolder("c:\logdir")

Once these two references have been verified your ready to run the script, as I mentioned before this version of the script is for non-wmi importation of that tracking logs into the database.

							Download Files