#139569 - 2005-05-1105:32 PMRFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
This version uses the LDAP dialect to search Active Directory. I like this one better than fnADQuery() as the LDAP dialect has more powerful filtering.
Code:
;========================================================================== ; ; NAME: fnLDAPQuery ; ; AUTHOR: Christopher Shilt (christopher.shilt@relizon.com) ; DATE : 5/11/2005 ; ; COMMENT: Uses ADODB to retrieve information from Active Directory ; ;========================================================================== Break On
For Each $Result in $aResults If VarType($Result)>8192 For Each $R in $Result $R ? Next Else $Result ? EndIf Next
Get $
Function fnLDAPQuery($What,$From,Optional $Filter,Optional $Scope,Optional $User,Optional $Pswd) Dim $oCon,$oCMD,$oRS,$sF,$sGV,$R,$vP,$aR[0],$nul If $Scope <> "base" AND $Scope <> "onelevel" AND $Scope <> "subtree" $Scope = "subtree" EndIf If VarType($What)>8192 For Each $sF in $What $sGV=$sGV+'$'+'oRS.Fields("'+$sF+'").Value,' Next $sGV=Substr($sGV,1,Len($sGV)-1) Else $sGV='$'+'oRS.Fields("'+$What+'").Value' EndIf
#139570 - 2005-05-1105:41 PMRe: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
Here is some more information about specifying a filter:
The search filter specifies all conditions that must be met for a record to be included in the RecordSet. Each condition is in the form of a conditional statement, such as "(cn=TestUser)", which has a boolean result. Each such condition is enclosed in parenthesis. The general form of a condition is an attribute and a value separated by an operator, which is usually the equals sign "=". Other operators that can separate attributes and values are ">=", and "<=" (the operators "<" and ">" are not supported). Conditions can be combined using the following operators.
& - The "And" operator (the ampersand). All conditions operated by "&" must be met in order for a record to be included.
| - The "Or" operator (the pipe symbol). Any condition operated by "|" must be met for the record to be included.
! - The "Not" operator (the exclamation point). The condition must return False to be included.
Conditions can be nested using parenthesis. In addition, you can use the "*" wildcard character in the search filter.
Search filter examples:
To return all user objects with cn (Common Name) beginning with the string "Joe": "(&(objectCategory=person)(objectClass=user)(cn=Joe*))"
To return all computer objects with no entry for description: "(&(objectCategory=computer)(!description=*))"
To return all user and contact objects: "(objectCategory=person)"
To return all group objects with any entry for description: "(&(objCategory=group)(description=*))"
To return all groups with cn starting with "Test" or "Admin": "(&(objectCategory=group)(|(cn=Test*)(cn=Admin*)))"
To retrieve the object with GUID = "90395FB99AB51B4A9E9686C66CB18D99": "(objectGUID=\90\39\5F\B9\9A\B5\1B\4A\9E\96\86\C6\6C\B1\8D\99)"
To return all users with "Password Never Expires" set: "(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=65536))"
To return all users with disabled accounts: "(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2))"
To return all distribution groups: "(&(objectCategory=group)(!groupType:1.2.840.113556.1.4.803:2147483648))"
To return all users with "Allow access" checked on the "Dial-in" tab of the user properties dialog of Active Directory Users & Computers. This is all users allowed to dial-in. Note that "TRUE" is case sensitive: "(&(objectCategory=person)(objectClass=user)(msNPAllowDialin=TRUE))"
To return all user objects created after a specified date (09/01/2002): "(&(objectCategory=person)(objectClass=user)(whenCreated>=20020901000000.0Z))"
To return all users that must change their password the next time they logon: "(&(objectCategory=person)(objectClass=user)(pwdLastSet=0))"
Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
you are too fast for me. after your post, i have modified my version to include your parameters user and pswd
Code: I have added the sort possibility. The code is a little bit complex because there is a bug in AD 2000 with sort on "distinguishedname".
Before the final loop, i resize the array to the right size (known with $oRS.recordcount). And in the final loop, I write directly the results in the function array (by resizing with redim $fnLDAPADQuery). This prevents from using a temporary array and then copying from one array to an other. When there are many items, it's more efficient and reduce the use of memory.
#139573 - 2005-05-1211:04 PMRe: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
Ok, here is the latest. It includes all of Christophe's and my features. For some reason, I couldn't get Christophe's version to work for me so I just incorporated his features into mine.
Code:
; ;Function: ; fnLDAPQuery() ; ;Authors: ; Christopher Shilt (christopher.shilt@relizon.com) ; Christophe Melin (christophe.melin@cus-strasbourg.net) ; ;Version: ; 1.0 (May 12, 2005) ; ;Version History: ; ;Action: ; Uses ADODB to retrieve information from Active Directory. ; ;Syntax: ; fnLDAPQuery(WHAT, FROM, Optional FILTER, Optional ORDER BY, Optional SCOPE, ; Optional USER, Optional PASSWORD) ; ;Parameters: ; WHAT : Required. Attribute (or array of attributes) to retrieve For ; each object. If you specify *, the query retrieves only the ; ADsPath of each object. ; ; FROM : Required. Specifies the ADsPath of the base of the search. ; For example, the ADsPath of the Users container in an Active ; Directory domain might be 'LDAP://CN=Users,DC=Fabrikam,DC=COM'. ; ; FILTER : Optional. Specifies the query filter. You can also add wildcards ; and conditions to an LDAP search filter. The following examples ; show substrings that can be used to search the directory. ; ; Get all users: ; "(objectClass=Users)" ; Get all users with a common name equal to "bob": ; "(&(objectClass=Users)(cn=bob))" ; Get all users with a common name beginning with "bob": ; "(&(objectClass=Users)(cn=bob*))" ; Get all users containing "bob" somewhere in the common name: ; "(&(objectClass=Users)(cn=*bob*))" ; Get all users with "bob" as the common name and "dull" as the surname: ; "(&(objectClass=Users)(cn=bob)(sn=dull))" ; ; ORDER BY : Optional. An optional statement that generates a server-side ; sort. Active Directory supports the sort control, but it can ; impact server performance if the results set is large. Active ; Directory supports only a single sort key. You can use the ; optional ASC and DESC keywords to specify ascending or descending ; sort order; the default is ascending. ; ; Order by surname with a ascending sort order: ; "sn" ; Order by surname with a descending sort order: ; "sn DESC" ; ; SCOPE : Optional. Specifies the scope of a directory search. ; ; BASE ; Limits the search to the base object. The result contains, ; at most, one object. ; ONELEVEL ; Searches one level of the immediate children, excluding ; the base object. ; SUBTREE (DEFAULT) ; Searches the whole subtree, including all the children and ; the base object itself. ; ; USER : Optional. Specifies the user to connect to the directory. ; ; PASSWORD : Optional. Specifies the password to connect to the directory. ; ;Remarks: ; ; ;Returns: ; ; An array of attributes entered in the order specified in the WHAT parameter. If ; only one attribute is specified each element in the array will contain the result ; of the query. If an array of attributes is specified in the WHAT parameter Each ; element in the array will contain an array of the results of the query. ; ; Sets the value of @ERROR based on success/failure. ; ;Dependencies: ; ; ;Example: ; ; ; == Return the Name and AdsPath of all users =================== ; $aAttributes = "Name", "AdsPath" ; $sADsPath = "LDAP://"+GetObject("LDAP://rootDSE").Get("defaultNamingContext") ; $strFilter = "(&(objectClass=User)(Name=*))" ; ; $aResults = fnLDAPQuery($aAttributes,$sADsPath,$strFilter,"Name") ; @ERROR " | " @SERROR ? ; ; For Each $Result in $aResults ; If VarType($Result)>8192 ; For Each $R in $Result ; $R ? ; Next ; Else ; $Result ? ; EndIf ; Next ; ; ; == Return the Name, AdsPath and members of all groups ========= ; $aAttributes = "Name", "AdsPath", "member" ; $sADsPath = "LDAP://"+GetObject("LDAP://rootDSE").Get("defaultNamingContext") ; $strFilter = "(&(objectClass=group)(Name=relizon_t*))" ; ; $aResults = fnLDAPQuery($aAttributes,$sADsPath,$strFilter) ; @ERROR " | " @SERROR ? ; ; For Each $Result in $aResults ; "=============================" ? ; If VarType($Result)>8192 ; For Each $R in $Result ; If VarType($R)>8192 ; for each $rr in $r ; " " $rr ? ; next ; else ; $R ? ; endif ; Next ; Else ; $Result ? ; EndIf ; Next ; ; Function fnLDAPQuery($What,$From,Optional $Filter,Optional $OrderBy,Optional $Scope,Optional $User,Optional $Pswd) Dim $oCon,$oCMD,$oRS,$sQ,$sF,$sGV,$R,$vP,$aR[0],$nul
If $Scope <> "base" AND $Scope <> "onelevel" AND $Scope <> "subtree" $Scope = "subtree" EndIf
If VarType($What)>8192 For Each $sF in $What $sGV=$sGV+'$'+'oRS.Fields("'+$sF+'").Value,' Next $sGV=Substr($sGV,1,Len($sGV)-1) Else $sGV='$'+'oRS.Fields("'+$What+'").Value' EndIf
$oCon=CreateObject("ADODB.Connection") $oCon.Provider = "ADsDSOObject" $oCon.Properties("Encrypt Password").Value=1 $oCon.Properties("ADSI Flag").Value=1 If $User AND $Pswd $oCon.Properties("User ID").Value=$User $oCon.Properties("Password").Value=$Pswd EndIf $oCon.Open("Active Directory Provider")
Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
Could you try my version with the line $=Execute('$$vP='+$sGV) in the final loop.
with the line $=Execute('$$vP='+$sGV) i also had an unexpected problem in line 1 !!! it's an issue with setoption( "NoVarsInStrings", xxx )
with your version, if i set $oCommand.Properties( "Cache Results" ).Value to 0, i have no result. if i set the value to 1, i have some results !!!
any idea ?
I am testing on a "big" AD (more than 4000 users, 3000 computers and 2000 groups). I will also test version with redim of temporary array and version without temporary array.
#139576 - 2005-05-1806:47 PMRe: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
For NoVarsInStrings compliance I'd go with:
Code:
$=Execute('$'+'vP='+$sGV)
I work in a similar sized AD (5000+ users, 2300+ computers, 1800+ groups) and have had no problems returning data. I've even been able to query a remote domain with no problems.
Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
OK for NoVarsInStrings compliance.
Chris, for "Cache result", what OS are you working on ? I am working on NT WorkStation 4 and DSClient For NT is not as powerful as W2K, WXP or W2K3.
i continue to investigate about "cache results" but it could a side-effect of recordcount property !!! with cache result = 1, the loop is OK with cache result = 0, the loop exits after the first element event if recourdcount is greater than 1
I have made some test with the two versions : for time execution, there is no difference but for memory, my version uses half memory. For small sets of data, this is not important but for large set, the difference becomes real.
i know memory isn't expansive now but when I can optimize resource use, i do it.
#139581 - 2005-05-2006:34 PMRe: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
Yes, accessing the RecordCount property seems to move the cursor and even using MoveFirst won't reset it. That is why I dim/redim my array on the fly, probably not the best practice but it works.
Yes, that is correct. IIRC, using Recordcount will move the cursor to the end and cannot move back again. Basically, you run into a tradoff of speed versus convenience. I think it also happens when using the asynchronous parameter (48) in WQL queries as I had problems with Recordcount in ReadEventlog(). See for example http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0501.asp
You can alwasy use the .GetRows() function to retrieve the complete recordset into an array. See e.g. DBCommand() for code samples.
_________________________
There are two types of vessels, submarines and targets.
; Search for users who are not disabled and w/o the "NoExpirey" flag set and have not changed their pwd by a certain date. $sFilter = "(&(objectClass=computer)(pwdLastSet<="+$sDate+")"+ "(!userAccountControl:1.2.840.113556.1.4.803:=2)"+ "(!userAccountControl:1.2.840.113556.1.4.803:=65536))"
For Each $Result in $aResults If VarType($Result)>8192 For Each $R in $Result $R ? Next Else $Result = Replace($Result,"LDAP://","") SHELL '%COMSPEC% /e:1024 /c dsrm -noprompt "$Result"' EndIf Next
#139584 - 2005-05-3109:05 PMRe: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S.Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
I'm glad that helped, pip3r. As a suggestion, I would use the ADsPath and perform a GetObject() on it. Then I would disable it, rename it, and move it to another OU to delete at a later date. I wouldn't want to be the admin to accidentally delete an account that is still needed.
If you don't feel like doing that, I'd change the property returned from ADsPath to distinguishedName so you don't have to use Replace().