Solved

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

Posted on 2011-09-29
16
1,429 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
  • 7
  • 6
  • 3
16 Comments
 
LVL 51

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
 
LVL 51

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 51

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 51

Accepted Solution

by:
Bill Prew earned 250 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

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 51

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now