Script to Update user attributes in AD

[Fahad]

Dear All

we are in process of updating all AD user information based on the
information exported from the HR DB. user info in HR DB will be exported to
excel sheet and my task is to write these info to the AD existed accounts.
the link field between excel sheet & AD account is the email address.

i have to read this email from excel sheet, then search AD for this email.
if it's found it then I have to update his info based on the excel sheet.

all common attributes in AD will be updated including (1st name, initials,
last name, display name, office, telephone, street, ...etc).

almost the data in AD is accurate but we need to rely on HR DB as main
reference for the user info.

now since the HR don't include all these information (i.e may be some fields
in the excel will be blank), then i need to reflect that in matched AD
attribute. example if the excel sheet don't have the phone number for certain
user while it's present for this user in AD, then I need to remove the phone
number in AD.

the script should check if the data in excel is same as in AD, then no need
write it to AD again. also if the both values in excel & in AD are empty,
then no need to do any thing.

i know this is a big task but i appreciate any help or idea.

[Richard Mueller [MVP]]

Fahad wrote:

> we are in process of updating all AD user information based on the
> information exported from the HR DB. user info in HR DB will be exported
> to
> excel sheet and my task is to write these info to the AD existed accounts.
> the link field between excel sheet & AD account is the email address.
>
> i have to read this email from excel sheet, then search AD for this email.
> if it's found it then I have to update his info based on the excel sheet.
>
> all common attributes in AD will be updated including (1st name, initials,
> last name, display name, office, telephone, street, ...etc).
>
> almost the data in AD is accurate but we need to rely on HR DB as main
> reference for the user info.
>
> now since the HR don't include all these information (i.e may be some
> fields
> in the excel will be blank), then i need to reflect that in matched AD
> attribute. example if the excel sheet don't have the phone number for
> certain
> user while it's present for this user in AD, then I need to remove the
> phone
> number in AD.
>
> the script should check if the data in excel is same as in AD, then no
> need
> write it to AD again. also if the both values in excel & in AD are empty,
> then no need to do any thing.
>
> i know this is a big task but i appreciate any help or idea.

I've used VBScript (and VB) programs to update users many times. The program
can get very complex, depending on your requirements, but makes bulk changes
much more convenient. An example that updates only the profilePath attribute
for users from the information in an Excel spreadsheet is linked here:

http://www.rlmueller.net/UpdateUserProfile2.htm

This example identifies users by the "pre-Windows 2000 logon name". It uses
the NameTranslate object to convert this into the Distinguished Name. This
is more efficient than using ADO to search for the user. In this example, if
the value in the spreadsheet is blank, nothing is done. If the value is the
special string ".delete", the value of the attribute is cleared. Your
requirements are more complex. The example traps and handles possible
errors, which is important when a script makes bulk changes. Many things can
go wrong and it is best to not have the program halt if there is a minor
problem affecting only one user, but you still want to know what happened
(never ignore errors).

Identifying users by email address is risky. The email address is not
required in AD. Also, there are two attributes of user objects possible. The
first is the "mail" attribute, which is single-valued and is displayed on
the "General" tab of ADUC. Nothing requires that the value be unique, or
even valid. The second is the "proxyAddresses" attribute, which is
multi-valued and used if Exchange has extended the schema. The addresses in
the proxyAddresses collection are prefixed by either "smtp:" or "x400:". The
one and only "primary" address has the prefix in all capitals, all the
others have lower case prefix. These values should be unique, but I don't
believe anything enforces this if you update addresses in code.

It would be better to identify users by either distinguishedName or
sAMAccountName. If you use email addresses, then you can use ADO to retrieve
the user(s) that have the specified address. The code must check that there
is only one user found. The ADO query would retrieve the value of the
distinguishedName, so you can bind to the user object. For information on
using ADO to search AD, see this link:

http://www.rlmueller.net/ADOSearchTips.htm

In this case, the filter clause could be similar to either:

strFilter = "(&(objectCategory=person)(objectClass=user)(mail=" & strEmail &
"))"
or
strFilter =
"(&(objectCategory=person)(objectClass=user)(proxyAddresses=SMTP:" &
strEmail & "))"

In the later case, ADO is case insensitive, so we cannot guarantee this is
the "primary" address. The value of strEmail is the email address read from
the spreadsheet. ADO cannot update objects in AD, so you must retrieve the
value of the distinguishedName attribute so you can bind to the user object.

The attributes you listed are all single valued strings, so they are easy to
update. The code in the link above can be used as a guide. However, some
attributes are multi-valued, so other techniques are required. Also, some
attributes are dates, some are boolean or flags, etc. See the first
spreadsheet in this link for the LDAP display names of attributes that
correspond to fields in ADUC:

http://www.rlmueller.net/UserAttributes.htm

The attributes you refer to are: givenName, initials, sn, displayName,
physicalDeliveryOfficeName, telephoneNumber, and streetAddress.

A program can be coded that requires that each value be in a specified
column of the spreadsheet. A better plan might be a program that reads the
attribute names (the LDAP display names) in the first row of the
spreadsheet. If your spreadsheet is generated by HR, you probably have no
control over the format. If so, the format must be known before anyone can
start.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

[BKelsey]

This script is exactly what I am looking for. I have one problem with it. I can see where it will display an error if it cannot find the input spreadsheet, but I can't seem to find where to define the path and file name. Can you please tell me where to put that information so I can run the script?

Thank You,
Brian Kelsey

[BKelsey]

Ok, I figured that out. I am fairly new to scripting and realized you had to call the script and the spreadsheet at the same time. It worked great for adding one attribute at a time. Can you give a sample of how to change it to modify two attributes, like Department and street?

Thanks,
Brian