Using VBScript to Record User Logon/Logoff Activity in a Database

The main purpose of this article is to give you the tools you need to manipulate data in a database using VBScript.

The steps taken here describe how we would go about adding user login/logoff information into a Microsoft Access database, but you could use the techniques here for other purposes.

Where has that user gone...

Imagine the scene: a user rings up telling you they are having a problem with their system. They are using a terminal server, and are situated at a remote site. Half of your phone call could be taken up trying to find out what terminal server they are on, what applications they are running, or even if they are logged on at all!

Wouldn't it be so much easier if you could just look into a simple database or web page, and find out exactly all those details, just by knowing the user's login name? Well, if we are clever with our logon/logoff scripts, we can do just that.

We can then take this idea further, and use the same database to hold versions of software that the user has installed. You could use this to ensure they are using the correct versions of everything.

Building the Database

The first thing we will do is create a database to hold the user information. We are using Microsoft Access here as most people will already have it, and VB script (or at least ADO) will connect to an Access data source without the need for any extra software or ODBC settings.

Create a new Access database called UserInfo.mdb. Save this onto a network share to which all your users have read and write access. They need both these permissions, as when the login script runs, it will run as the currently logged on user.

Note: If you are worried about your users finding this database and attempting to change it, you could put it on a hidden share (when creating the share, put a $ at the end of the name). To add a level of security to the database, you could also password protect the database.

Once you have created the database, create one table called tblUserInfo. Into this table create the following fields, like so (some of the fields we will use later on for software versions):

Once this table has been created, we can write the login script.

Creating the VBS login Script

To write the VBScript, all you need is a text editor. There are a number of professional script writing tools on the market (Primal script is a good product), but you can also use something as simple as notepad, which is what I tend to use.

Open up notepad (or your script editor) and create a new document called UserLoginInfo.vbs. For the time being just save the script onto your desktop.

Main Update Function

First of all we will write the function that will do all the work. Each line will be explained as we write it.

Put in the opening of the function and all the variable names. Notice we are going to pass 5 variables into this function from where we call it:

sub DbaseUpd (strUser,strSite,strXntVer,strXntDate,strTerm)
Dim conn,sql,strSQLQuery,rsVers,strConnStr

Now we will create the connection to the database, and open it up so we can read and write to it:

strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= w-
editfs1itSoftwareUpdateseditupdates.mdb;User Id=admin;Password=;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnStr

First we set the variable strConnStr to the connection string we will need to create our database connection. If you have never used a connection string before, it is just a string that tells ADO what database driver to use, the location of the database and any password information. For more on connection strings see http://www.connectionstrings.com/

Then we create an ADODB object, and open it using our connection string.

Next, we run some SQL on our database to see if the current user already has a record. We need to do this before attempting to write the user's information, because the SQL will be different, as you'll see.

strSQLQuery = "SELECT username FROM tblUserInfo WHERE username = '" & strUser & "'"
set rsVers=CreateObject("ADODB.Recordset")
rsVers.open strSQLQuery,conn,3,3

The variable strSQLQuery holds the SQL that will be executed against the database. All it will do is return a record if the user exists. The second line creates a record set ADODB object, and the third line opens up the record set on the database connection we just established using the SQL we have written.

The next part will then update our database with the information we have been passed.

if rsVers.bof and rsVers.eof then 'new user

  strSQLQuery = "INSERT INTO tblScribeInfo (username,site,logintime,termserver,xntVer,xntdate)"
  strSQLQuery = strSQLQuery & " Values('" & strUser & "','" & strSite & "','" & Now() & "','" & strTerm & "','" & strXntVer & "','" & strXntDate & "')"
else                         'existing user
  strSQLQuery = "UPDATE tblScribeInfo SET username = '" & strUser & "', site = '" & strSite & "', logintime = '" & Now() & "', termserver = '" & strTerm & "', "
  strSQLQuery = strSQLQuery & "xntVer = '" & strXntVer & "', xntdate = '" & strXntDate & "' WHERE username = '" & strUser & "'"
end if

The first section of code is asking our previously created record set if we have returned the user or not. It works by seeing if the record set is both at the end and the beginning of the records. If this is the case we have no records.

If we do not have the user present already, it will build some SQL that will insert a new user into the database, with all the details we have passed to the function.

If the user already exists, then it will build some SQL that will update the current user with the passed information.

All that is left to do now is execute the update/insert SQL function, and clean up after ourselves.

rsVers.close
rsVers.open strSQLQuery,conn,3,3

set rsVers = Nothing
set conn = Nothing

end sub

The first line closes our previously opened record set so we can re-use it. The second line then runs the SQL on that record set so our database is now up to date. The record set and database connections are then destroyed to free up resources, and the sub procedure is closed.

Calling the Function

Now that we have written the main function that does all the work, we need to call it from within the main body of the script. Before we can do this we need to get all the information about the user, applications and so on that we are going to pass to the function.

First of all we will get the domain and user name of the currently logged on user. Do this with the following code (don't forget to declare the variables as well):

Set objNet = WScript.CreateObject("WScript.Network")
strDomain = objNet.UserDomain
strUser = objNet.Username

That will create a wscript.network object, and from that we can retrieve the username and domain. The next variable we pass in the function is the site name. There are various ways of determining this; the way do it is based on the user's group membership, so it is hard coded into the login script within the group section.

Next we pass the version and date of a file. In this case we are checking a QuarkXpress extension, but it can be used for any exe or similar file.

Set objFso = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("c:QuarkXTensionqScribe.xnt")
strFileVer = objFSO.GetFileVersion("
                                c:QuarkXTensionqScribe.xnt")
strFileDate = objFile.DateLastModified

Here we are just creating a FileSystemObject so we can work with files. Now we have the file information; the last part we need is to find out the current machine the user is logged onto. We can retrieve this information out of the registry.

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

strTermSvr = objwsh.RegRead("
          HKEY_LOCAL_MACHINESYSTEMCurrentControlSet
          ControlComputerNameActiveComputerNameComputerName")

Now we have everything in place to call the function. We do this like so:

DbaseUpd strUser, "Preston", strFileVer, strFileDate ,strTermSvr

After doing this, you should now have a brand new entry in your database. Going forward you will probably have your own specific thing you want to log. You just need to alter the database schema accordingly; change the insert/update SQL and pass the new data into the function.

Once you have tested it locally fist, try deploying it as part of your normal vbs login scripts. In only a couple of days you should have gathered quite a substantial amount of data about your users that should help in everyday help desk calls.

Going Forward

The data probably isn't very usable to your help desk in its current state, so why not look at creating a simple web page to query the database? You could write a page in ASP that would accept the user name as an argument, then return all the data on that user.

As well as using a login script, you could also use a logoff script. Then, when the user logs of the system it will update the database with a logoff time, or maybe just remove the user's entry from the system altogether.

Hopefully, the building blocks used in this article can be used to build more complex scripts that will help keep track of user activity in your domain. If you have any ideas about what else could be logged, or any other general comments, feel free to email me at luke@beakersoft.wanadoo.co.uk