Active Directory - Bulk updating user information using Excel.

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.
 

dsquery user -samid "DJohns" | dsmod user -tel "555-555-5555"

Select all Open in new window

...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") and the result would be: foobar
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:  
 

=CONCATENATE("dsquery user -samid"," """,A2,"""," | dsmod user -tel ",B2)

Select all Open in new window

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():

 

=IF(b2="","",CONCATENATE("dsquery user -samid"," """,A2,"""," | dsmod user -tel ",B2))

Select all Open in new window

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:
 

=IF(A7="DEFAULT VALUES","",CONCATENATE("dsquery user -samid ",A7," | dsmod user",C7,E7,G7,I7,K7,M7,O7,Q7,S7,U7,W7,Y7,AA7,AC7,AE7,AG7,AI7,AK7,AM7))

Select all Open in new window

Minimum Requirements
Windows Server 2003 Administration Tools Pack - installed (Or, Windows Server 2008 which includes dsquery/dsmod)
Microsoft Excel Runtime - installed
xLS-dsquery-dsmod-example.xls