Bulk update AD user objects

[msadexchman]

I've just been given an Excel spreadsheet from our HR department with the
request to populate all the fields in the spreadsheet to the respective
users object in Active Directory. There are fields such as:

Title
First Name
Last Name
Street
Department
Manager

Is it possible to update the user accounts in AD from this? I don't see a
column for "username" in the spreadsheet?

Any help would be appreciated.

 

[Richard Mueller]

I've just been given an Excel spreadsheet from our HR department with the
request to populate all the fields in the spreadsheet to the respective
users object in Active Directory. There are fields such as:

Title
First Name
Last Name
Street
Department
Manager

Is it possible to update the user accounts in AD from this? I don't see a
column for "username" in the spreadsheet?

Hi,

Assuming the "First Name" is the value of the givenName attribute and "Last
Name" is the value of the sn attribute, you have a problem. None of the
values are mandatory, much less required to be unique. You have no way to
reliably identify the users.

Assuming that the combination of "First Name" and "Last Name" is intended to
uniquely identify the users, the first step is to determine if you are lucky
enough for this to work in your environment. You can use ADO to retrieve the
user(s) with the specified combinations of names. If there is just one
match, retrieve the distinguishedName and put that in a new spreadsheet
(with the other values) that can be used later to update the remaining
attributes. In brief (not tested):

=========================
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

strAttributes = "distinguishedName"
strBase = "<LDAP://dc=MyDomain,dc=com>"

intRow = 1
Do While (objSheet1.Cells(intRow, 1).Value <> "")
strFirstName = objSheet1.Cells(intRow, 2).Value
strLastName = objSheet1.Cells(intRow, 3).Value
' Filter on users with specified first and last name.
strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(givenName=" & strFirstName & ")(sn=" & strLastName & "))"
adoCommand.CommandText = strBase & ";" & strFilter _
& ";" & strAttributes & ";subtree"
Set adoRecordset = objCommand.Execute
If (adoRecordset.EOF = True) Then
' No user found.
objSheet2.Cells(intRow, 7).Value = "User Not Found"
Else
blnFirst = True
Do Until adoRecordset.EOF
strDN = adoRecordset.Fields("distinguishedName").Value
If (blnFirst = True) Then
' This is the first user found with the names.
objSheet.Cells(intRow, 7).Value = strDN
strTitle = objSheet1.Cells(intRow, 1).Value
strStreet = objSheet1.Cells(intRow, 4).Value
strDepartment = objSheet1.Cells(intRow, 5).Value
strManager = objSheet1.Cells(intRow, 6).Value
objSheet2.Cells(intRow, 1).Value = strTitle
objSheet2.Cells(intRow, 4).Value = strStreet
objSheet2.Cells(intRow, 5).Value = strDepartment
objSheet2.Cells(intRow, 6).Value = strManager
blnFirst = False
Else
' This is a duplicate user with the same names.
objSheet2.Cells(intRow, 7).Value = "More than one user
found"
End If
adoRecordset.MoveNext
Loop
End If
intRow = intRow + 1
Loop
============

objSheet1 and objSheet2 must be bound appropriately earlier in the code.
I've assumed column numbers for the values in the spreadsheet. The new
spreadsheet (objSheet2) has a new column (# 7) with the DN of the user if
there is one match. Nothing above tested, but hopefully you get the idea.
Another program would be used to read the new spreadsheet, bind to the users
with the DN, and set the values.

Of course, the above assumes that the givenName and sn attributes are
populated. If instead you must use the displayName or cn attribute of the
user objects, the idea would be the same, but you would construct the
proposed displayName or cn from the specified first and last names. It might
be more difficult to get a match unless your names are standardized. Maybe:

strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(cn=" & strFirstName & " " & strLastName & "))"

 

[Richard Mueller]

Hi,

Just as an aside, I had a similar task a few years ago when my company sold
a business unit and I had to track user accounts in various systems to be
disabled and deleted because 1400 people left. It was a nightmare. I also
got spreadsheets from HR. I quickly saw that identifying users by name was
foolish. I found people that had changed their names 3 times during their
careers and had accounts/identities under all their names. I found users
with the exact same names. There was no standard way of putting the names
together (middle initials with our without periods, middle names,
nicknames). It took me months and in the process I found active accounts for
people that had left years earlier.

 

[Paul Bergson]

To track down user names to id's go to my website and click on the "Active
Directory Downloads" link within there select "Active Directory User Account
Attributes" this will give you a complete list in csv format that you can
open up in an Excel spreadsheet. I believe you should be able to easy sync
the two. From there you can decide how you want to update your objects.

You could then use ldifde to modify the accounts defined within your Excel
spreadsheet

http://computerperformance.co.uk/Logon/CSVDE_LDIFDE.htm

http://support.microsoft.com/kb/313823

--

Paul Bergson MCT, MCSE, MCSA, CNE, CNA, CCA
http://www.pbbergs.com

This posting is provided "AS IS" with no warranties, and confers no rights.