?
Solved

VBScript Query LDAP with columns with a hypen in it's name

Posted on 2011-09-29
16
Medium Priority
?
1,490 Views
Last Modified: 2013-12-24
I am trying to query my ldap server and it works great until I try to reference a column with a hyphen in it's name. Is there a way to reference a column that has a hyphen in it's name?
Set oConnection = CreateObject("ADODB.Connection")
    Set oRecordSet = CreateObject("ADODB.Recordset")
    Set oComm = CreateObject("ADODB.Command")
   
    oConnection.Provider = "ADsDSOObject"
    oConnection.Open "ADs Provider"
    Set oComm.ActiveConnection = oConnection

    strQuery = "SELECT mail, ps-name FROM 'LDAP://ldap.mycompany.com/o=DC/OU=GA'  WHERE uid='myid' "
    oComm.CommandText = strQuery
    oComm.Properties("Page Size") = 50
    oComm.Properties("Timeout") = 30

    ' On Error Resume Next
    Set oRecordSet = oComm.Execute
    Do While Not oRecordSet.EOF
            sTmp=""
        For i = 0 To oRecordSet.Fields.Count-1
       
                  ReturnValue = oRecordSet.Fields(i)
                  If IsArray(ReturnValue) Then
                        For j = LBound(ReturnValue) To UBound(ReturnValue)      
                              If ReturnValue(j) <> "" Then
                                    sTmp=sTmp & "," & ReturnValue(j)
                              End If
                        Next
                  
                  Else
                  End If

        Next
            sTmp=Right(sTmp,Len(sTmp)-1)
        Wscript.echo sTmp
        oRecordSet.MoveNext
    Loop

Open in new window

0
Comment
Question by:abgtemp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 56

Expert Comment

by:Bill Prew
ID: 36815213
Do you get a VBS error, or just no data, or what?

~bp
0
 

Author Comment

by:abgtemp
ID: 36815409
It is a VBS error.  It fails with "Error: Unspecified Error"

0
 

Author Comment

by:abgtemp
ID: 36815443
The line that it is failing on is the line that executes the query.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 56

Expert Comment

by:Bill Prew
ID: 36815642
I was able to run your script adjusting for my environment, and referencing a field with a dash in it with no problem.  If I include the name of an invalid field in the query though, I get the error you get.  Are you sure all the fields in your query are correct, I suspect that may be the problem, especially if it works with out that one field name, but not with it.

~bp
0
 

Author Comment

by:abgtemp
ID: 36815728
I am using LDAP Browser to view the data and that is where I am seeing the column names. Maybe there are reference differently. Is there a way to list all of the column names via vbscript?
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 36815793
Easiest way is to grab a copy of the great freeware utility called ADFIND from the site below, and then just do a simple query like this to get a list of all attributes (use a name that will be found):

adfind -f (name=*Smith*)

http://www.joeware.net/freetools/tools/adfind/index.htm

~bp
0
 
LVL 56

Accepted Solution

by:
Bill Prew earned 1000 total points
ID: 36815806
That being said, LDAP browser should show the same field names...

So, this query works:

    strQuery = "SELECT mail FROM 'LDAP://ldap.mycompany.com/o=DC/OU=GA'  WHERE uid='myid' "

but this one doesn't?

    strQuery = "SELECT mail, ps-name FROM 'LDAP://ldap.mycompany.com/o=DC/OU=GA'  WHERE uid='myid' "


~bp
0
 

Author Comment

by:abgtemp
ID: 36816410
That's correct. If I drop off the ps-name name, the query doesn't fail. I have attached a screenshot of the ldap browser.
ldap.gif
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 36816510
Odd, not sure why that's happening.  I assume those ps- attributes are custom at your location?  Do they exist for all persons?

One other thing to try, do the 2 field query but use a baseline attribute instead like maybe:

    strQuery = "SELECT mail, cn FROM 'LDAP://ldap.mycompany.com/o=DC/OU=GA'  WHERE uid='myid' "

Just to see if that works or fails.  I'm trying to see if it's just any fields beyond 1, or the particular field you are adding (ps-name).

~bp
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36818516
Is ps-name the attribute name?  I'm not sure that would be the correct name....what's it for?

See if this works just to pull that attribute for your user account:
Set objADSysInfo = CreateObject("ADSysInfo")
Set objUser = GetObject("LPAP://" & objADSysInfo.UserName)
MsgBox objUser.Get("ps-name")

Open in new window


Regards,

Rob
0
 

Author Comment

by:abgtemp
ID: 36896974
I originally tried querying 4 fields and I received the same error. I reduced the number for this example.

Yes, ps-name is the correct name. I attached a screenshot that shows several fields that start with "ps". There are fields without them and fields with them. Also, just to be clear, this error is not specific to this particular field. I receive this error when trying to query any fields that have a hyphen in it's name.

One note, I'm able to query the fields with the hyphen in it's name within LDAP Browser successfully.

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36898113
OK, please try it this way. It's a different method of querying AD, and will be the method that the LDAP Browser uses.  It is also used by ADUC.

Just looking at the screenshot though.....uid....is that a Lotus Notes property?  Are you querying Lotus Notes?

I've never had much success using any external querying methods, besides the publicly available properties, and have had to use the LDAPSearch.exe utility that comes installed with the Lotus Notes client.

http://www-12.lotus.com/ldd/doc/domino_notes/Rnext/help6_admin.nsf/c1f2605b7be6a95985256b870069c0a8/f9cfdc5e6caabbef85256c1d00393d35?OpenDocument

It also uses the parameters, filter, attributes methods that I have shown here.

Regards,

Rob.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000

Set objRoot = GetObject("LDAP://RootDSE")
strDomain = objRoot.Get("defaultNamingContext")

objCommand.CommandText = "<LDAP://" & strDomain & ">;(&(uid=myid));mail,ps-name;Subtree"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields("mail").Value
    Wscript.Echo objRecordSet.Fields("ps-name").Value
    Wscript.Echo 
    objRecordSet.MoveNext
Loop

Open in new window

0
 

Author Comment

by:abgtemp
ID: 36925857
Yes, I am querying Lotus Domino. I tried the query above and still get the generic error "Line 13: Unspecified error". But don't get it when I remove the ps-name. I used the query with and without quotes around ps-name no difference.

As a workaround is there a way to just return all attributes of a user? If I can at least do that, I can then parse the data that I need.
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 36926595
Try:

strQuery = "SELECT * FROM 'LDAP://ldap.mycompany.com/o=DC/OU=GA'  WHERE uid='myid' "

Open in new window

~bp
0
 

Author Comment

by:abgtemp
ID: 36926679
That's one of the early queries I tried. But that does not quite work like a SQL query. That query will only return the ADsPath and it's value.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36927691
Try this.  It uses the LDAPSearch.exe tool that I mentioned.  It requires that you enter credentials to authenticate to the LDAP server, to be able to read more properties than just the basic public ones.

Change the values as required.

Regards,

Rob.
strLDAPSearch = "C:\Program Files\IBM\Lotus\LDAPSearch.exe"
strBase = "O=YourOrg,C=AU"
strHost = "192.168.0.1"
strPort = "390"
' Scope can be "subtree", "onelevel", or "base"
strScope = "onelevel"
' Limit the objects returned, 0 is unlimited
strLimit = "0"
' User to authenticate as
strUserDN = "CN=Your Name,O=YourOrg,C=AU"
If strUserDN <> "" Then strPassword = InputBox("Enter password:", "Password")
strFilter = "uid=myid"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strLDAPSearch = objFSO.GetFile(strLDAPSearch).ShortPath
strTempOutput = Left(strLDAPSearch, InStrRev(strLDAPSearch, "\")) & "TempSearchResults.txt"
If strUserDN <> "" Then
	strCommand = "cmd /c " & strLDAPSearch & " -b """ & strBase & """ -h " & strHost & " -p " & strPort & " -s " & strScope  & " -z " & strLimit & " -D """ & strUserDN & """ -w " & strPassword & " """ & strFilter & """ > " & strTempOutput
Else
	strCommand = "cmd /c " & strLDAPSearch & " -b """ & strBase & """ -h " & strHost & " -p " & strPort & " -s " & strScope  & " -z " & strLimit & " """ & strFilter & """ > " & strTempOutput
End If
objShell.Run strCommand, 0, True
objShell.Run "notepad " & strTempOutput, 1, True
objFSO.DeleteFile strTempOutput, True

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question