This isn’t related to SQL in any way but I’d thought I’d present it here as it has proven to be an excellent solution to a problem I was facing at work.
I’m currently working for a software house helping out with upgrades of customer systems. I got thrown a few .dll files and was informed these needed to be deployed and registered on all PC’s running our application. Now some of these sites are pretty large and some of them lack any sort of IT or technical person. I thought an automated solution was in order.
I thought I’d take a look at the Sysinternals site to see if any tools fitted the bill. Anyone working in a Windows Environment needs to check out this set of tools as there are many potential arse saving goodies. Among the sysnternals set of tools I found psexec which allows you to execute processes on remote machines.
First I needed a list of PC’s in the domain. The vb script below I have sourced from this site, I’ve just added a field to the WQL query and modified the “table” name we will select from. You need to modify this to fit your domain, for example if your domain was youdidwhatwithtsql.com then change the LDAP url to LDAP://DC=youdidwhatwithtsql,DC=com, if it was domain.local then it would be LDAP://DC=domain,DC=local.
'This script will list all computers on your domain 'Created by C.E. Harden August 16 2006 Const ADS_SCOPE_SUBTREE = 2 Const OPEN_FILE_FOR_WRITING = 2 Const ForReading = 1 Wscript.Echo "The output will be written to C:\Computers.txt" strFile = "Computers.txt" strWritePath = "C:\" & strFile strDirectory = "C:\" Set objFSO1 = CreateObject("Scripting.FileSystemObject") If objFSO1.FileExists(strWritePath) Then Set objFolder = objFSO1.GetFile(strWritePath) Else Set objFile = objFSO1.CreateTextFile(strDirectory & strFile) objFile = "" End If Set fso = CreateObject("Scripting.FileSystemObject") Set textFile = fso.OpenTextFile(strWritePath, OPEN_FILE_FOR_WRITING) Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" Set objCOmmand.ActiveConnection = objConnection objCommand.CommandText = _ "Select Name, OperatingSystem Location from 'LDAP://DC=youdidwhatwithtsql,DC=com' " _ & "Where objectClass='computer'" objCommand.Properties("Page Size") = 1000 objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE Set objRecordSet = objCommand.Execute objRecordSet.MoveFirst Do Until objRecordSet.EOF 'Wscript.Echo "Computer Name: " & objRecordSet.Fields("Name").Value textFile.WriteLine(objRecordSet.Fields("Name").Value) objRecordSet.MoveNext Loop Set objFSO = CreateObject("Scripting.FileSystemObject") Set objArgs = Wscript.Arguments Set objTextFile = objFSO.OpenTextFile(strWritePath, ForReading) Do Until objTextFile.AtEndOfStream strReg = objTextFile.Readline Loop WScript.Echo "All done!"
Save this in notepad with a .vbs extension and execute it by double clicking
Click ‘OK’ and then the script will finish in a few moments (or a bit longer if you have lots of computers in your domain).
I added the OS name because I wanted to strip out any servers from the list. Next I removed all unneeded columns, using Excel, so I was just left with a list of computer names..
Next create a folder in the root of c:\ called psexec and place your edited version of Computers.txt inside. Next download the PsTools Suite, unzip the archive and place a copy of psexec.exe into c:\psexec. Finally we need a batch file containing the commands we want to execute on each of our domain computers. For this example the actual commands in the batch file are unimportant so change these to suit your particular problem. In my real-life use I renamed some local files, copied a few .dll files from a network share and registered them on each domain computer. For this example the following command will output a some text with the computer name, datetime and the username who ran the command.
echo I ran on %COMPUTERNAME% at %DATE%:%TIME% by %USERNAME%
Copy your commands into a text file called commands.bat and place it into c:\psexec. Finally we are ready to use psexec to execute the commands, in our batch file, on all of the computers specified in Computers.txt.
C:\PSEXEC\psexec.exe @C:\PSEXEC\Computers.txt -u RHYS-PC\Rhys -c C:\PSEXEC\commands.bat > C:\PSEXEC\output.txt
The C:\PSEXEC\psexec.exe is obviously to run the psexec executable. The @C:\PSEXEC\Computers.txt will feed the process our list of computer names. The -u switch should be changed to a domain administrator, or another user with appropriate rights to each machine. The -c switch includes the path to the command.bat file. This will be copied to, and executed on, each machine listed in Computers.txt.Finally we use > C:\PSEXEC\output.txt to redirect the ouput to a text file.
Run your modified command as appropriate. You may get a license agreement for psexec appearing, just click ok, then you’ll have to enter the password for the user specified in the command. Psexec should work through the list of computers and attempt to run the batch file on each and return an exit code.
The following was outputted in this example…
Password: C:\Windows\system32>echo I ran on RHYS-PC at 02/03/2009:20:25:49.36 by Rhys I ran on RHYS-PC at 02/03/2009:20:25:49.36 by Rhys \\RHYS-PC:
This example is fairly lightweight to keep the concept simple but hopefully it’s enough to illustrate the great power of a simple tool like PsExec. I’m sure in my own situation this has saved many hours of time and kept some customers smiling.
Blogged with the Flock Browser