Updating AD Attributes—Revisited

"Easily Update AD Attributes," July 2003, InstantDoc ID 39117, provides a script that updates attributes in Active Directory (AD) by using a comma-separated value (CSV) file or Microsoft Excel spreadsheet. Since the article's publication, I've received numerous email messages from administrators around the world telling me that the updateattribs.vbs script has saved them countless hours in their efforts to keep their AD user information up-to-date during moves and mergers.

Updateattribs requires that you supply the distinguished name (DN) of the users whose attributes you want to change. You can easily find a user's DN by using the Microsoft ldifde.exe, csvde.exe, or dsquery.exe command-line tool. However, some of the administrators who wrote to me had questions about how to obtain the DNs for their users, so I decided to make the script easier to use by revamping it to take the username as input. I also changed the parameters from unnamed to named.

The Original Version
Let's step back for a few minutes and look at how the original script operates. It requires at least two parameters. The first parameter is the input file formatted as a CSV file or Excel spreadsheet. The second parameter is the name of an output text file in which the script writes a detailed account of every action that it performs.

Figure 1 shows a sample input file, which has a header row that lists the names of the attributes to update in order starting with the DN. For each line in the input file (skipping the header row, of course), the script uses the Lightweight Directory Access Protocol (LDAP) provider to bind to the object's DN, then updates each attribute with the values specified in the input file. To save time, the script updates a value only when the value in the input file is different from the value in the AD object. Finally, the script outputs the results to the output file and writes statistics regarding how many objects were updated, how many attributes were updated, and how many errors occurred.

The New Script
The old script works well and executes fairly quickly, but as I mentioned above, it requires that the script's user provide an accurate DN for each user account he or she wants to modify. Because DNs aren't the typical format we use to identify our users, I decided to change the script so that it would use usernames instead. We all know how to retrieve our users' usernames, so it makes sense to use the username instead of the DN as the identifying field. However, to make changes to objects in AD by using LDAP, the script needs to convert the username to its DN.

In AD, the username is stored in a user account's sAMAccountName attribute. Thus, for each username in the input file, the script can query AD for a user object in which the sAMAccountName equals the username. The sAMAccountName attribute must be unique for each user so that the script's query will return one result, from which the script can extract the distinguishedName attribute, or no result, in which case the user doesn't exist in the domain. After obtaining the DN, the script can use it as the basis for the LDAP provider calls to update the desired user attributes.

Another change I made to the script was to use named instead of unnamed arguments. Named arguments let you specify the script's parameters in any order. For example, in a typical script, parameters are specified in a sequential order, as in

myscript.vbs parameter1
 parameter2

whereas named arguments can be specified as follows

myscript.vbs /p1:parameter1
 /p2:parameter2

or

myscript.vbs /p2:parameter2
 /p1:parameter1

The second and third examples are equivalent because each argument is labeled and can be retrieved from inside the script in any order. To find out whether a named argument was specified by the user, you use the WScript.Arguments.Named.Exists method, passing in the name of the parameter you want to check for. To retrieve the value of the named parameter, you use the WScript.Arguments.Named.Item function, which takes the name of the argument you want to retrieve and returns its value. Listing 1 shows an example of how named parameters are checked, retrieved, and stored in variables. Although using named arguments isn't a technical necessity for this script, it makes the script's syntax more familiar to users who are accustomed to specifying switches for command-line utilities.

Script Details
The syntax to run the new script is

updateattribs.vbs /i:inputfile
 /o:outputfile
 /basedn:DC=COMPANY,DC=COM
 /v:yes

where /i specifies the input file, /o specifies the output file, /basedn specifies the DN of the container (or node in the AD tree) in which to search for user accounts, and /v specifies whether to provide verbose output to the console. The first thing the script does is collect the values for the three required arguments: the input and output file names and the base DN. The fourth parameter, /v, is optional. The input file name specifies a CSV file or Excel spreadsheet such as that shown in Figure 2. The file or spreadsheet must have a header row that lists the attribute names to be updated, and the first column is reserved for the username. Each subsequent row contains the values for a user whose attributes require updating.

As you can see, the new input file is much simpler than the old one. Instead of having to specify a complete DN for each user object, you simply specify the username you want to update. The new input file and script provide the added advantage of being able to accommodate changes in your AD organization without you having to change the input file. If jsmith moves from one organizational unit (OU) to another, the input file is still valid because, unlike the DN, the username (i.e., sAMAccountName) doesn't change when an object moves.

To enhance performance, the script binds to LDAP://RootDSE and keeps a reference to it within the script, as Listing 2 shows. This technique effectively keeps a channel open between the LDAP provider and the server so that subsequent calls to the GetObject method execute much more quickly.

The script then uses the OpenTextFile function of Scripting.FileSystemObject to initialize the output file by creating it. And the script opens the input file by creating an instance of the Excel.Application COM object. It reads in the attribute names that are listed in the header row and stores them in the attributeNames array. This array will be used later to specify which attributes will be updated for the users. To see the code that performs these steps and download the complete code for the new Update-attribs script, go to http://www.windowsitpro.com/windowsscripting, enter InstantDoc ID 45814 in the InstantDoc ID text box, and click the 45814.zip hotlink.

At this point, the script's main loop starts to process each record in the input file. Starting with the second row in the file (the first detail row), the script reads in the first column—the user's username. The code then uses ActiveX Data Objects (ADO) to set up the ADs Provider to query AD starting from the base DN specified in the command-line arguments and look for a user object with a sAMAccountName that matches the username, as Listing 3 shows. If the result set contains a value, the script has found the user; if not, the script outputs an error and continues to the next record. When there's a match, the script reads the distinguishedName attribute into the dn variable and binds to it by using the GetObject method. This method returns a reference to the object, which the script stores in the ADObject variable.

The script can now update the attributes for the user objects referenced by ADObject. The script accomplishes this task by looping through the attributeNames array and checking the value of each attribute in AD and in the input file. If the values are different, the script uses the object's Put method to update the AD value. The Put method actually makes the change in the local cache rather than to AD. To make the changes permanent, the script commits them using the object's Setinfo method.

During its execution, the script uses a number of variables to keep a count of the number of objects and attributes that it's processed and updated as well as any errors it's received. All this information is used to create a summary that the script sends to both the screen and the output file. You'll also notice that when an attribute is updated, the script records the old and new values to the output file, so you can always use the output file to verify that changes have been made or to revert to the old values if necessary.

Running the Script
I've tested the new Updateattribs script on Windows XP Service Pack 1 (SP1) and SP2 and Windows 2000 Professional SP3 and SP4 machines, against both Windows Server 2003 and Win2K Server ADs. The script requires Excel 2000 or later to be installed (for parsing the input file) and has been tested for use with Excel 2003, Excel 2002, and Excel 2000. Due to the large amount of output the script generates when you enable verbose output, I highly recommend that you use cscript.exe to execute it.

Here are a few other tips for using the script:

• Make sure you use the full path name to specify the input file (e.g., C:\scripts\myfile.csv). If the path includes any spaces, enclose the entire string in double quotes (e.g., "C:\My Documents\test.csv").
• Make sure the attribute names you specify in your input file are exactly as they appear in AD (you can use adsiedit.msc or any similar tool to verify this). Attribute names are case sensitive.
• If the script is unable to resolve usernames into DNs, make sure the base DN you specified is correct. For simplicity's sake, I typically use the domain name so that I can search the entire directory (e.g., DC=COMPANY,DC=COM; DC=CORP,DC=COMPANY,DC=COM).
• If you have a large number of users and you know that all the users in your input file are contained within a particular OU structure, you can speed up the process by specifying a more granular base DN (e.g., OU=SALES,DC=COMPANY,DC=COM or even "OU=New York,OU=Sales,DC=COMPANY,DC=COM").

As with all new things, make sure you test Updateattribs in your test lab before using it in production. The script executes very quickly. Even in tests against an AD containing more than 100,000 objects, the script takes less than a second to update each record.

End of Article