Modifying an attribute in AD for multiple users (CSVDE / ADModify)

Hello

I have a field in AD, in this case 'mailNickname', that I want to change for 1600 users.  The value for each user will be different and the attribute is currently populated with an attribute I would like to discard.

I looked at CSVDE to do this.  However CSVDE seems to only populate fields that are blank.  When I try to change the field I get an error that says "Add error on line 2: Already Exists".  I can run it again with a -k to ignore the error but it still doesnt modify the attribute.  Since I have a large user base I need to be able to imput a file that has the changes I have.

So I moved on to ADModify, however ADModify seems to set one attrubite to one thing for everyone.  And since i need to set and attribute to a unique value for each person that doesnt seem to work.  There is a admodcmd.exe command line version but the help on this is limited, and i dont see a way to point it to a input file, and even if I can I have no idea what that file formatting would be.

any help is GREATLY appreciated.

Eric
LVL 1
Eric-arupAsked:
Who is Participating?
 
LauraEHunterMVPConnect With a Mentor Commented:
Hmmmm.  I'm running into similar difficulties as you in getting the csv stuff to work in admod - the writer of the tool just added the CSV functionality maybe a month ago and I'll admit that even -I'm- still playing with it a bit.

Try this on for size since I had 20 minutes to kill before a meeting just now: create an Excel spreadsheet containing 2 columns, with column headers in Row 1:

UserDN, newValue (don't enclose the DN value in quotation marks, it will create an error.)

The following VBScript will loop through the Excel spreadsheet one row at a time, bind to the UserDN specified in Column1, and set the mailNickname attribute to the value specified in Column2.  I know you said that you have limited VBScript experience, but if you read through the code it should be fairly clear what's going on.

To give credit where it's due, I stole the shell of this script from my friend Richard Mueller's website: www.rlmueller.net - he has lots of pre-made VBScripts up there that you might find useful.

Option Explicit

dim strExcelPath, strAttr, objFSO, objShell, objExcel, objSheet, intRow, strDN, strNewVal, objUser

' Specify spreadsheet. & object being modified
strExcelPath = "c:\Users.xls"
strAttr = "mailNickname"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Wscript.Shell")

' Open spreadsheet.
Set objExcel = CreateObject("Excel.Application")

On Error Resume Next
objExcel.Workbooks.Open strExcelPath
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Unable to open spreadsheet " & strExcelPath
    Wscript.Quit
End If
On Error GoTo 0
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Start with row 2 of spreadsheet.
' Assume first row has column headings.
intRow = 2

' Read each row of spreadsheet until a blank value
' encountered in column 2 (the column for the value).
' For each row, bind to the user and set the attribute value.
Do While objSheet.Cells(intRow, 2).Value <> ""
    ' Read values from spreadsheet for this user.
    strDN = Trim(objSheet.Cells(intRow, 1).Value)
    strNewVal = Trim(objSheet.Cells(intRow, 2).Value)

    ' Bind to the user object being modified
    On Error Resume Next
    Set objUser = GetObject("LDAP://" & strDN)
    If (Err.Number <> 0) Then
        On Error GoTo 0
        Wscript.Echo "Unable to bind to user: " & strDN
        Wscript.Quit
    End If
    On Error GoTo 0

    ' Set the mailNickname attribute to the value listed in column 2
    objUser.Put "mailNickname", strNewVal
    objUser.SetInfo
    ' Increment to next user.
    intRow = intRow + 1
Loop

Wscript.Echo "Done"

' Clean up.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objUser = Nothing
Set objSheet = Nothing
Set objExcel = Nothing
Set objFSO = Nothing
Set objShell = Nothing
0
 
sirbountyCommented:
Could probably create a vbscript using adsi that would do it - if you're interested, tell me more on the unique value you wish to set.  Is it based on a current property per user, or would you have it in a file or something?
0
 
Eric-arupAuthor Commented:
That is a good idea.

I probably should have mentioned that i have no VB Scripting knowledge.

: \

The unique value will be a string of numbers either 5 or 6 characters long, 90% of them will be 5 characters long.  Its a number that doesnt currently exist in any other field.  65% of my enviroment is like that now, however all the others are characters and numbers.  Management wants them all to be unique numbers that are being provided to me by another department.


e-
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LauraEHunterMVPCommented:
Actually the "Already exists" error happens because CSVDE will only create new objects, it won't modify existing ones.

adfind.exe (free command-line tool from www.joeware.net) will modify existing objects using CSV input, or else you can use the FileSystemObject in vbscript to read in from a CSV or an Excel spreadsheet.
0
 
sirbountyCommented:
I'm still a bit fuzzy on where this number is coming from...will it be the same for every user?
If not, what determines 'who' gets 'what'?
0
 
LauraEHunterMVPCommented:
Take a look at the following VBScript that will take a list of existing AD users and modify an attribute, with data being pulled from an Excel spreadsheet.  It's not a 100% match to what you're trying to do, but it's thematically close enough that it should get you 90% of the way there:

http://www.rlmueller.net/UpdateUserProfile.htm
0
 
Eric-arupAuthor Commented:
@sirBounty

Its a number assigned by HR, its like an employee ID number.  But its not the employee ID ( i am aware of that attribute in AD) Its unique for each person.  And its currently in place for about 1000 people.  And incorrect for the rest.  HR predetermines who gets what number by some metric that doesnt involve me or my department, however developers are going to start using LDAP queries against it in their applications and they want our helpdesk who uses ADUC to be able to modify it.  We dont have the knowhow in house to add a property sheet to ADUC, so we are using this mailNickname attribute.
0
 
sirbountyCommented:
Okay - but if you've got a list of users:

John Doe
Jane Smith
Jim Williams

How are you providing/supplying the unique code for each user?
Do you have a reference sheet of some sort?
0
 
Eric-arupAuthor Commented:
Yes the numbers are provided to me in a csv format
Firstname,Lastname,uniquenumber

e-
0
 
sirbountyCommented:
Oh, alright...only problem I see with that upfront is...Firstname and Lastname can be duplicates...

What if you have 2 Mary Jones?
0
 
Eric-arupAuthor Commented:
@sirBountry

I am looking into duplicates, but i dont believe we do.  We are putting MI into the 'firstname' in those cases so those values are unique.

@Laura

I am trying the ADFind and AdMod in a test enviroment, i'll let you know.


thanks guys :)
e-
0
 
sirbountyCommented:
Well, Ill stand down until you know if the other tools are going to work.
Building a script will take time...I dont want to work on one that might not get used...

Let me know...
0
 
Eric-arupAuthor Commented:
@Laura

AdMod seems to be working.  I have been sucsessful modifying single attributes entirly from command line.  However I seem to be having problems with importing from CSV files, the help files are lengthy and they discuss a lot of very cool and complex things you can do but they seem to miss on basic stuff.  My csv is pretty basic, just DN,objectclass,description,sAMAccountName and the the values in comma delimination with the DN in quotes.  I am using the command admod.exe -h 10.1.1.1:380 -csv -add -import users.import.csv     the process hangs.

Since it hangs that makes me think I have something in my csv that it doesnt understand.  My values are as follows
DN,objectClass,description,sAMAccountName
"CN=Doe\, John,CN=Users,DC=testcompany,DC=com",user.JOHNSDESCRIPTION,john.doe

I must be missing something in syntax or switches...


@sir

Thanks for the help I think i'll continue down the admod road, once I completly understand it I'll be able to use it for other things especially if these types of issues come up again.


e-
0
 
Eric-arupAuthor Commented:
I have to run out to a remote site here in a few minutes, I'll play with this and report back tomorrow.

thanks
e-
0
 
Eric-arupAuthor Commented:
ok sorry for the late response.  I was able to get both to work.

for the script, thank you :)

As for ADMod here is the command line i used.  In this example i am feeding in a csv file that changes the description for 5 users.

admod.exe -h localhost description::{{.}} -saftey 500 -exterr -csv - expand -csvmodnull IGNORE < user.import.csv

the CSV file looks like this...

dn,description
"CN=Doe\, John,CN=Users,DC=mycompany,DC=com",USR_DESCRIPTION1
"CN=Doe\, Jacob,CN=Users,DC=mycompany,DC=com",USR_DESCRIPTION2
"CN=Doe\, Jane,CN=Users,DC=mycompany,DC=com",USR_DESCRIPTION3
"CN=Doe\, Jill,CN=Users,DC=mycompany,DC=com",USR_DESCRIPTION4
"CN=Doe\, Jack,CN=Users,DC=mycompany,DC=com",USR_DESCRIPTION5


My problem seemed to stem from not using the -expand and -csvmodnull IGNORE switches, allthought I admit I dont completly understand these switches...... yet.

Thank you for the help.
E-

0
All Courses

From novice to tech pro — start learning today.