Exchange 2003 WMI Logon and Audit Tracking Database            Download Files

Effectively auditing logons to an Exchange Server has never been a very easy task although logon information has always been available in Exchange Administrator and System Manager this information was not easily retrieved programmatically unless you used Extended MAPI. With Exchange 2003 a new WMI class has been made available that does allow you to retrieve this type of information using a simple VB Script.  Getting a snapshot of this information however useful doesn't allow you to answer that many questions, but once you can start storing this information in a database you can then start to answer the following type of questions by aggregating and  reporting on this data.

Who's logged on Currently via Outlook or OWA ?
Who's logged on Yesterday via Outlook or OWA ?
What version of Outlook is being used by each of my users ?
How many of my users are using Cache mode or Online mode ?
Historically logon information about who was logged on when. ?
Who are my top 20 users by number of logons and time ?
What is the busiest time for Logons during the day ?
How many Logon's and Logoff's by day ?
Which accounts are logging on to multiple mailboxes? Which account logged onto which mailbox?

Click here to see some screenshots

As you can see once you start to analysis this data it can become pretty useful (especially from a security auditing standpoint), so what this article does is presents one method you can use to collect the data via a WMI vbs script, store that data in a Access database and then report on this information using ASP pages.

How it works

If you have looked at the Logons via Exchange System Manager you will  notice that the information in this table is limited in it usefulness in the format you can retrieve it in. Also there is no way by default to find when new people have logged on or logged off all the table really does is presents a snapshot of the current logons. What the Access database does is it uses 3 tables to determine and store new Logon and Logoff events. The first two tables are snapshot tables which are designed to store the current logons in logonsnap1 and the previous logons in logonsnap. What the population script does is populates the logonsnap1 table and also executes "action queries" which manipulates the data in these two tables. The first action query that runs deletes the current records in the logonsnap table, the next action query then copies any records in logonsnap1 table to logonsnap table, the next action query then deletes all the records in logonsnap1. After this the script populates the logonsnap1 table with the latest logon data. Once all these process's have successfully completed and both snapshot tables are populated another action query is executed that identifies new logons by matching data in both tables based on Mailboxname, UserName and Logontime. A similar query is also run to identify any logoffs and the results of both of these queries are then written to the logonsraw table which is then used to construct different aggregated views of the data to be used in the Report pages.

IMAP4 and POP3 logons

I don't use these protocols (I always disable them on my servers) so I haven't designed the scripts or reports to deal with them, from some quick testing I've done POP3 logons don't register (the logons don't appear long enought) and IMAP4 logons will register and appear in the report as outlook logons (the reports could be reset to filter on this type of logon).

Database population script

The database population script performs a variety of functions, the first of these functions is database management because the script is creating and deleting data all the time due to the snapshot process the size of the database continues to grow because Access doesn't reuse deleted record space until a compaction is done. So the first part of the script once a day performs a compaction of the database using the Jet Replication Object (JRO).

if hour(now()) = 1 and minute(now())> 30 then
	Set Engine = CreateObject("JRO.JetEngine")
	set fso = createobject("Scripting.FileSystemObject")
	if Fso.FileExists("d:\logontracktemp.mdb") then
		Set f2 = fso.getfile("d:\logontracktemp.mdb")
		f2.Delete
	end if
	Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\logontrack.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\logontracktemp.mdb"
	set f1 = fso.getfile("d:\logontracktemp.mdb")
	f1.copy("d:\logontrack.mdb")
        set Engine = nothing
	set f1 = nothing
	set fso = nothing
        set f2 = nothing
end if 

The next part of the code sets up some connection parameters for WMI, opens up the database and confronts the first challenge of dealing with Exchange logons which is that the logon date\time and last operation date\time are stored in UTC time. To work out what the UTC 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 via WMI to local time.

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

The next part of the code executes 3 "Action Queries" to prepare the database for the snapshot process to capture new logons and logoffs. The first action query performs a delete of all the current records in the logonsnap table, the second action query copies all the records from logonsnap1 table to the logonsnap table and the third action query then deletes all the records in logonsnap1 table.

Cnxn1.Open strCnxn1
SQL1 = "DELQ2"
Cnxn1.Execute(SQL1)
SQL1 = "APPQ"
Cnxn1.Execute(SQL1)
SQL1 = "DELQ"
Cnxn1.Execute(SQL1)

The Next part sets-up and performs a WMI Semi-Synchronous query of the Exchange_Logon class using a WQL Where predicate to limit the query to only return Outlook and OWA logons.   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 Logon and last operation time from GMT into Local time and splits it into separate date and time columns. The last section does a filter to ignore any of the NT AUTHORITY\SYSTEM logons and then performs a database insert.

strWinMgmts = "winmgmts:{impersonationLevel=impersonate}!//" & strComputerName & "/" & cWMINameSpace
Set objWMIExchange =  GetObject(strWinMgmts)
Set listExchange_LogonEntries = objWMIExchange.ExecQuery("Select * FROM Exchange_Logon Where ClientVersion <> 'SMTP' AND ClientVersion <> 'OLEDB' ",,48)
For each objExchange_LogonTrackingEntry in listExchange_LogonEntries
    	MailboxDisplayName = objExchange_LogonTrackingEntry.MailboxDisplayName
   	if (isnull(MailboxDisplayName)) then MailboxDisplayName = "N/A"
	ServerName = objExchange_LogonTrackingEntry.ServerName
   	if (isnull(ServerName)) then ServerName = "N/A"
	ClientVersion = objExchange_LogonTrackingEntry.ClientVersion
   	if (isnull(ClientVersion)) then ClientVersion = "N/A"
	ClientMode = objExchange_LogonTrackingEntry.ClientMode
   	if (isnull(ClientMode)) then ClientMode = "N/A"
	Hostaddress = objExchange_LogonTrackingEntry.Hostaddress
   	if (isnull(Hostaddress)) then Hostaddress = "N/A"
	LoggedOnUserAccount = objExchange_LogonTrackingEntry.LoggedOnUserAccount
   	if (isnull(LoggedOnUserAccount)) then LoggedOnUserAccount = "N/A"
	LogonTime = objExchange_LogonTrackingEntry.LogonTime
   	LogonTime = dateadd("h",toffset,cdate(DateSerial(Left(LogonTime, 4), Mid(LogonTime, 5, 2), Mid(LogonTime, 7, 2)) & " " & timeserial(Mid(LogonTime, 9, 2),Mid(LogonTime, 11, 2),Mid(LogonTime,13, 2))))
	LastOperationTime = objExchange_LogonTrackingEntry.LastOperationTime
   	LastOperationTime = dateadd("h",toffset,cdate(DateSerial(Left(LastOperationTime, 4), Mid(LastOperationTime, 5, 2), Mid(LastOperationTime, 7, 2)) & " " & timeserial(Mid(LastOperationTime, 9, 2),Mid(LastOperationTime, 11, 2),Mid(LastOperationTime,13, 2))))
	MacAddress = objExchange_LogonTrackingEntry.MacAddress
   	if (isnull(MacAddress)) then MacAddress = "N/A"
        if LoggedOnUserAccount <> "NT AUTHORITY\SYSTEM" then
        	ltoinsert = "('" & MailboxDisplayName & "','" & ServerName & "','" & ClientVersion & "','" & ClientMode & "','" & hostaddress & "','" & LoggedOnUserAccount & "','" & condate(logonTime) & "','" & formatdatetime(LogonTime,4) & "','" & condate(LastOperationTime) & "','" & formatdatetime(LastOperationTime,4) & "','" & MacAddress & "')"
		sqlstate1 = "INSERT INTO logonsnap1 ( MailboxName, ServerName, [Client Version], [Client Mode], [Host Address], UserName, LoggedOnDate, LoggedOnTime, LastOpperationDate, LastOpperationTime, MacAddress ) values " & ltoinsert
	        Cnxn1.Execute(sqlstate1)
	end if
Next

The last section of the code then executes 2 Action queries, the first finds the new logons and then adds then to the LogonsRaw table and the seconds finds any new logoffs and adds them to the LogonsRaw table. The last part of the code is a function that converts the date into a serial format that I prefer to use in Database queries.

SQL1 = "Addlogoffs"
Cnxn1.Execute(SQL1)
SQL1 = "Addlogons"
Cnxn1.Execute(SQL1)
cnxn1.close
set cnxn1 = nothing
 

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:\LogonTrack.mdb;"

In the database maintainace section of code at the beginning of the script there is also hard coded reference to the location of the script.

if Fso.FileExists("d:\logontracktemp.mdb") then
	Set f2 = fso.getfile("d:\logontracktemp.mdb")
	f2.Delete
end if
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\logontrack.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\logontracktemp.mdb"
set f1 = fso.getfile("d:\logontracktemp.mdb")
f1.copy("d:\logontrack.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, I recommend you run the script by using a Scheduled task and schedule it to run every 15 minutes. You can vary this limit depending on your needs this is a interval that I find effective though. The script needs to run under an account that has administrative rights.

Because this script does a capture based on a time window its possible for it to miss a logon that only lasts for a very short time, to avoid this you can push up the poling time for example to every 5 minutes. Also with OWA it takes time for the logons table to update once you disconnect because this type of thing isn't documented anywhere I  don't know the reason behind but this it usually takes about 10-15 minutes for this to happen. So you need to be aware of this when you testing the script.