Section 1.5. Getting the Most Out of Your Scripts

1.5. Getting the Most Out of Your Scripts

With the VBScript solutions, my intention is to provide the answer in as few lines of code as is reasonable. Since this book is not a pure programming book, I did not want to overshadow the graphical and command-line solutions by providing pages of code or detailed explanations on how to use WSH, WMI, or ADSI. If you are looking for such material, I recommend my book Active Directory Cookbook (O'Reilly) or Windows 2000 Scripting Guide by Microsoft Corporation (MSPress). The code in this book is meant to show you the basics for how a task can be automated and let you run with it. Most examples will take only minor tweaking to make them do something useful in your environment.

To make the code as simple as possible, I had to remove error checking and other features that are standard scripting best practices. Next, I'll describe how to incorporate these things into your own scripts so that you can quickly turn any code in this book into a robust script with all the trimmings.

Running Scripts Using Alternate Credentials

Just as you might need to run the graphical and command-line tools with alternate credentials, you may also need to run your scripts and programs with alternate credentials. One way is to use the runas utility when you invoke a script from the command line. Another option is to use the Scheduled Tasks service to run the script under credentials you specify when creating the scheduled task. And yet another option is to hardcode the credentials in the script. Obviously this is not very appealing in many scenarios because you do not want the username and password contained in the script to be easily viewable by others. Nevertheless, at times it is a necessary evil, especially when working against multiple servers, and I'll describe how it can be done with WMI, ADSI, and ADO.

WMI

Here is example WMI code that prints the list of disk drives on a system:

strComputer = "."  ' localhost set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") set objDisks = objWMI.InstancesOf("Win32_LogicalDisk") for each objDisk in objDisks     Wscript.Echo "DeviceID: " &  objDisk.DeviceID            Wscript.Echo "FileSystem: " &  objDisk.FileSystem        Wscript.Echo "FreeSpace: " & objDisk.FreeSpace           Wscript.Echo "Size: " & objDisk.Size             WScript.Echo "" next

This code does the same thing, except it targets a remote computer (srv01) and authenticates as the administrator account on that system:

set objLocator = CreateObject("WbemScripting.SWbemLocator") set objWMI = objLocator.ConnectServer("srv01", "root\cimv2", _                                       "srv01\administrator", "Adm1nPa33wd") set objDisks = objWMI.InstancesOf("Win32_LogicalDisk") for each objDisk in objDisks     Wscript.Echo "DeviceID: " &  objDisk.DeviceID            Wscript.Echo "FileSystem: " &  objDisk.FileSystem        Wscript.Echo "FreeSpace: " & objDisk.FreeSpace           Wscript.Echo "Size: " & objDisk.Size             WScript.Echo "" next

To authenticate as an alternate user in WMI, you simply need to replace the GetObject call with two statements. The first is a call to CreateObject, which instantiates a SWbemLocator object. With this object, you can then call the ConnectServer method and specify the credentials for authentication. The first parameter is the server name, the second is the WMI provider path, the third is the user, and the fourth is the user's password.

ADSI

With ADSI, you can use the IADsOpenDSObject::OpenDSObject method to specify alternate credentials. For example, a solution to print out the description of a domain might look like the following:

set objDomain = GetObject("LDAP://dc=rallencorp,dc=com") WScript.Echo "Description: " & objDomain.Get("description")

Using OpenDSObject, it takes only one additional statement to make the same code authenticate as the administrator account in the domain.

set objLDAP = GetObject("LDAP:") set objDomain = objLDAP.OpenDSObject( _     "LDAP://dc=rallencorp,dc=com", _     "[e-mail]", _     "MyPassword", _     0) WScript.Echo "Description: " & objDomain.Get("description")

ADO

It is just as easy to authenticate in ADO code. Take the following example, which queries all computer objects in the rallencorp.com domain:

strBase    =  "<LDAP://dc=rallencorp,dc=com>;" strFilter  = "(&(objectclass=computer)(objectcategory=computer));"  strAttrs   = "cn;" strScope   = "subtree"     set objConn = CreateObject("ADODB.Connection") objConn.Provider = "ADsDSOObject" objConn.Open "Active Directory Provider" set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope) objRS.MoveFirst while Not objRS.EOF     Wscript.Echo objRS.Fields(0).Value     objRS.MoveNext wend

Now, by adding two lines (shown in bold), we can authenticate with the administrator account:

strBaseDN  =  "<LDAP://dc=rallencorp,dc=com>;" strFilter  = "(&(objectclass=computer)(objectcategory=computer));"  strAttrs   = "cn;" strScope   = "subtree"     set objConn = CreateObject("ADODB.Connection") objConn.Provider = "ADsDSOObject" objConn.Properties("User ID")  = "[e-mail]" objConn.Properties("Password") = "MyPassword" objConn.Open "Active Directory Provider" set objRS = objConn.Execute(strBaseDN & strFilter & strAttrs & strScope) objRS.MoveFirst while Not objRS.EOF     Wscript.Echo objRS.Fields(0).Value     objRS.MoveNext wend

To authenticate with ADO, you need to set the User ID and Password properties of the ADO connection object. I used the user principal name (UPN) of the administrator for the user ID in this example. Active Directory allows connections with a UPN ([e-mail]), NT 4.0-style account name (e.g., RALLENCORP\Administrator), or distinguished name (e.g., cn=administrator,cn=users,dc=ral-lencorp,dc=com) for the user ID.

Defining Variables and Error Checking

An important part of any script is error checking. Error checking allows your programs to gracefully identify any issues that arise during execution and take appropriate action. Another good practice when writing scripts is to define variables before you use them and clean them up after you are done with them. In this book, most of the programmatic solutions do not include any error checking, predefined variables, or variable clean up. While admittedly this does not set a good example, if I included extensive error checking and variable management, it would have made this book considerably longer with little value to you, the reader.

Error checking with VBScript is pretty straightforward. At the beginning of the script, include the following declaration:

On Error Resume Next

This tells the script interpreter to continue even if errors occur. Without that declaration, whenever an error is encountered, the script will abort. When you use On Error Resume Next, you need to use the Err object to check for errors after any step where a fatal error could occur. The following example shows how to use the Err object.

On Error Resume Next set objDomain = GetObject("LDAP://dc=rallencorp,dc=com") if Err.Number <> 0 then    Wscript.Echo "An error occured getting the domain object: " & Err.Description    Wscript.Quit end if

Two important properties of the Err object are Number, which if nonzero signifies an error, and Description which contains the error message (when present).

As far as variable management goes, it is always a good practice to include the following at the beginning of every script:

Option Explicit

When this is used, every variable in the script must be declared or an exception will be generated when you attempt to run the script. This prevents a mistyped name from causing hard-to-trace errors. Variables are declared in VBScript using the Dim keyword. After you are done with a variable, it is a good practice to set it to Nothing so you release any resources bound to the variable, and don't accidentally reuse the variable with its previous value. The following code shows a complete example for printing the display name for a domain with error checking and variable management included:

Option Explicit On Error Resume Next     Dim objDomain set objDomain = GetObject("LDAP://cn=users,dc=rallencorp,dc=com") if Err.Number <> 0 then    Wscript.Echo "An error occured getting the domain object: " & Err.Description    Wscript.Quit end if     Dim strDescr strDescr = objDomain.Get("description") if Err.Number <> 0 then    Wscript.Echo "An error occured getting the description: " & Err.Description    Wscript.Quit end if     WScript.Echo "Description: " & strDescr     set objDomain = Nothing set strDescr  = Nothing

Using Command-Line Options in a Script

Most code samples you'll see in this book use hardcoded variables. That means when you want to change the value of a variable, you have to modify the script. A much more flexible solution is to obtain the desired value of those variables via command-line options. All good command-line programs work this way.

With WSH, you can retrieve the command-line options that are passed to a script by enumerating the WScript.Arguments object. Here is an example:

set objArgs = WScript.Arguments WScript.Echo "Total number of arguments: " & WScript.Arguments.Count for each strArg in objArgs     WScript.Echo strArg next

This works OK, but there is no structure to it. You can't retrieve the value of the /foo option by name. You can only access elements of a WScript.Arguments collection by index number. But never fear, WSH 5.6 introduced named and unnamed arguments. Let's say we invoked the following command:

> d:\scripts\dostuff.vbs /c:test /verbose:4

This bit of code shows how you can access the /c and /verbose options:

WScript.Echo WScript.Arguments.Named.Item("c") WScript.Echo WScript.Arguments.Named.Item("verbose")

Writing the Output of a Script to a File

In most of the code in this book, I simply print the output to STDOUT using the WScript.Echo method. This is OK if you need an interactive script, but what if you want to schedule one to run periodically? Printing the output to STDOUT won't do much good. An alternative is to write the output to a file instead. This is pretty easy using WSH. The following code appends some text to a file:

' ------ SCRIPT CONFIGURATION ------ strFile = "<FilePath>"   ' e.g. c:\output.txt ' ------ END CONFIGURATION --------- const ForAppending = 8 set objFSO = CreateObject("Scripting.FileSystemObject") set objFile = objFSO.OpenTextFile(strFile, constForAppending, True) objFile.WriteLine("Script completed: " & Now) objFile.Close

There is nothing magical here. The Scripting.FileSystemObject interface is used for working with files. The OpenTextFile method supports different access options. The following script is a variation of the earlier script except it opens a file for writing out all of the running processes (overwriting any existing data in the file):

' ------ SCRIPT CONFIGURATION ------ strFile = "<FilePath>"   ' e.g. c:\output.txt ' ------ END CONFIGURATION --------- constForWriting = 2 set objFSO = CreateObject("Scripting.FileSystemObject") set objFile = objFSO.OpenTextFile(strFile, constForWriting, True)     objFile.WriteLine("Script started: " & Now) objFile.WriteLine("List of processes:")     set objWMI = GetObject("winmgmts:root\cimv2") for each objProcess in objWMI.InstancesOf("Win32_Process")     objFile.WriteLine(vbTab & objProcess.Name) next     objFile.WriteLine("Script completed: " & Now) objFile.Close

Instead of WScript.Echo, you have to use the WriteLine method of the Scripting.FileSystemObject object. If you also wanted to print the results to STDOUT, there is nothing to prevent you from putting a WScript.Echo statement right before or after a WriteLine statement.

Sending the Output of a Script in Email

When you automate a task, you are being proactive. Part of being proactive is trying to identify issues before they turn into major problems. If your scripts simply append their status to a log file, it is unlikely you'll learn about any problems in a timely manner unless you are vigilantly watching over your log files. Fortunately, you can send emails very easily from VBScript so that instead of writing to a file, you can choose to send an email when there is a serious issue.

Here is an example script that just sends simple email:

' This code sends an email via SMTP ' ------ SCRIPT CONFIGURATION ------ strFrom = "[e-mail]" strTo   = "[e-mail]" strSub  = "Script Output" strBody = "The script ran successfully" strSMTP = "smtp.rallencorp.com" ' ------ END CONFIGURATION ---------     set objEmail = CreateObject("CDO.Message") objEmail.From = strFrom objEmail.To = strTo objEmail.Subject = strSub objEmail.Textbody = strBody objEmail.Configuration.Fields.Item( _          "http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objEmail.Configuration.Fields.Item( _          "http://schemas.microsoft.com/cdo/configuration/smtpserver") = _          strSMTP objEmail.Configuration.Fields.Update objEmail.Send WScript.Echo "Email sent"

This code requires the use of a SMTP-enabled mail server. The email is directed toward the mail server, which relays it to the correct destination. This script also requires Microsoft Collaboration Data Objects (CDO) to be installed on the client computer. This can be done by installing Outlook or by installing CDO separately from the Microsoft site (search for "CDO" on http://msdn.microsoft.com/).

Reading and Writing from Excel

A common question I see on newsgroups has to do with reading and writing Excel spreadsheets from scripts. Why would you want to do this, you might ask? Well, let's suppose that I manage over 20 servers. I put together a small spreadsheet to keep track of them. Now, if I want to perform a task on all of my servers with a script, all I need to do is read information about each of the servers from the Excel spreadsheet and I don't have to worry about hardcoding the servers within the script.

This next script shows how to iterate over the rows in a worksheet until the script comes across a row that does not have the first cell populated:

' ------ SCRIPT CONFIGURATION ------ strExcelPath = "c:\data.xls" intStartRow = 2 ' ------ END CONFIGURATION --------- On Error Resume Next set objExcel = CreateObject("Excel.Application") if Err.Number <> 0 then   Wscript.Echo "Excel application not installed."   Wscript.Quit end if On Error GoTo 0     objExcel.WorkBooks.Open strExcelPath set objSheet = objExcel.ActiveWorkbook.Worksheets(1)     intRow = intStartRow do while objSheet.Cells(intRow, 1).Value <> ""   WScript.Echo "Row " & intRow   WScript.Echo "Cell 1: " & objSheet.Cells(intRow, 1).Value   WScript.Echo "Cell 2: " &  objSheet.Cells(intRow, 2).Value   WScript.Echo "Cell 3: " &  objSheet.Cells(intRow, 3).Value   WScript.Echo "Cell 4: " &  objSheet.Cells(intRow, 4).Value   intRow = intRow + 1   WScript.Echo loop     objExcel.ActiveWorkbook.Close objExcel.Application.Quit Wscript.Echo "Done"

In this case, I just printed the values from the first four cells. You could obviously do more complex stuff with that information.

Now suppose I wanted to analyze the process information of a system. I could use the taskmgr.exe program, but it doesn't really give me the flexibility I need. Instead I can write a script to output that information to a spreadsheet. Here is the code to do that:

' ------ SCRIPT CONFIGURATION ------ strComputer = "." strExcelPath = "d:\procs.xls" ' ------ END CONFIGURATION ---------     On Error Resume Next set objExcel = CreateObject("Excel.Application") if Err.Number <> 0 then   Wscript.Echo "Excel application not installed."   Wscript.Quit end if On Error GoTo 0     ' Create a new workbook. objExcel.Workbooks.Add     ' Bind to worksheet. Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) objSheet.Name = "Processes"     ' Populate spreadsheet cells with user attributes. objSheet.Cells(1, 1).Value = "Process Name" objSheet.Cells(1, 2).Value = "Command Line" objSheet.Cells(1, 3).Value = "PID" objSheet.Cells(1, 4).Value = "Owner" objSheet.Range("A1:D1").Font.Bold = True     ' Query process information set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") intProcessCount = 1 for each objProcess in objWMI.InstancesOf("Win32_Process")    ' For each process, write the name, command-line options and process ID    ' to the spreadsheet    intProcessCount = intProcessCount + 1    objSheet.Cells(intProcessCount,1).Value = objProcess.Name    objSheet.Cells(intProcessCount,2).Value = ObjProcess.CommandLine    objSheet.Cells(intProcessCount,3).Value = ObjProcess.ProcessID    objProcess.GetOwner strUser,strDomain    objSheet.Cells(intProcessCount,4).Value = strDomain & "\" & strUser next     ' This formats the columns objExcel.Columns(1).ColumnWidth = 20 objExcel.Columns(2).ColumnWidth = 50 objExcel.Columns(3).ColumnWidth = 5 objExcel.Columns(4).ColumnWidth = 30     ' Save the spreadsheet, close the workbook and exit. objExcel.ActiveWorkbook.SaveAs strExcelPath objExcel.ActiveWorkbook.Close objExcel.Application.Quit     WScript.Echo "Done"

I included comments in the code to help you follow along with what is happening. Pretty straightforward, isn't it? Keep in mind that Excel must be installed on the computer in order to run this script.