Guy's Scripting Ezine 42 - Modify Users
Contents for Guy's Scripting Ezine 42 - Modify Users with Excel
CSVDE does not MODIFY existing Active Directory objects. I mention this limitation because each week people write and ask me how to modify a user's attributes with CSVDE. And it breaks my heart to have to explain that you need VBScript or LDIFDE to modify existing users.
Now do not get me wrong, CSVDE is a great utility if you need to bulk import 100s of new users from a spreadsheet. I also love CSVDE because it taught me so much about LDAP attributes. To paraphrase Clint Eastwood, ' A command must know his limitations'.
This week's project
Since we cannot use CSVDE to modify an existing user, we will employ VBScript instead. Just like CSVDE feeds from information stored in a spreadsheet, so will our VBScript read those same cells of a spreadsheet.
I am worried because this script is difficult. The solution is to go slowly and build up confidence by analysing each component of the script.
Active Directory Preparation
Firstly, please check Active Directory. Can you identify the name of my test OU? The answer is that I am using an OU called Cowbridge as a test bed for my script (See Line 13). So, either create a Cowbridge OU in your domain, or else amend Cowbridge in the script to the name of your test OU.
Next you need a spreadsheet. In the first row put the LDAP fields, for example in Row 1, Column B (b1) enter telephoneNumber. telephoneNumber is a precise LDAP attribute that Active Directory understands. In the second row under column B (Cell b2) enter the phone number you want to add to your users. (We can pretend everyone in Cowbridge uses the same office phone number.)
Where is my spreadsheet saved? Line 13 (22) tells us: strPathExcel = "c:\scripts\cowbridge.xls" Now all you have to do is save your file to the same path, else change the name in line 13 (22) to reflect YOUR spreadsheet name and YOUR path.
SolarWinds' Orion performance monitor will help you discover what's happening on your network. This utility will also guide you through troubleshooting; the dashboard will indicate whether the root cause is a broken link, faulty equipment or resource overload.
What I like best is the way NPM suggests solutions to network problems. Its also has the ability to monitor the health of individual VMware virtual machines. If you are interested in troubleshooting, and creating network maps, then I recommend that you try NPM now.
Scenario. In the build up to the main script, you want to check that the spreadsheet has been created properly, and the actual phone number is in the correct cell.
Note 0: Suggestion from DA - add objExcel.Quit. Otherwise you will see zillions of Excel applications in task manager. (Guy added objExcel.Quit to later scripts but forgot here).
Note 1: Set objExcel = CreateObject("Excel.Application") This is where we create a spreadsheet. The idea is to adapt the CreateObject method to be Excel.Application, rather than a Notepad application or even a Network object. (In other scripts we use:
Note 2: Once we create the Excel object we put it to work to open the file at the end of the strPathExcel.
Note 3: intRow =2 is vital to tell VBScript to look on the second row (not the top row)
Note 4: objSheet.Cells(intRow,2).Value. Question what does intRow,2 mean? The answer is Column B, the second column. So if you have other LDAP attributes amend this value according to the Column you place the values.
Import users from a spreadsheet. Just provide a list of the users with their fields in the top row, and save as .csv file. Then launch this FREE utility and match your fields with AD's attributes, click and import the users.
Optionally, you can provide the name of the OU where the new accounts will be born. Download your FREE bulk import tool.
If you need more comprehensive software, download a free trial of SAM (Server & Application Monitor)
Scenario. All users in the Cowbridge OU need their telephoneNumber changing.
Note 0: Check out the user's properties, open Active Directory Users and Computers and navigate to your OU (equivalent of Cowbridge).
Note 1: This script amends ALL the users in the named OU Cowbridge. Technically this is controlled by the For... Next Loop. Logically, you would only choose values like physicalDeliveryOfficeName (Office), where all the user's genuinely needed the same value, for example they are all in the SWales office.
Note 2: Pay close attention to
Once your basic script works, then you can experiment with other LDAP fields like,
Description, department, Title, physicalDeliveryOfficeName (Office).
To accept this challenge,
While CSVDE does not allow you to modify Active Directory objects directly, the secret is to store the attributes in a spreadsheet then use CSVDE to import into your Active Directory. If your aim is to modify existing objects then VBScript (unlike CSVDE) will permit addition or alteration of LDAP properties.
See More Active Directory VBScripts to Create Users