The Scenario
The thought of interrogating another user for their computer name had become a bit grueling for me and for users as well. It was becoming increasingly more time consuming with new users coming into the company weekly along with the daily calls we received from users that had been with the company for years but still didn’t know how to find their computer name. Some companies have web interface programs that you direct users to a URL and connect to their desktops without having to know the name of the computer; at our site we did not have that luxury. We use Dameware Mini Remote which requires you to specify a computer name or IP address of the computer to connect to. I thought there must be an easy way to get the users computer information without having to ask the user every time.
That would make my job easier and their experience with the IT department less stressful, as in most cases they are stresses out enough because their computer equipment was not working. (Or they wouldn’t be calling us, right!).
In the beginning
I started out creating a VBS login script and placed it on a network share and then placed a shortcut to it in the each local computers registry, which ran every time the computer was logged into. The script wrote to a text file in the same share the script was in and that all the users had access to. It recorded the user’s login name and computer name to the file with the “computername” and “username” methods of the “Wscript.Network” object. Then it was a simple task to read the text file to find the user’s name in the file and get the computer name the user was logged into.
Writing to the text file worked for the most part and users were beginning to think they called too much because we had the computer name and were not asking very often for it. But the script had problems, since it was hard to make the script find a particular line in the file to change just the computer name when they logged onto a different computer, it appended to the information to the text file, making it harder to manage as it grew daily. We also had issues with user’s computers not being recorded in the text file as only one person at a time could write to it.
Another Twist
To complicate matters more the company adopted new software that required a Remote Desktop Connection on each PC and we then were back to asking the user for the server name they were connected to. I knew the text file script had out grown its usefulness and it was time to look at a new way to do this task. This first attempt had given me an idea about writing the information to a SQL database instead of a text file. Being new to SQL I had some research to do before I was ready for this step, but in doing some testing, SQL seemed to be the best approach for the needs of the company and not just the small area the original script was working for. With SQL users can write simultaneously to the database so we would have more accurate records. The other benefit was the fact that I could write a script to return the users information to me without having to open a text file and search for the information.
There were a few details I needed to figure out first. I needed to find another way to deploy the script to the workstations, as I didn’t want to go the local registry route again, so since our company was now in an Active Directory environment we decided to go with a group policy for the login script. This way we could have it centrally manageable and easily changed if needed. Also this would cover the servers as well since we applied it to the OU that the all the users were in.
In Comes SQL
To start I needed to create a SQL database that all Domain Users had access to. Now I’m not a DBA, and you don’t need to be one to accomplish the setup of the database. All you need to know is just a few things about SQL. First, what data types will you be using, and I was only going to use two, “nvarchar” and “date”. I picked “nvarchar” data type because it is a variable length type, and I choose the “date” data type so I could eventually search the database using date comparisons to see how many users logged in per day. To find out more information on SQL data types you can check out MSDN site for SQL data types here http://msdn2.microsoft.com/en-us/library/ms179910(SQL.90).aspx . The other items needed would be the Database name, the table name and the column names, each need to be unique, as you will be references each to retrieve data latter. The last thing would be security logins, or basically who needs access to the database.
I used Enterprisemanager to create the database on a SQL 2000 test server. But you can use SQLexpress as well oo create the database called “User_Login_Data”, just right click on databases and chose new, then type in the information in the wizard and you have a new database. To create the new table, right click the on the database and choose new, which brings up a new table in design mode. Enter the four columns named Computername, Username, Servername, LastDateTime, and since this was the information we wanted to gather I tried to make it as straight forward as possible. I made all the columns “nvarchar” data type except the “LastDateTime” which I made “date” data type. You can accept the length unless your computer names are over 50 characters, and it’s up to you whether or not the columns accept NULL values. Once this was done I saved the table as “User_Data”. I then went down to “Security” and then “Logins” and right clicked on “Logins” and chose “new login” and added a security login for Domain Users and gave them write access to the database, then I gave the TR staff admin access to the database.
The New Script
Now that the database was setup I needed to write the script to access the database and record the information. The script needed to determine if the users were logging into a server or a workstation. This part was actually easy, because of the computer naming convention in our company. Servers always started with an “S” and the rest were workstations and laptops. Just as in my first script, I started out creating my variables as seen in Callout “A” in figure 1 to catch the computer name and user name. In this script I created two sub routines for writing the user information whether the user logged into a server or a workstation, Callout “B” shows how the script determines which sub routine to run by the first character of the computer name. If the computer name starts with an S then the script runs the “GetServer” routine and writes the information to the database, other wise it runs the “WritetoSQL” routine. For testing, I placed a sub routine in the script called “Writetoeventlog” which will write an entry in the local computers Application Event log letting you know that the script did indeed run on that computer. You’ll see at the top of the script I use several constants that declare what type of event is logged and even though I’m only using one I like to keep a reference to them all incase I want to change them later on down the road I won’t have to go looking them up. I did the same for theADOconstants for cursor types and lock types.
A good resource for ADOis here http://www.w3schools.com/ado/default.asp
To write to the SQL database I used theADOobject. I created a reference to the Connection object which allows me to make the initial connection to the SQL server and the database, and also set a timeout property like callout “C” shows in Figure 1. The Call out “D” in Figure 1 checks the connection state to the database and quits the script if a connection failed for any reason. Once connected it fills the recordset with a SELECT query. The reason for the query is to search the database for a user that already exists. The recordset is then looped thru using a “For” command searching for the user’s login name. If the user exists the variable “SQLUsername” is assigned the value of the user, and then an Update command is executed against the database to change the “Computername” to the current one. Other wise the variable is empty and the script does an Insert command against the database to write all the users information. Another reason I added the “LastDateTime” information is it gives me an idea if the script is working or not, because if I get a call from a user and see that the “LastDateTime” entry was yesterday, I immediately know a few things. Either the user has not logged in yet, or the user is working on a computer that has been logged in by someone else, and of course the last option, the script is not working.
Return on investment
Once the login script was working and recording data it was time to write the script to retrieve the results. Since this was going to be used be other analyst I wanted to make a script that was more than what just what VBScript alone had to offer, so I decided to go with an HTML Application (HTA). Using a web like front end would give it a much more professional look and feel. Upon thinking more about the functionality of the HTA I also wanted it to do more that just give me the user’s information, I wanted it work for me.
To start I created a basic HTA and set the window size as it only needs to display a little information and a few buttons. The first thing the script does is make an Active Directory connection to resolve the login name to the user’s full name callout “A” in Figure 3 shows the sub routine that performs this function. This way we can make sure we are helping the right person. Then the script makes a connection to the SQL server just like the login script and fills a recordset to search for the user’s login name that is entered in by the analyst when the HTA UI starts as shown in Figure 2, it also show the results shown after a username has been entered. The query returns all the information in the database, and the script uses that information to automate connections to the user’s computer. For instance, the script makes it easy to connect to the users administrative C$ share with a click of a button. Inside the “BrowseC” sub routine Shown in Figure 3 callout “B”. The script uses the “Wscript.Shell” to execute Explorer and passes the argument to it (the computer name retrieved from the SQL query).
Since we use Dameware Mini Remote Control (DWRCC.exe) to connect to users Desktops for support, and DWRCC.exe supports command line options, again it was fairly easy to use the Shell object to execute DWRCC.exe with arguments to automatically connect to the user’s desktop with a click of a button. Callout “C” show the “Dameware” sub routine with the arguments passed to it. To see all of the possible arguments for DWRCC.exe, use “-?”. The policy in our company make requires us to remove the service that Dameware installs, so I’ve also included a subroutine called “RemoveSrv” which runs as soon as the DWRCC.exe stops. The last thing to do was to connect to the user terminal server. And again I used the Shell object and passed the mstsc.exe executable the server argument as shown in Callout “D“ in Figure 3. To see the all the arguments for mstsc.exe use “/?”.
Although it may seem like a bit of work to set and get going the pay off is a huge time saver. I can be at the users desktop before they can finish the sentence “Can you take a look at something for me?”.
FIGURE 1
On Error Resume Next
Wscript.interactive = False
Const Event_SUCCESS = 0
Const Event_ERROR = 1
Const Event_WARNING = 2
Const Event_INFORMATION = 4
Const adOpenForwardOnly = 0 ‘Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records
Const adOpenKeyset = 1 ‘Like a dynamic cursor, except that you can’t see records that other users add
Const adOpenDynamic = 2 ‘Additions, changes, and deletions by other users are visible
Const adOpenStatic = 3 ‘static copy of a set of records
Const adLockReadOnly = 1 ‘for a read-only Recordset
Const adLockPessimistic = 2 ‘The provider lock records immediately after editing
Const adLockOptimistic = 3 ‘ The provider lock records only when calling update
Const adLockBatchOptimistic = 4 ‘for a writeable one
Dim Username, FSO, Shell, Net, StrComputer
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set Shell = CreateObject(“WScript.Shell”)
Set Net = WScript.CreateObject(“WScript.Network”)
‘ BEGIN CALLOUT A
Username = Ucase(Net.UserName)
StrComputer = UCase(Net.Computername)
‘ END CALLOUT A
‘ BEGIN CALLOUTB
If Left(StrComputer,1) = “S” then
GetServer
Else
WriteToSQL
End If
‘ END CALLOUT B
Writetoeventlog
Function WriteToSQL
On Error Resume Next
Dim oCN, oRS, sTime, sDate, LastDateTime, DateTime
Dim objRecordSet, objConnection, SQLusername
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
‘ BEGIN CALLOUT C
objConnection.ConnectionTimeout = 30
‘ END CALLOUT C
objConnection.Open “Provider=SQLOLEDB;Data Source=servername;” & _
“Trusted_Connection=Yes;Initial Catalog=User_Login_Data;”
‘ BEGIN CALLOUT D
If objConnection.State = 1 Then
WScript.Echo “Connected to SQL”
Else
wscript.Echo “Not connected to SQL Exiting”
Exit Function
End If
‘ END CALLOUT D
sTime = Time
sDate = Date
DateTime = sDate & ” ” & sTime
objRecordSet.Open “Select * FROM Users_Data WHERE Username = ‘”&username&”‘”,objConnection, adOpenStatic, adLockOptimistic
Do While Not objRecordSet.EOF
‘ BEGIN CALLOUT E
SQLusername = objRecordSet.fields(“username”)
‘ END CALLOUT E
objRecordSet.MoveNext
Loop
If SQLusername = “” Then
Wscript.echo “User is not is database, adding user”
objConnection.Execute “Insert Into Users_Data (computername,username,impactserver,LastDateTime,RamQuest) VALUES (‘”&StrComputer&”‘,'”&username&”‘,'”&Rimpactserver&”,”&DateTime&”‘,'”&Ramquest&”‘)”
Else
Wscript.echo “User is already in database, updating user info”
objConnection.Execute “Update Users_Data Set LastDateTime ='”&DateTime&”‘, computername ='”&StrComputer&”‘ , Servername ='”&Servername&”‘ Where Username = ‘”&SQLusername&”‘”
End If
objConnection.Close
End Function
Sub GetServer
On Error Resume Next
If Left(StrComputer,1) = “S” Then
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.ConnectionTimeout = 30
objConnection.Open “Provider=SQLOLEDB;Data Source=ServerName;” & _
“Trusted_Connection=Yes;Initial Catalog=User_login_data;”
If objConnection.State = 1 Then
WScript.Echo “Connected”
Else
Wscript.Echo “Not connected Exiting”
Exit Sub
End If
objRecordSet.Open “Select * FROM Users_Data WHERE Username = ‘”&username&”‘”,objConnection, adOpenStatic, adLockOptimistic
Do While Not objRecordSet.EOF
SQLusername = objRecordSet.fields(“username”)
objRecordSet.MoveNext
Loop
objConnection.Execute “Update Users_Data Set servername ='”&StrComputer&”‘ Where Username = ‘”&SQLusername&”‘”
objConnection.Close
End If
End Sub
Sub writetoeventlog
On Error Resume Next
Shell.LogEvent EVENT_INFORMATION, “Login Script started” , StrComputer
End Sub
FIGURE 2
FIGURE 3
<html>
<head>
<title>Connect To Users</title>
<HTA:APPLICATION
ID=”oHTA”
APPLICATIONNAME=”CONHTA”
BORDER=”thick”
BORDERSTYLE=”sunken”
CAPTION=”yes”
ICON=”Complex.ico”
MAXIMIZEBUTTON=”no”
MINIMIZEBUTTON=”no”
SHOWINTASKBAR=”yes”
SINGLEINSTANCE=”no”
WINDOWSTATE=”normal”
SCROLL=”no”
SYSMENU=”yes”
VERSION=”1.0″
>
</head>
<SCRIPT Language=”VBScript”>
window.moveTo 200,200
window.resizeTo 500,300
Dim Computer, username, FSO, Shell, Net, Response
Const ADS_SECURE_AUTHENTICATION = &H1
Const adOpenForwardOnly = 0 ‘Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records
Const adOpenKeyset = 1 ‘Like a dynamic cursor, except that you can’t see records that other users add
Const adOpenDynamic = 2 ‘Additions, changes, and deletions by other users are visible
Const adOpenStatic = 3 ‘static copy of a set of records
Const adLockReadOnly = 1 ‘for a read-only Recordset
Const adLockPessimistic = 2 ‘The provider lock records immediately after editing
Const adLockOptimistic = 3 ‘ The provider lock records only when calling update
Const adLockBatchOptimistic = 4 ‘for a writeable one
Set FSO = createobject(“scripting.filesystemobject”)
Set Shell = CreateObject(“WScript.Shell”)
Set Net = createobject(“wscript.network”)
Sub Window_Onload
User.focus
End Sub
Sub SearchScript
On Error Resume Next
document.body.style.cursor = “wait”
username = User.Value
Dim openfile, outfile, readline, arrFileLines()
GetUserInfo
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open “Provider=SQLOLEDB;Data Source=SERVERNAME;” & _
“Trusted_Connection=Yes;Initial Catalog=User_Data;”
objRecordSet.Open “Select * FROM Users_Data WHERE Username = ‘”&username&”‘”,objConnection, adOpenStatic, adLockOptimistic
Do While Not objRecordSet.EOF
StrComputer = objRecordSet.fields(“Computername”)
users = objRecordSet.fields(“username”)
StrServer = objRecordSet.fields(“servername”)
LoginTime = objRecordSet.fields(“LastDateTime”)
objRecordSet.MoveNext
Loop
objConnection.Close
If users = “” Then
document.body.insertAdjacentHTML “beforeEnd”,”User name Not Found”
document.body.style.cursor = “Default”
Exit Sub
End If
If StrServer = “” Then
results = Response&”<br>”
results = Results & “User Is On computer “&StrComputer&”<br>”
results = Results & “No Terminal Server Found<br>”
results = Results & “Logon Time was “&LoginTime&”<br>”
results = Results & “<p>Connect With <input id=runbutton class=””button”” type=””button”” value=””DameWare”” name=””Dameware”” onClick=””Dameware”” tabindex=””2″” title=””Use Dameware To Connect To Users Computer””>”
DataArea.innerHTML = results
str2HTML = “<input type=””hidden”” name=””D2″” size=””20″” Value=”&Chr(34)&StrComputer&Chr(34)&”>”
document.body.insertAdjacentHTML “beforeEnd”,str2HTML
Else
results = Response&”<br>”
results = Results & “User Is On computer “&StrComputer&”<br>”
results = Results & “User Is On Terminal Server ” & StrServer&”<br>”
results = Results & “Logon Time was “&LoginTime&”<br>”
results = Results & “<p>Connect With <input id=runbutton class=””button”” type=””button”” value=””DameWare”” name=””Dameware”” onClick=””Dameware”” tabindex=””2″” title=””Use Dameware To Connect To Users Computer””>”
results = Results & “<p>Connect With <input id=runbutton class=””button”” type=””button”” value=””TerminalServer”” name=”” TerminalServer “” onClick=”” GETTerminalServer “” tabindex=””3″” title=””Use Remote DeskTop To Connect To Users Terminal Server””> Using Remote DeskTop”
str2HTML = “<input type=””hidden”” name=””D2″” size=””20″” Value=”&Chr(34)&StrComputer&Chr(34)&”>”
str3HTML = “<input type=””hidden”” name=””D3″” size=””20″” Value=”&Chr(34)& Strserver&Chr(34)&”>”
DataArea.innerHTML = results&str2HTML&str3HTML
End If
DriveArea.innerHTML = “<input id=runbutton3 class=””button”” type=””button”” value=””Browse C: Drive”” name=””CDrive”” onClick=””BrowseC”” tabindex=””3″” title=””Browse C$ on Users Computer””>”
‘ Clean up
document.body.style.cursor = “Default”
Set oDatabase = Nothing
Set oServer = Nothing
End Sub
‘ BEGIN CALLOUT C
Sub Dameware
On Error Resume Next
Computer = D2.value
Shell.Run “””C:\Program Files\DameWare Development\DameWare NT Utilities\DWRCC.exe”””&” -c: -h: -m:” & Computer&” -a:1″,8,True
RemoveSrv Computer
window.Close
End Sub
‘ END CALLOUT C
‘ BEGIN CALLOUT D
Sub GETTerminalServer
TerminalServer = D3.value
Shell.Run “””C:\Program Files\Remote Desktop\mstsc.exe “””&” /V:” & TerminalServer
window.Close
End Sub
‘END CALLOUT D
‘ BEGIN CALLOUT A
Function GetUserInfo
On Error Resume Next
Dim objRecordSet, First, Last, LoginName, SamAcc, ObjCommand, ObjConnection
Set objRootDSE = GetObject(“LDAP://rootDSE”)
LDAPRoot = “LDAP://” & objRootDSE.Get(“defaultNamingContext”)
Set ObjConnection = CreateObject(“ADODB.Connection”)
ObjConnection.Open “Provider=ADsDSOObject;”
Set ObjCommand = CreateObject(“ADODB.Command”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
ObjCommand.ActiveConnection = ObjConnection
ObjCommand.CommandText = “Select givenname, sn, SamAccountName From ‘” & LDAPRoot & “‘ where objectCategory=’User’ AND SamAccountName='”&username&”‘”
objCommand.Properties(“Timeout”) = 30
objCommand.Properties(“Cache Results”) = False
Set objRecordSet = objCommand.Execute
Do Until objRecordSet.EOF
Response = objRecordSet.Fields(“givenname”) & ” ” & objRecordset.Fields(“sn”) & ” Is User “& username
objRecordSet.MoveNext
Loop
ObjConnection.Close
End Function
‘ END CALLOUT A
Sub buttonfocus
run_button.focus
End Sub
Sub RemoveSrv(Computer)
On Error Resume Next
‘Get a reference to the Win32Process class object on specified machine
Set objProcess = GetObject(“winmgmts:{impersonationLevel=impersonate}!” & _
“\\” & Computer & “\root\cimv2:Win32_Process” )
If Err Then
MsgBox “Unable to get reference to machine ” &Computer
Exit Sub
End If
‘Remove service from remote machine
nResult = objProcess.Create(“DWRCS -Remove”,Null,Null,nProcID)
If nResult = 0 Then
‘MsgBox “DameWare service successfuly removed from ” & strComputerName
Else
MsgBox “Unable to remove service on computer ” & Computer
End If
End Sub
‘ BEGIN CALLOUTB
Sub BrowseC
On Error Resume Next
Computer = D2.value
Shell.run “Explorer \\”&Computer&”\C$”
End Sub
‘ END CALLOUTB
</SCRIPT>
document.body.insertAdjacentHTML “beforeEnd”,”text displayed”
<body bgcolor=”#0099CC”>
<p>Type user login name And click search <input type=”text” name=”User” size=”10″ onchange=”buttonfocus”> <input id=runbutton type=”button” value=”Search” name=”run_button” onClick=”SearchScript” tabindex=”1″ title=”Search SQL Database for User name”></p>
<span id=DataArea></span><p>
<span id=DriveArea></span><p>
</body>
</html>
Share this:
Leave a Reply