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

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

abgtempAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
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
 
Bill PrewCommented:
Do you get a VBS error, or just no data, or what?

~bp
0
 
abgtempAuthor Commented:
It is a VBS error.  It fails with "Error: Unspecified Error"

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
abgtempAuthor Commented:
The line that it is failing on is the line that executes the query.
0
 
Bill PrewCommented:
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
 
abgtempAuthor Commented:
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
 
Bill PrewCommented:
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
 
abgtempAuthor Commented:
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
 
Bill PrewCommented:
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
 
RobSampsonCommented:
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
 
abgtempAuthor Commented:
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
 
RobSampsonCommented:
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
 
abgtempAuthor Commented:
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
 
Bill PrewCommented:
Try:

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

Open in new window

~bp
0
 
abgtempAuthor Commented:
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
 
RobSampsonCommented:
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
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.