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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

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
RobSampsonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.