Easily Update AD Attributes

Organizations that store user information in Active Directory (AD) often run into problems when they need to update information for a large group of people. For example, when an entire division relocates, you need to update those employees' address and telephone information in AD. The most common methods for making such updates are to use Lightweight Directory Access Protocol (LDAP) Data Interchange Format (LDIF) files in conjunction with ldifde.exe or to create custom scripts that use Active Directory Service Interfaces (ADSI). Creating LDIF files to perform such updates isn't simple, though, because LDIF file generation can require many string manipulations. Creating custom ADSI scripts gives you more control over how to perform the updates—you can add, delete, or update objects and attributes in any way you see fit (e.g., modify a telephone number's format, change characters from lowercase to uppercase)—but you must find a way to retrieve updated information and read it into the scripts.

To simplify the process, you can create a VBScript script that uses ADSI to update AD object attributes according to values in a comma-separated value (CSV) file or Microsoft Excel spreadsheet (as the sidebar "Using Excel to Parse the Input File" explains). I've written a sample script, updateattribs.vbs, that you can adapt to your environment; I tested this script on a Windows 2000 Service Pack 3 (SP3) system and used the script with both Excel 2002 and Excel 2000; the script will work with any Win2K version. You can download the script from http://www.winscriptingsolutions.com, InstantDoc ID 39117.

Defining the Script Requirements
Updateattribs.vbs contains two required parameters: the input and output filenames. The input file needs to be a file that Excel can open, and the file's first row needs to contain the names of each attribute that you want to update. These attributes can be in any order, as long as the first column contains the distinguished name (DN) attribute for the AD objects that you need to modify. The script uses the DN to identify these objects. The file must then list each of these AD objects in subsequent rows (i.e., one row per object), as Figure 1 shows. The output file will contain a log of all the updates that the script makes to the AD objects you're modifying, the errors that the script encounters, and a summary of what took place during the script's execution.

Writing the Script
To run the script, use the following syntax:

updateattribs.vbs   [/v]

The optional /v parameter instructs the script to use verbose output. The verbose switch generates a lot of output through Wscript.Echo statements (even without verbose output, the script executes a fair number of Echo statements), so use the CScript host engine to run the script. If you use the WScript host engine, you'll continually need to click OK as message boxes pop up.

Updateattribs.vbs begins by reading in the specified parameters, as the code in Listing 1 shows. Then, the code in Listing 2 binds to the Root Directory Service Entry (rootDSE) object and saves that reference in the dso variable. This action, which many consider to be the best practice for performing ADSI LDAP calls, is vital for the script's performance. Each time the script uses the GetObject function to connect to an AD object, the function's underlying mechanism binds to the LDAP server. Over the course of the script, these bind operations can negatively affect the script's performance. Keeping a reference to the initial GetObject call makes additional bind operations unnecessary during subsequent GetObject calls, thus avoiding a performance problem.

After opening the specified output file in Write mode, the script opens the specified input file in Excel, as the code at callout A in Listing 3 shows. This code creates a hidden instance of the Excel Application object. The code at callout B then selects the spreadsheet region that contains data. Selecting the entire region lets the script use the range.Columns.count and range.Rows.count properties to determine the necessary number of columns and rows. The script then copies the values in the first (i.e., header) row and stores those values in an array called attributeNames.

Next, the major part of the script processes each row of spreadsheet data and updates the AD objects as necessary. (Web Listing 1 at http://www.winscriptingsolutions.com, InstantDoc ID 39117, shows a sample of this part of the script.) For performance reasons, the script updates only those AD object attribute values that differ from existing values.

First, the script uses the Range object's Cell property to access the Excel worksheet and retrieve the first AD object's DN value. Next, the script tries to get a reference to that object by making a GetObject call for that DN and storing the reference in the ADObject variable. If the call is successful, the script uses the Range object's Cell property to read in the updated values for each attribute in the attributeNames array, using the i (row) and j (column) loop variables to specify the offset of each cell. To retrieve each attribute's existing value from AD, the script uses the Get function of the IADs object that the ADObject variable references. For each set of values, the script uses the LCase function to convert both strings to lowercase letters and compares the resulting strings to determine whether they're identical. If the values differ, the script uses the IADs object's Put method to update the attribute's value, passing the attribute name and attribute value from the worksheet as parameters. After the script updates an AD object, the script uses the SetInfo method to commit the changes, as the following code exemplifies.

If (changeMade = True) Then    ADObject.SetInfoEnd If

After the script processes each row of data in the spreadsheet, it outputs a summary of the number of rows processed, AD objects updated, attributes updated, and errors encountered. The script then closes all the input and output files and quits the instance of the Excel Application object. Listing 4 shows this cleanup code.

Updateattribs.vbs is fairly straightforward. However, successful execution relies on the following tips:

The Power of Scripting
Updating AD objects' attributes doesn't need to be a tedious and error-prone task. The next time your boss hands you a spreadsheet with 1000 employee addresses and phone numbers and asks you to update the information in AD, you can confidently say that you'll be finished in no time.