Guy’s Scripting Ezine 42 – Modify Users

Contents for Guy’s Scripting Ezine 42 – Modify Users with Excel

 ♣

This Week’s Secret

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.

Spreadsheet Preparation

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.

Guy Recommends:  A Free Trial of the Network Performance Monitor (NPM)Review of Orion NPM v11.5 v11.5

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.

Download a free trial of Solarwinds’ Network Performance Monitor

Example 1 Simple Script to open the spreadsheet

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.

‘ ExcelOnly.vbs
‘ VBScript to open a spreadsheet
‘ Author Guy Thomas https://computerperformance.co.uk/
‘ Version 1.2 – August 22nd 2004
‘ —————————————————————–‘
Option Explicit
Dim objOU, objUser, objRootDSE, objExcel, objSheet
Dim strContainer, strDNSDomain, intRow, intCounter
Dim strPathExcel, strPhone, strOffice

‘ ——————-
‘ Open Excel Spreadsheet N.B. change path
strPathExcel = "c:\scripts\cowbridge.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

intRow = 2 ‘ Means second row in Spreadsheet
‘ intRow,2 means 2nd COLUMN, (,6 means columns F)
strPhone= Trim(objSheet.Cells(intRow,2).Value)
wscript.Echo strPhone

WScript.Quit

‘ End of example VBScript
 

Learning Points

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.

Guy Recommends:  SolarWinds’ Free Bulk Import ToolFree Download Solarwinds Bulk Import Tool

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)

Example 2 Creating a script to add telephoneNumber values

Scenario.  All users in the Cowbridge OU need their telephoneNumber changing.

 

‘ ExcelLoops.vbs
‘ VBScript to modify user properties from a spreadsheet
‘ Author Guy Thomas https://computerperformance.co.uk/
‘ Version 2.4 – August 22nd 2004
‘ —————————————————————–‘
Option Explicit
Dim objOU, objUser, objRootDSE, objExcel, objSheet
Dim strContainer, strDNSDomain, intRow, intCounter
Dim strPathExcel, strPhone, strOffice

‘ —————————-
‘ Get objOU –> LDAP Domain Root, then add OU
‘ N.B. Change strContainer
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")
strContainer = "OU=Cowbridge ,"
strContainer = strContainer & strDNSDomain
set objOU =GetObject("LDAP://" & strContainer )

‘ ——————-
‘ Open Excel Spreadsheet N.B. change path
strPathExcel = "c:\scripts\cowbridge.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

intRow = 2 ‘ Means second row in Spreadsheet
‘ intRow,2 means 2nd COLUMN, (,6 means columns F)
strPhone= Trim(objSheet.Cells(intRow,2).Value)
strOffice= Trim(objSheet.Cells(intRow,6).Value)
wscript.Echo strPhone

‘ Research LDAP properties. E.g. telephoneNumber
‘ More LDAP properties you could add to the spreadsheet
‘ Description, Office,
intCounter = 0
For each objUser in objOU
  If objUser.class="user" then
  objUser.Put "telephoneNumber", strPhone
  ‘ objUser.Put "physicalDeliveryOfficeName", strOffice
  objUser.SetInfo
  intCounter = intCounter +1
  End if
next
WScript.Echo intCounter & " Accounts phone changed " _
& strPhone
WScript.Quit

‘ End of example VBScript
 

 

Learning Points

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
objUser.Put "telephoneNumber", strPhone.  What this line does is assign strPhone to the LDAP field telephoneNumber.

Guy’s Challenge

Once your basic script works, then you can experiment with other LDAP fields like, Description, department, Title, physicalDeliveryOfficeName (Office).  To accept this challenge,
1) Open up your spreadsheet and add extra LDAP fields with their values in the second row. 
2) Add extra:  strLDAPValues = "OfficeName" to the script. 
3) Append: objUser.Put "LDAP Property", strLDAPvalue

Summary

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

• User Spreadsheet  • Ezines  •LDAP Properties  • Create Users  • Solarwinds User Import Tool

Ezine 13 Create Users  • Ezine 21 Create Users  • Ezine 23 Enable Accounts

Ezine 42 Modify Users  •Ezine 93 ADSIEdit  • Ezine 103 SamAccountName 

Ezine 134 Delete Users  • PowerShell 3 Workflow  •PowerShell Get Users  •PowerShell Create Users