<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Active Directory - Bulk updating user information using Excel.

Published on
19,081 Points
14,581 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
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month