Logging Windows 2003 Terminal Server Connections

Introduction

Quite a lot of businesses are moving their client base onto terminal servers. This gives obvious benefits to the business such as better disaster recovery, ease of support for helpdesk personnel and ease of upgrading.

While this brings benefits to the business, it can sometimes be quite hard to manage the environment.

There could be upwards of 20 servers to look after. You might need to know how they are performing under a certain user load, and make sure that the load is balanced correctly over all the available hardware.

This article explains a way you can run a process on the terminal servers that will log the current number of currently connected users into a database, using a VB script and a batch file.

Create the Database Table

For this article I am using a SQL2000 database, but you can use anything you can update via your VB script. You need to create a table as follows to hold the information:

Now save the table as ts_status. Now we can create the batch file that will get the actual user information from the terminal server.

Creating the Batch File and Why it’s Used

There is an application that you run on the command line called QUERY. This is what we use to obtain the list of currently logged in users. The tool can also be used to gather other information at the terminal server. Try running

C:query /?

from a command prompt to see the list of available arguments. In our case we are interested in the USER argument. If you run this it will give you a list of the currently logged in users, along with some other information.

What we are going to do is run the QUERY USER command, and return the results to a text file. We will then use the contents of the text file to update our database table. Open up notepad or your preferred text editor, and type in the following:

REM Batch file to save the list of logged in users to a text file

REM First delete the current file if it exists

del /q c:CurrentTSUsers.txt

REM Now use the QUERY tool to get a list of users and re-direct the output
REM to a text file

query user >>c:CurrentTSUsers.txt

Once you have this in your file, save it to the root of your c: drive and call it GetTSUserInfo.bat

If you are doing this work on a terminal server, you should be able to run this batch file and look in the c:CurrentUsers.txt file to see all the logged in users.

Writing the Script

Now that we have the batch file that will provide us with the information, we can start to write our VB script. Again open up your preferred text editor, and type the following code. We will write the script a section at a time and then explain the code.

'Create the objects

Set objwsh = WScript.CreateObject("WScript.Shell")

Set objFSO= CreateObject("Scripting.FileSystemObject")

'set the initial vars

CONST ForReading = 1

CONST ForWriting = 2

'get the computer name from the reg

strServerName = objwsh.RegRead("HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControl
ComputerNameActiveComputerNameComputerName")

'set the current date in a format SQL will understand

strSQLDate = DatePart("yyyy", Now()) & "-" & DatePart("m", Now()) & "-" & DatePart("d", Now()) & " " & DatePart("h", Now()) & ":"  & DatePart("n", Now()) & ":" & DatePart("s", Now()) 

'set the initial line counter

intLineCnt = 0

'run the user query batch file to get the latest user info.

objwsh.Run "c:GetTSUserInfo.bat",1,true   

The couple of lines simply create two objects: objwsh is the shell object, and objFSO is the file system object.

Then we set two constants for when we read and write to our text files.

The next part uses the Shell object we created, gets the current computer name out of the registry and stores it in a variable to use later.

Then we assign the variable strSQLDate to be the current date and time, in a format that we can use successfully in the SQL query, which we will use to update our table. The next line sets the initial line count to zero.

Running the Batch File

Then we run the batch file we created in the last step to get our user list. We run it using the shell object we created. First we pass the name of the batch file, then a 1 is the window style (1 means activate and display the window), the true means wait until the process we are calling has finished running before carrying on with the rest of the script.

'Now open up the file and get the number of users out. All we do is open the file 'and read every line, and count the read lines. Then knock off one for the first 'header line:

Set objUserFile= objFSO.OpenTextFile("c:CurrentTSUsers.txt", ForReading)

Do until objUserFile.AtEndOfStream

   strCurrLine = objUserFile.readline

   intLineCnt = intLineCnt + 1

loop

'Close the file

objUserFile.Close

'Get the end amount of users no header row if 0

if intLineCnt > 0 then

    intLineCnt = intLineCnt  -1

end if

'Now get the last number of connections from the local file. If they are the same 'we can just quit and not bother doing an update

Set objUserFile= objFSO.OpenTextFile("c:LastTSUserCnt.txt", ForReading)

intLastCnt = trim(objUserFile.readline)

objUserFile.close

Now, in this block of code we are going to open up the text file the batch file has created for us and read its information.

First of all, use the FileSystemObject we created to open up the file for reading.

Now that the file is open, we will loop line by line through the text file, read the current line into a variable and increment the line count by one every time we go through the loop. When we have come to the end of the loop we close the file.

The file that gets created always has a header line at the top if there are users logged in. We don’t want to include this line in our count so the next section simply takes one of the count variables if it is greater than zero.

The next line of code opens up another file that we use to get its information out. Every time the script runs we write the number of users we found into this file.

Then we compare this number to the current number and if it is the same we don’t bother doing a database update as the details are the same. This is just done to decrease hits on the database. The code simply reads the number out of this file and saves to a variable, then closes the file.

if Cint(intLastCnt) <> Cint(intLineCnt) then

   'connect to the database

   strConnString = "Driver={SQL Server};Server=TestServer;Database=TS_Connections;Uid=test;Pwd=test;"

   Set connIns = CreateObject("ADODB.Connection")

   connIns.Open strConnString

   'now build the sql to update the new info the database and run it

   strUpdateSql = "UPDATE ts_status SET current_count = " & intLineCnt & ", last_count_time = '" & strSQLDate & "' WHERE server_name = '" & strServerName & "'"

   set rsServerUpd=CreateObject("ADODB.Recordset")

   rsServerUpd.open strUpdateSql,connIns,3,3

   connIns.close

   set connIns = Nothing

   'now update the count file with the new info

   Set objUserFile = objFSO.OpenTextFile("c:LastTSUserCnt.txt", ForWriting)

   objUserFile.Write intLineCnt

   objUserFile.Close

end if

The final part of the code in the script is the bit that actually does the database update. The code is wrapped in an if statement which checks to see if the last count and current count are the same. As discussed before, if they are the same then we don’t bother doing anything.

If we are updating the database, the first part of the code creates a connection string to the database. In this case I am using a SQL Server connection string. You will have to alter this to match your specific database type and location.

Then, we create an ADOB object and open a connection to our database using the connection string.

Now that we have a connection to the database, we build a SQL query to run that contains our new user count and date values. A record set object is then created using ADOB, and the SQL query is executed on the database connection.

The connection to the database is then closed.

The final part of the code writes out the value of the count to the text file, ready for when  the script runs next time.

Going Forward

Hopefully, with these simple building blocks you can now log information about your terminal server usage. You can change the script so it writes out the user names to the database, find the amount of time users are spending on the servers, and whether they are logged into more than one server. You'll find various other uses for the code as well.  I have used it in the past to write an application that load balances the users across multiple servers.