Call Us For A Free Evaluation (509) 475-9070

Connect to Users

Connect to Users

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 &nbsp;&nbsp; <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 &nbsp;&nbsp; <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 &nbsp;&nbsp; <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&nbsp;&nbsp; <input type=”text” name=”User” size=”10″ onchange=”buttonfocus”>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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>

Leave a Reply


Spokane Computer Repair and Networking (509) 475-9070