Looking for ideas to get user properties from Active Directory

Question

Looking for ideas to get user properties from Active Directory

Coriolan

SSCommitted

Points: 1931
More actions
December 11, 2006 at 5:29 pm

#175570

1. I have a list of users first and last names
For example:
Last Name                   First name
------------------------------------
Lemanski                         Sue
Norman                           Tim
2. Using their first and last name, I need to access our company global catalog to get their domain within the company and their other properties such as PersonID and email address.  For that purpose, I have a query as below:
select o.displayname, o.mail,
(Substring(o.distinguishedname, charindex('DC',o.distinguishedname) + 3, 4) + '\'
+ Substring(o.distinguishedname, charindex('CN',o.distinguishedname) + 3, 6)) as DOMAINPersonID
from
(SELECT *
FROM   OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, distinguishedName, displayName,sn,givenname
FROM ''GC://USxxxx'' where objectClass = ''User'' and objectCategory = ''Person''
and sn=''mendonca'' and givenname=''jorge'' ')
) as o

Note the hard-coded surname and givenname thus I can only query one person at a time.

3. I am looking for ideas on how to feed a list of user first and last names to an SQL Query or database object and get user properties from Active directory( in my case it will be a global catalog).
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

First and last name doesn't guarantee uniqueness, though, in any directory. Even a small organization can have two John Smiths. But beside that... are you just trying to do a lookup one at a time? Or are you desiring to query all the users and populate a table accordingly?

K. Brian Kelley
@kbriankelley

michaela SSCertifiable Points: 5649 More actions · Answer 2

Being a bulk operation, you can use VBScript in a SSIS/DTS package:

Script: Retrieves user account attributes found on the Account page of the user account object in Active Directory Users and Computers:

On Error Resume Next

Set objUser = GetObject _

("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com")

objUser.GetInfo

strUserPrincipalName = objUser.Get("userPrincipalName")

strSAMAccountName = objUser.Get("sAMAccountName")

strUserWorkstations = objUser.Get("userWorkstations")

Set objDomain = GetObject("LDAP://dc=fabrikam,dc=com")

objDomain.GetInfoEx Array("dc"), 0

strDC = objDomain.Get("dc")

WScript.echo "userPrincipalName: " & strUserPrincipalName

WScript.echo "sAMAccountName: " & strSAMAccountName

WScript.echo "UserWorkstations: " & strUserWorkstations

WScript.echo "dc: " & strDC

Script: Retrieves user account attributes found on the Profile page of the user account object in Active Directory users and Computers.

On Error Resume NextSet objUser = GetObject _  ("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com")objUser.GetInfo strProfilePath = objUser.Get("profilePath")strScriptPath = objUser.Get("scriptPath")strHomeDirectory = objUser.Get("homeDirectory")strHomeDrive = objUser.Get("homeDrive") WScript.echo "profilePath: " & strProfilePathWScript.echo "scriptPath: " & strScriptPathWScript.echo "homeDirectory: " & strHomeDirectoryWScript.echo "homeDrive: " & strHomeDrive

Coriolan SSCommitted Points: 1931 More actions · Answer 3

Thanks, Michaela. Is there a way to the entire output to a text file instead of echo for each person?

Coriolan SSCommitted Points: 1931 More actions · Answer 4

Kudos, Bkelly. You raised a problem that I did encounter. I did have multiple people by the same first and last names or even people whose First Name is for example Kenneth in AD and they go by Ken.

So my list has Ken as first name and not Kenneth.

In my scenario, that will a on the 'Rejects Report'. Rejects Report will be 'eye balled' to determine the error.

I reckon there will be a manual effort to scrutinize the 'not found' or 'ambiguous' name information.

The correction will go by the person's org name for uniqueness if possible. If not, it will be returned to the requestor and the requestor will have to supply the PERSON ID which is unique in our company but takes longer time to obtain.

Thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Okay, I'm going to look at it from a different perspective since my "day job" is a directory services administrator. First question, where does the list of first name/last name come from? It would seem to me to do something similar to what michaela has posted... retrieve all of the user accounts from Active Directory using a script to loop through them and that will let you retrieve all of the user information you are interested in and post the information into SQL Server. Everything you've indicated thus far Authenticated Users has read access to, so that's not a problem on the security side.

As far as getting out of writing to text and into a database, you can use CreateObject to create an ADODB.Connection and go from there... there are plenty of those kinds of examples on MSDN.

K. Brian Kelley
@kbriankelley

Coriolan SSCommitted Points: 1931 More actions · Answer 6

The list of user names comes from 'managers'. These managers have a set of users/clients that they want to receive feedback from. They especially know their users/clients by name not by PERSON ID. Since they are managers, they don't have the 'extra' time to do a PERSON ID look up. leaving the leg work to me instead.

I will search msdn. thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

If you can do a pull, say nightly, then they can search against the DB fairly quickly and you can use an outside script to use all of the relevant data. If it needs to be real time, however, you're stuck with doing a query each time.

K. Brian Kelley
@kbriankelley