Results 1 to 4 of 4

Thread: Script to Update user attributes in AD

  1. #1
    Fahad Guest

    Script to Update user attributes in AD

    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.

  2. #2
    Richard Mueller [MVP] Guest

    Re: Script to Update user attributes in AD

    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
    --



  3. #3
    Join Date
    May 2009
    Posts
    2

    Re: Script to Update user attributes in AD

    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

  4. #4
    Join Date
    May 2009
    Posts
    2

    Re: Script to Update user attributes in AD

    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

Similar Threads

  1. Script that allows me to Switch User
    By The Recruiter in forum Software Development
    Replies: 6
    Last Post: 05-01-2010, 09:39 PM
  2. How to get the value of AD user attributes in vbscript?
    By PARRISH in forum Active Directory
    Replies: 2
    Last Post: 20-11-2009, 11:13 PM
  3. how to update or modify AD properties attributes
    By vivekmohan in forum Software Development
    Replies: 2
    Last Post: 02-09-2009, 09:01 PM
  4. Create and Update the attributes of XML file
    By TechPredator in forum Software Development
    Replies: 4
    Last Post: 30-04-2009, 11:34 AM
  5. Delegate rights to change User Attributes
    By Robbin M in forum Active Directory
    Replies: 2
    Last Post: 12-02-2009, 12:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •