It happens to all of us. Once in a while Active Directory information gets a little "out of date". People tend to change departments, get married and change their last names, change cell phone numbers or change addresses. Maintaining this information can be a challenge when you have a lot of users, but it can also be very beneficial to an organization as a whole.
This is my little "trick" to bulk updating user attributes and information in Active Directory, based on data from a Microsoft Excel sheet. The sheet uses formulas to produce a command line. The command line employs Dsquery.exe and Dsmod.exe, utility programs which are included in the Windows Server 2003 Administration Tools Pack.
An example of a command line to modify a user, using these tools, might look something like this.
This is my little "trick" to bulk updating user attributes and information in Active Directory, based on data from a Microsoft Excel sheet. The sheet uses formulas to produce a command line. The command line employs Dsquery.exe and Dsmod.exe, utility programs which are included in the Windows Server 2003 Administration Tools Pack.
An example of a command line to modify a user, using these tools, might look something like this.
...so Dsquery.exe finds the user whose "samid" = djohns, and the "DistinguishedName" result is piped directly into Dsmod.exe where we use the telephone parameter with the new value.
To produce this command line in MS Excel, I primarily use the "Concatenate" function.
In a cell, you can type the formula =concatenate("foo","bar")
This works on cells that have information too, for example =concatenate(A1,B1,C1) would join the strings that exists in cells A1, B1 and C1.
Example:
The result would be: dsquery user -samid "DJohns" | dsmod user -tel "555-555-5555"
That formula's resulting command line can then be copied and pasted directly into command prompt.
Another thing this formula must take into account is that some information might be left blank. For this scenario we wouldn't want to include that parameter in the final resulting command line, or we might not want to produce a command line at all. To solve this problem we use the IF() function in Excel. =IF({logical test},{value if true},{value if false})
Example using IF() with Concatenate():
This means we only produce a command line if the phone number in B2 is not left blank.
A demonstration of a working example can be found in the attached file: XLS-Dsquery-Dsmod.xls
Pay close attention to the "notes" and use this at your own risk. Some logic is built in; for example, "description" is auto generated from other fields, as well as the "displayName". The description field uses the "department" + "title" + "location", so if "DJOHNS" works in Human Resources, his title is Director, and his office is in Florida, his description becomes "Human Resources Director in Florida".
To use this sheet, simply populate it with user information, and then copy/paste the resulting command lines into the command prompt. Take notice that the "DEFAULT VALUES" rows were put there to enable you to copy and paste them to rows below, when adding users, in order to transpose all of the formulas that are involved in this sheet. If a row is a "DEFAULT VALUE" row, the final command line formula will NOT produce a command line. All of the columns that contain formulas are hidden, with the exception of the command line column.
This is the formula that produces the final command line:
Minimum Requirements
Windows Server 2003 Administration Tools Pack - installed (Or, Windows Server 2008 which includes dsquery/dsmod)
Microsoft Excel Runtime - installed
by: xuserx2000 on 2009-10-07 at 14:35:49ID: 4032
The functionality for setting a NEW password (-pwd), was intentionally left out of this sheet, but could be added if needed.