vbscript ldap : How do I ldap query with an email address using vbscript?

I'm trying to write a vbscript which executes and pulls the Office field for a user in active directory.  I only have their smtp email address.  Is there a way to do this?  I can do it by cn however I haven't been able to query by email?

If necessary, would I have to go through MS Exchange to do this and then use the displayname to query active directory?  

I know i can do it this way: 
Set MyUser = GetObject ("LDAP://cn=" & uname & ",ou=" & strname & ",DC=bobdom,DC=net")
 
I tried it this way but it failed: 
Set MyUser = GetObject ("LDAP://mail=" & uname & ",ou=" & strname & ",DC=bobdom,DC=net")

Open in new window

philltxAsked:
Who is Participating?
 
sirbountyConnect With a Mentor Commented:
Try this:
strEmail = "someone@somewhere.com"
 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim objDomain : Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
Set cmd.ActiveConnection = cn
 
cmd.CommandText = "SELECT ADsPath FROM '" & objDomain.ADsPath & "' WHERE email='" & strEmail & "'"
Dim objRS : Set objRS = cmd.Execute
Do While Not objRS.EOF
  wscript.echo objRS.Fields(0)
Loop
 
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Open in new window

0
 
philltxAuthor Commented:
thanks for the snippet.

When I changed the strEmail to an email address which is listed in active directory, I didn't receive any output or errors.  Would having different ou's in AD affect the way the code queries AD?

thanks
0
 
sirbountyCommented:
Oops - yes, let's set it to query all paths...



Const ADS_SCOPE_SUBTREE = 2
 
strEmail = "someone@somewhere.com"
 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim objDomain : Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
Set cmd.ActiveConnection = cn
 
cmd.CommandText = "SELECT ADsPath FROM '" & objDomain.ADsPath & "' WHERE email='" & strEmail & "'"
cmd.Properties("Page Size") = 1000
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRS : Set objRS = cmd.Execute
Do While Not objRS.EOF
  wscript.echo objRS.Fields(0)
Loop
 
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Open in new window

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
philltxAuthor Commented:
thanks for the quick response,

I'm still getting the same result.. no errors or anykind of output.
Is there a way to diagnose why there is no output?

0
 
sirbountyCommented:
bah - my mistake...change
 WHERE email=
to
 WHERE mail=

(just remove the 'e')
0
 
philltxAuthor Commented:
i removed the "e" and still the same result... would assigning it a domain help?

thanks
0
 
sirbountyCommented:
assigning it a domain?
Should be in the format of user@domain.com, no?
0
 
RobSampsonConnect With a Mentor Commented:
Hi, try this.

I've changed the objDomain to just strDNSDomain....no need to bind to it first.....

I've also added objRS.MoveNext.....was the script ever ending?

I've also added a record counter so you'll know if it returned anything....

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
strEmail = "someone@somewhere.com"
 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim strDNSDomain : strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
Set cmd.ActiveConnection = cn
 
cmd.CommandText = "SELECT ADsPath FROM '" & strDNSDomain & "' WHERE mail='" & strEmail & "'"
cmd.Properties("Page Size") = 1000
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRS : Set objRS = cmd.Execute
intRecords = 0
Do While Not objRS.EOF
  wscript.echo objRS.Fields(0)
  intRecords = intRecords + 1
  objRS.MoveNext
Loop
 
WScript.Echo intRecords & " records were returned."
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Open in new window

0
 
philltxAuthor Commented:
Hey it works! however it seems to loop indefinetly.  How do i get this to stop?
0
 
philltxAuthor Commented:


Also, I am having trouble setting the function name getOffice = objRS.Fields(0).  I get a type mismatch error.  Any ideas on this?

0
 
RobSampsonCommented:
Hi, not sure if you're still needing this, but try changing this:

Do While Not objRS.EOF
  wscript.echo objRS.Fields(0)
  intRecords = intRecords + 1
  objRS.MoveNext
Loop

to this
While Not objRS.EOF
  wscript.echo objRS.Fields(0)
  intRecords = intRecords + 1
  objRS.MoveNext
Wend


and also, is getOffice a function you've defined?  Perhaps you need to typecast the return value:
This would return a string:
getOffice = CStr(objRS.Fields(0).Value)
This would return an integer:
getOffice = CInt(objRS.Fields(0).Value)

Regards,

Rob.
0
 
Abdul RehmanCommented:
Const ADS_SCOPE_SUBTREE = 2
 
strEmail = "Fname.Lname@yourcompany.com"
 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim strDNSDomain : strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Dim cn : Set cn = CreateObject("ADODB.Connection")
Dim cmd : Set cmd = CreateObject("ADODB.Command")
cn.Provider = "ADsDSOObject"
cn.Open "Active Directory Provider"
WScript.Echo strEmail

Set cmd.ActiveConnection = cn
 
cmd.CommandText = "SELECT ADsPath FROM '" & strDNSDomain & "' WHERE mail='" & strEmail & "'"
cmd.Properties("Page Size") = 1000
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRS : Set objRS = cmd.Execute
intRecords = 0
While Not objRS.EOF
  wscript.echo objRS.Fields(0)
  intRecords = intRecords + 1
  objRS.MoveNext
Wend
 
WScript.Echo intRecords & " records were returned."
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.