Weekly Terminal Services Connection Report using VBS

A few weeks ago we had some state auditors come by and mention that we should review our logs for any sort of outside / vendor access. I knew that going to each server and reviewing the logs manually would be very time consuming and not really provide solid documentation that it was done. I decided that the only way to solve this problem was with a report of some nature. I fired up my trusty Crystal Reports and started to view the logs using that, once I got in to more I realized that when I added the description field of the event log it always crashed Crystal Reports. This left me going to plan B which is writing the reports from scratch using Visual Basic Scripting language.

I already knew that you can use VBS to connect to WMI (Windows Management Interface) and view different parts of the system including the event log, so I spent the morning writing the report and parsing it down to the detail that I really needed. Then I decided to take it to the next level by adding in recursion for multiple servers and also set it up to send an HTML email so it is easy to review every week. Why every week you may ask, well in looking at my event log on my domain server I noticed that I start losing Security events at about 10-14 days out since it is authorizing so much, and a weekly task is a very manageable one.

Script Configuration

Configure the servers that this script will report on. Modify the Servers array for each server that needs to be checked. (Note: all servers need the same login credentials for the script to work)
Find the objMessage.From field and update it with who the email is coming from
Find the objMessage.To Field and update with the email address of the person who will be receiving the report, if you have multiple addresses to send to separate them with a semi-colon (;)
Find the (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp-relay.waynezim.com” and update this with your SMTP server, if your server requires authentication you will need to modify this script to include that, a simple Google search should show you what needs to be changed.
This script should be setup to be a scheduled task on one of your servers, the credentials used in setting up the job will be used to connect to the other servers, this account needs to exist on all servers to view the Security Event Log and make the report.
To setup a scheduled task, go to your Control Panel, open Scheduled Tasks, right click New > Scheduled Task, name it, then right click and modify the Properties, Browse to where the script is saved, set the Run as at the bottom for the user that exists on all Servers and set the password. Then go to the Schedule tab and set it to Weekly and change it to run when you want it to.

Dim objWMI, objEvent ' Objects
Dim strComputer ' Strings
Dim intEvent, intNumberID, intRecordNum, colLoggedEvents
'--------------------------------------------
' Server List to Parse Logs
Dim Servers(5)
Servers(0) = "server1"
Servers(1) = "server2"
Servers(2) = "server3"
Servers(3) = "server4"
Servers(4) = "server5"
Servers(5) = "server6"
'--------------------------------------------
' Email Body Heading
HTMLMsg = "<html><body><h3>Remote Desktop Connections from " & cDate(Now() - 7) & " to " & cDate(Now()) & "</h3>"
HTMLMsg = HTMLMsg & "<table border=1><tr><td><b>Computer Name</b></td><td><b>Logon Type</b></td><td><b>Remote IP</b></td><td><b>Date / Time</b></td><td><b>User</b></td></tr>"
'--------------------------------------------
' Next section creates the file to store Events
' Then creates WMI connector to the Logs

'Range Variable - Out of Loop for Common Report Time
WeekAgo = cDate(Now() - 7)

'Start Each Computer Loop
For Each strComputer in Servers
' --------------------------------------------
' Set your variables for Events Loop
intEvent = 1
intRecordNum = 1

Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colLoggedEvents = objWMI.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = 'Security' AND EventCode = 528 AND TimeWritten > '" & WeekAgo & "'")
' -----------------------------------------
' Next section loops through ID properties
intEvent = 1
	For Each objEvent in colLoggedEvents

	HTMLMsg = HTMLMsg & "<tr><td>" & objEvent.ComputerName & "</td>"
	LogonType = RTrim(Mid(objEvent.Message,InStr(objEvent.Message,"Logon Type:")+12,2))
	If LogonType = 2 Then HTMLMsg = HTMLMsg & "<td>Interactive</td>" End if
	If LogonType = 3 Then HTMLMsg = HTMLMsg & "<td>Network</td>" End if
	If LogonType = 4 Then HTMLMsg = HTMLMsg & "<td>Batch</td>" End if
	If LogonType = 5 Then HTMLMsg = HTMLMsg & "<td>Service</td>" End if
	If LogonType = 7 Then HTMLMsg = HTMLMsg & "<td>Unlock</td>" End if
	If LogonType = 8 Then HTMLMsg = HTMLMsg & "<td>Network using Clear Text</td>" End if
	If LogonType = 9 Then HTMLMsg = HTMLMsg & "<td>New Credentials</td>" End if
	If LogonType = 10 Then HTMLMsg = HTMLMsg & "<td>Remote Interactive</td>" End if
	If LogonType = 11 Then HTMLMsg = HTMLMsg & "<td>Cached Interaction</td>" End if

	IPlen = InStr(InStr(objEvent.Message,"Source Network Address:")+24,objEvent.Message,"	") - InStr(objEvent.Message,"Source Network Address:") - 28
	RemoteAddress = RTrim(Mid(objEvent.Message,InStr(objEvent.Message,"Source Network Address:")+24,IPlen))
	HTMLMsg = HTMLMsg & "<td>" & RemoteAddress & "</td>"
	EventTime = Mid(objEvent.TimeWritten, 5, 2) & "/" & Mid(objEvent.TimeWritten, 7, 2) & "/" & Mid(objEvent.TimeWritten, 1, 4) & " " & Mid(objEvent.TimeWritten, 9, 2) & ":" & Mid(objEvent.TimeWritten, 11, 2) & "." & Mid(objEvent.TimeWritten, 13, 2)
	HTMLMsg = HTMLMsg & "<td>" & EventTime & "</td>"
	HTMLMsg = HTMLMsg & "<td>" & objEvent.User & "</td></tr>"
	intRecordNum = intRecordNum +1
	IntEvent = intEvent +1

	Next
Next

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Remote Connections Report: " & cDate(Now())
objMessage.From = "root@waynezim.com"
objMessage.To = "waynezim@waynezim.com"
objMessage.HTMLBody = HTMLMsg
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-relay.waynezim.com"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==

objMessage.Send
WScript.Quit

Report Preview
If you need help decoding what Logon Type really means check out this great article.

Wayne Zimmerman

Profile cancel