<

Active Directory - Bulk updating user information using Excel.

Published on
18,862 Points
14,362 Views
5 Endorsements
Last Modified:
Ron Malmstead
...it's been a while since I've been on Experts-Exchange.
Since I last used this site, a lot has changed...for the better!
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"

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)

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

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

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
5
Comment
2 Comments
LVL 25

Author Comment

by:Ron Malmstead
The functionality for setting a NEW password (-pwd), was intentionally left out of this sheet, but could be added if needed.
0
LVL 1

Expert Comment

by:omzz
There are some third party tools available to bulk create users and mailboxes.
One such tool is Synchronize from Imanami.
0

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month