Solved

How can I use Excel or CSV files to modify an Exchange Custom Attribute in Active Directory?

Posted on 2008-06-17
6
498 Views
Last Modified: 2010-04-21
I need to use Exchange Custom Attribute 15 to allow for display of our internal 7-digit dialing (xxx-xxxx)... I have edited the template within Exchange 2007 so that it points to, and displays, the data contained within this attribute. I also have a spreadsheet containing the user account name, email address, etc. and have added a column that matches the users to reflect the data I wish to populate within the attribute...
I can manually edit the attribute within ADUC but would like to use a script or another method since I have some 2800 users to update...
Any help is appreciated!
0
Comment
Question by:gdozier
  • 4
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
aces4all2008 earned 500 total points
ID: 21808119
Something similar to the following cmd file should work for you.  You'll need to run it from a server 2003 machine (something with ldifde.exe anyway) and modify it to match your environment.  The script is set up to use csv file with only 2 fields: username and attribute value.  You should test this before using it on all 2800 users.
Set CsvFile=InputFile.csv
Set LdfFile=CustomAttr.ldf
Set AttrName=Name
Set LogFIle=ImportLog.txt
 
 
for /f %%a in (%CsvFile%) do call :MakeLdf %%a %%b
Call :WriteAttributes
 
Goto :End
 
:MakeLdf
Set UserID=%1
Set AttrValue=%2
 
for /f "delims=|" %%i in ('dsquery user -samid %UserID%') do echo dn: %%i>>%LdfFile%
echo changetype: modify>>%LdfFile%
echo replace: %AttrName%>>%LdfFile%
echo %AttrName%: %AttrValue%>>%LdfFile%
echo ->>%LdfFile%
echo.>>%LdfFile%
 
Goto :End
 
:WriteAttributes
 
ldifde -i -f %LdfFile% 
 
:End

Open in new window

0
 

Author Comment

by:gdozier
ID: 21809569
so to be sure I understand; this script takes a csv file with the two fields (username; attribute) and makes the ldf file which is then used to populate the attribute?
If I am missing something, please elaborate... I have a test domain on which I can test with using any number of users... If it works for 10 should work for all, correct?
0
 

Author Comment

by:gdozier
ID: 21812578
On another note, when the script is copy/pasted; in what type of format should the file be saved in order to utilize? And do I call the file using ldifde?
I know I appear ignorant and perhaps for good reason!  :)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:gdozier
ID: 31468129
Works perfectly with one exception: When the LDF file is created it places the DN data within quotes which causes a syntax error... Do a "find/replace" on the LDF file to remove the quotes and then manually run ldifde against the edited LDF file and all is good to go... I simply couldn't ascertain where the quotes were being inserted; if you can correct this in the above script then it will work without any edits other than those required for each environment...
0
 
LVL 6

Expert Comment

by:aces4all2008
ID: 21850959


"...when the script is copy/pasted; in what type of format should the file be saved in order to utilize?"

Save it as a cmd file using notepad

"...this script takes a csv file with the two fields (username; attribute) and makes the ldf file which is then used to populate the attribute?"

Yes

"...If it works for 10 should work for all, correct?"

Yes

"...Works perfectly with one exception: When the LDF file is created it places the DN data within quotes which causes a syntax error... "

See code snippet below


Good luck to you





Set CsvFile=InputFile.csv
Set LdfFile=CustomAttr.ldf
Set AttrName=Name
Set LogFIle=ImportLog.txt
 
 
for /f %%a in (%CsvFile%) do call :MakeLdf %%a %%b
Call :WriteAttributes
 
Goto :End
 
:MakeLdf
Rem Generates ldf file for use with ldifde application from csv fil
 
Set UserID=%1
Set UserDN=
Set AttrValue=%2
 
for /f "delims=|" %%i in ('dsquery user -samid %UserID%') do Call :FormatDN %%i
echo dn: %UserDN>>%LdfFile%
echo changetype: modify>>%LdfFile%
echo replace: %AttrName%>>%LdfFile%
echo %AttrName%: %AttrValue%>>%LdfFile%
echo ->>%LdfFile%
echo.>>%LdfFile%
 
Goto :End
 
:WriteAttributes
Rem Writes attributes from ldf file to active directory
 
ldifde -i -f %LdfFile% 
 
Goto :End
 
:FormatDN
Rem Removes quotes (first and last characters) from around distinguished name
 
Set UserDN=%1
Set UserDN=%UserDN:~1,-1%
 
Goto :End
 
:End

Open in new window

0
 

Author Comment

by:gdozier
ID: 21856243
Perfect!! Thanks for the code addition in addition to your previous assistance!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server 2012 management. 5 24
exchange 2007 - Message not received 10 32
How to remove the name in calendar in Outlook 2016 3 36
Locating a GPO setting 3 29
Find out what you should include to make the best professional email signature for your organization.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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