How to Exporting more than one field of users info from AD LDAP

[tbscmgi]

I'm not sure how to access LDAP to export more than one field of data from LDAP.
I need to get the user name, email address, user location.

This is the code I'm using
On Error Resume Next

Dim qQuery, objConnection, objCommand, objRecordSet, obj
Dim oRootDSE, strDomain
Dim File1
Dim RF
set fso = CreateObject("Scripting.fileSystemObject")
set File1 = fspenTextFile("J:\emailaddress.txt",2,True)

Set oRootDSE = GetObject("LDAP://rootDSE")
strDomain = oRootDSE.get("defaultNamingContext")

qQuery = "<LDAP://" & strDomain &">;" & _
"(objectCategory=person)" & _
";adspath;subtree"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection
objCommand.CommandText = qQuery
Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF
RF = objRecordSet.Fields("adspath")
File1.WriteLine RF

objrecordset.MoveNext
Wend
close.File1
msgbox "done"
objConnection.Close

Thanks for any help in advance.
Tony

 

[PHV]

A starting point:

...
qQuery = "<LDAP://" & strDomain &">;" & _
         "(objectCategory=person)" & _
         ";adspath,name,distinguishedName;subtree" 
...
While Not objRecordSet.EOF
    RF = objRecordSet.Fields("adspath") & vbTab _
      & objRecordSet.Fields("name") & vbTab _
      & objRecordSet.Fields("distinguishedName")
    File1.WriteLine RF
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PScottC]

qQuery = "<LDAP://" & strDomain &">;" & _
"(objectCategory=person)" & _
";adspath;subtree"

Your query has 4 distict sections:

sQ = Search Base ; Filter ; Attributes ; Search Scope

See the following link for a full explanation: [URL unfurl="true"]http://www.microsoft.com/technet/scriptcenter/guide/sas_usr_ykxh.mspx?mfr=true[/url]

[ul][li]Search Root is where the query starts from.[/li]
[li]Filter is an LDAP filter you can use to limit the results returned.[/li]
[li]Attributes is a comma separated list of attributes that will be returned.[/li]
[li]Search Scope limits the search to Base, OneLevel, or SubTree.[/li][/ul]

In your query you are only pulling the adspath attribute. To get other attributes, simply list them in comma separated format, hence:

sQ = "<LDAP://DC=MyDomain,DC=tld>;(objectCategory=person);adspath,proxyAddresses,physicalDeliveryOfficeName;subtree"

Will return the aDSPath, proxyAddresses, and physicalDeliveryOfficeName attributes.

The last attribute seems dumb, but that is the one that shows as "Office" in the General tab of a user's account in ADUC.

Now... we will hit one more snag before the day is done... proxyAddresses (the attribute that holds the user's email address) is stored as a multi-size string, and must be treated as an array when parsing the data. What distinguishes the primary email address from all others is that it is prefixed with "SMTP:" (all caps, others will be lower case).

To deal with that you code like this:

While Not objRecordSet.EOF
	For Each item In objRecordSet.Fields("proxyAddresses").Value
		If Left(item, 5) = "SMTP:" Then
			sSMTP = Replace(item,"SMTP:","")
		End If
	Next
    RF = objRecordSet.Fields("adspath").Value & "," & sSMTP & "," & objRecordSet.Fields("physicalDeliveryOfficeName").Value
    File1.WriteLine RF
    
    objrecordset.MoveNext
Wend

Hope this helps.

PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers

 

[tbscmgi]

Sorry guys no matter what I try it did not work. For some reason I can get the adspath, mail, name, displayname all at the same time. Below is my new vbscript. HELP !!!!
'
option Explicit
dim octr, sctr, fctr, fso, oShell, objEmail, objIPAWMIService, colIPAItems, objIPAItem
dim svripaddr, computername, c, d, e, f, fc, f1, stripaddress, ofile, spath
dim sddssize, sddsavai, sdddrive, sddper, tmpsvripaddr
dim objConnection, objCommand, rootdn, objRecordSet
Const ADS_SCOPE_SUBTREE = 2
'
'
Sub ShowConnectAD

Dim rootDSE

set rootDSE = Getobject("LDAP://rootDSE")
rootdn = rootDSE.Get("defaultNamingContext")
set objConnection = CreateObject("ADODB.Connection")
set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open = "Active Directory Provider"

End Sub
'
'
Sub ShowQueryAD

set objCommand.ActiveConnection = objConnection
ObjCommand.CommandText = _
"Select name, adspath, user, mail, displayname " _
& "from 'LDAP://" & rootdn & "' " _
& "where objectCategory='person'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute

End Sub
'
'
Sub ShowProcessServers()

Dim strComputer

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
msgbox objRecordSet.Fields("Name").Value
msgbox objRecordSet.Fields("adspath").Value
msgbox objRecordSet.Fields("user").Value
msgbox objRecordSet.Fields("mail").Value
msgbox objRecordSet.Fields("displayname").Value

' OFile.Writeline octr & " Folder's in " & strComputer & " " & spath

objRecordSet.MoveNext
Loop

End Sub
'
'
Sub ShowOpenOutputFile

Set OFile = fso.CreateTextFile("j:\sysinfo\AllEmail.txt", 2, True)

End Sub
'
'
set fso = CreateObject("Scripting.fileSystemObject")
set oShell = CreateObject("WScript.Shell")
'
'ShowOpenOutputFile
'
call ShowConnectAD
'
call ShowQueryAD
'
call ShowProcessServers
'
'OFile.close
msgbox "AD LDAP Scan is complete, Hit OK to finish.

 

[PHV]

it did not work
Any chance you could post some relevant info like, say, an error message ?

 

[tbscmgi]

PHV-
The error is on this line
Set objRecordSet = objCommand.Execute
error : Unspecified error
code: 80004005
source : Provider

 

[PScottC]

You're trying to query non-existant attribute, "user".

Use ADSI edit to determine which attributes to use in your query.

Also, it would be helpful to us if you continued with the same code as you used at the beginning of this thread. Your second set of code uses a SQL style query instead of an LDAP style query. It's a little hard to follow your code progression.

PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers