Solved

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

Posted on 2007-04-04
15
2,954 Views
Last Modified: 2008-06-01
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
0
Comment
Question by:Eric-arup
  • 7
  • 5
  • 3
15 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 18853419
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18853449
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
 
LVL 30

Expert Comment

by:LauraEHunterMVP
ID: 18853459
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18853466
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
 
LVL 30

Expert Comment

by:LauraEHunterMVP
ID: 18853522
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18853549
@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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18853657
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18853699
Yes the numbers are provided to me in a csv format
Firstname,Lastname,uniquenumber

e-
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18853745
Oh, alright...only problem I see with that upfront is...Firstname and Lastname can be duplicates...

What if you have 2 Mary Jones?
0
 
LVL 1

Author Comment

by:Eric-arup
ID: 18853861
@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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18853873
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18859863
@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
 
LVL 30

Accepted Solution

by:
LauraEHunterMVP earned 500 total points
ID: 18860355
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18860961
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
 
LVL 1

Author Comment

by:Eric-arup
ID: 18875806
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

Join & Write a Comment

Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
Resolve DNS query failed errors for Exchange
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 transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now