Link to Active Directory and Select Fields 1

[jmeckley]

I have created a view that links to Active Directory. So far I can query name, mail, title, manager, adspath. However I don't know where, or how, to get a list of all the fields I can select.

My Guess and Test method isn't effeceint at all. It took me 2 days to figure out that the MAIL field contains email addresses. I am ultimately looking for a way to view the groups a user belongs to.

Thanks for your help

Jason Meckley
Database Analyst
WITF

 

[SQLSister]

It's probably not the most efficient way but
select * from viewname
should show all the fields and the data in them so you know what is what.

 

[Bygbobbo]

The MSDN has all the names and references.

Try this link.

http://msdn.microsoft.com/library/d...ry/en-us/adschema/ad/win2k_c_groupofnames.asp

I hope this helps, it helped me when querying the active directory.


Bygs

 

[jmeckley]

I checked out the link in the post above. I am having difficulty understanding how to use it.

Here is my knowledge on the subject so far:
[tt]SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, '<LDAP://W2KRoot>;(&(objectCategory=Person)(objectClass=user));name, adspath,;subtree') Rowset_1
ORDER BY name[/tt]
Selects all the entities who are have a class of user and category of person

[tt]SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, '<LDAP://W2KRoot>;(&objectClass=group);name, adspath,;subtree') Rowset_1
ORDER BY name[/tt]
Selects all the groups in active directory.

[tt]SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, '<LDAP://W2KRoot>;;name, adspath,;subtree') Rowset_1
ORDER BY name[/tt]
Selects everything in active directory.

Bygbobbo, have you queried AD to select groups (or any field that could have multiple values)? From what I have read it is not possible, but I don't buy that.

Thank you for assistance

Jason Meckley
Database Analyst
WITF

 

[jmeckley]

Here is what I have found this morning. I can query other fields within AD, but I have to guess and test to see if it will work.

View
[tt]SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, '<LDAP://W2KRoot>;(&(objectCategory=Person)(objectClass=user));name, displayname, mail, member, adspath,;subtree') Rowset_1
ORDER BY name
[/tt]
Stored Procedure
[tt]CREATE PROCEDURE sp_ActiveDirectory_s AS
Select displayname,
mail
From ADUser_v
Where mail is not null
And mail not like '%corp.witf.org'
And mail not like 'systemmailbox%'
Order by Displayname
[/tt]
This is the best I can do to get users email addresses since I have not found a way to select what groups a member is a part of. Hope this helps some of you.

Jason Meckley
Database Analyst
WITF

 

[gtog]

I am struggling with the same problem. I have found the following information, which might be useful:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/ad/win2k_c_person.asp

It lists all &quot;fields&quot; for the Person category, class user.

For what it's worth, I also add the following code:

Sub QueryAD()
Set oConnection1 = CreateObject(&quot;ADODB.Connection&quot
Set oCommand1 = CreateObject(&quot;ADODB.Command&quot
' Open the connection.
oConnection1.Provider = &quot;ADsDSOObject&quot; ' This is the ADSI OLE-DB provider name
oConnection1.Open &quot;Active Directory Provider&quot;
' Create a command object for this connection.
Set oCommand1.ActiveConnection = oConnection1
' Compose a search string.
oCommand1.CommandText = &quot;select name, displayname, userprincipalname, mailNickname, telephoneNumber from 'LDAP://enteryourservernamehere' WHERE objectCategory='Person' AND objectClass='user'&quot; 'AND name='Don Pedro'&quot;
' Execute the query.
Set rs = oCommand1.Execute
'--------------------------------------
' Navigate the record set
'--------------------------------------
Do While Not rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Name & &quot; - &quot; & fld.Value
Next fld
Debug.Print
rs.MoveNext
Loop
End Sub