read a text file and use its value to be the search critieria

Not sure if someone asked the same question in the past.
 
I have written a simple script to retrieve org users' attribute from LDAP.  The search criteria (WHERE clause) is based on "memberOf" attribute.  Since my users are allocated into more than 200 groups, I don't think it is a good practise to hardcode all group names into the vbs.   (I even tried but I found that the vbs aren't functional if I coded more than 78 groups insides.)
 
Could anyone tell me if it is possible to make the WHERE clause to read from text file to look for the group names? 
 
My code extract here:
 
objCommand.CommandText = _
    "SELECT cn, displayName, mail, company FROM 'LDAP://ldap.abc.com:389/ou=All Businesses,dc=sales,dc=adc,dc=com'" &_
 "WHERE objectClass='person' AND " &_
 "memberOf='CN=09758,OU=Groups,DC=SALES,DC=ABC,DC=com' OR"&_
"memberOf='CN=09759,OU=Groups,DC=SALES,DC=ABC,DC=com' "&_
 "ORDER BY cn"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
 
 
Regards,
Kelly.

Well, there is a couple of way to do what your asking.  As you are stipulating OR, you are just seeing if users are in one group or the other, I would probably run these as seperate queries so then I could show which group they were members of in my output.

With that said, I noticed the groups are numeric (09758, 09759, 09760....).  If your groups numerically sequential, you can do it easier by just doing simple increments:

'Connect code here
For i= 09758 to 09760
objCommand.CommandText = _
    "SELECT cn, displayName, mail, company FROM 'LDAP://MYDC.abc.com:389/ou=All Businesses,dc=sales,dc=adc,dc=com'" &_
 "WHERE objectClass='person' AND " &_
 "memberOf='CN=" & i  & ",OU=Groups,DC=SALES,DC=ABC,DC=com' "&_ 
 "ORDER BY cn"
' Execute code here
Next
'Close connections here

Reading from a file is the same principle:

Const FOR_READING = 1
Dim objFSO : Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile("C:\Groups.txt", FOR_READING, False)
'Connect code here
Do Until objFile.AtEndOfStream
    strLine = Trim(objFile.ReadLine)
    objCommand.CommandText = _ 
        "SELECT cn, displayName, mail, company FROM 'LDAP://MYDC.abc.com:389/ou=All Businesses,dc=sales,dc=adc,dc=com'" &_ 
     "WHERE objectClass='person' AND " &_ 
     "memberOf='CN=" & strLine  & ",OU=Groups,DC=SALES,DC=ABC,DC=com' "&_ 
     "ORDER BY cn"
    ' Execute code here
Loop

The main thing you want to understand in this scenario is that you only want to connect to Active Directory ONE TIME, not make a new connection for each request. So, your logic should be

Connect to AD
Do your loop and run all queries
Close connections (.Close)

Hi rasimmer,

I am yet to try your solution but I will try soon.

I have a question here.   Well, I have 152 groups in total needed to be the selection criteria..  I got error (Provider: A directory service error has occurred).   However, I tried to split them over and use 48 groups each time.  It works.  FYI, it doesn't work if i use MORE THAN 48 groups to search. (Same error as the above)

So, may I know whether VBS has limitation ?

Regards,
Kelly.

Kelly,

If I had to guess, the query is to complex (http://msdn.microsoft.com/en-us/library/aa392903%28VS.85%29.aspx):

"There are limits to the number of AND and OR keywords that can be used in WQL queries. Large numbers of WQL keywords used in a complex query can cause WMI to return the WBEM_E_QUOTA_VIOLATION error code as an HRESULT value. The limit of WQL keywords depends on how complex the query is."

This particular article is regarding WMI Query Language, but I think you are meeting a similar limitation with your query.  You basically have 47 OR statements in one query.  I would modularize your code and run a seperate query for each group.

Thanks raismmer.  I totally agree that I am in the similar situation that the article talks about.   (You know me, I am new to VBS so that I wrote this stupid script : (  )

Do you mean that I should separate it into 4 queries in order to get all output?

Regards,
Kelly.

Rasimmer,
I tried your method to read from file.   It returns me an error - Provider: Unspecified Error.    Do you have any ideas on that?
By the way, what is the best way to modularize the code?

Kelly.