You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Advanced Scripting » RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Register User       Forum List       Calendar       FAQ
Page 1 of 1 1
Topic Options
#139569 - 2005-05-11 05:32 PM RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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
 
$=SetOption("WrapAtEOL","on")
$=SetOption("NoVarsInStrings","on")
$=SetOption("Explicit","on")
 
Dim $sWhat,$sFrom,$sFilter,$sScope,$aResults,$Result,$R,$
 
$sWhat = "ADsPath","badPwdCount"
 
$sFrom = "LDAP://"+GetObject("LDAP://rootDSE").Get("defaultNamingContext")
  
; Search for Disabled Users
$sFilter = "(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2))"
 
; Search for Users with badPwdCount greater than 1 (can be used to search for locked accounts)
$sFilter = "(&(objectCategory=user)(badPwdCount>=1))"
 
$sScope = "subtree"
 
$aResults = fnLDAPQuery($sWhat,$sFrom,$sFilter,$sScope)
@ERROR " : " @SERROR ?
 
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
    
    $oCon=CreateObject("ADODB.Connection")
    $oCon.Provider = "ADsDSOObject"
    ;$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")
     
    $oCMD = CreateObject("ADODB.Command")
    $oCMD.ActiveConnection = $oCon
    $oCMD.CommandText = "<"+$From+">;"+$Filter+";"+Iif(VarType($What)>8192,Join($What,','),$What)+";"+$Scope
    $oCMD.Properties("Page Size").Value=1000
    $oCMD.Properties("Timeout").Value=30
    $oCMD.Properties("Cache Results").Value=NOT 1
     
    $oRS = $oCMD.Execute
    If @ERROR Exit @ERROR EndIf
    If $oRS.BOF AND $oRS.EOF Exit @ERROR EndIf
     
    Do 
        $nul=Execute('$'+'vP='+$sGV)
        $aR[$R]=$vP
        $oRS.MoveNext
        $R=$R+1
        ReDim Preserve $aR[$R]
    Until $oRS.EOF
    ReDim Preserve $aR[$R-1]
    $fnLDAPQuery=$aR
EndFunction
Top
#139570 - 2005-05-11 05:41 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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))"
Top
#139571 - 2005-05-11 06:21 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
ChristopheM Offline
Hey THIS is FUN
*****

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:
;Function: 
; fnLDAPADQuery() 
; 
;Author: 
; Christophe Melin (christopher.melin@cus-strasbourg.net) 
; 
;Version: 
; 1.0 (May 11, 2005) 
; herited from fnADQuery from Christopher Shilt (christopher.shilt@relizon.com)
; 
;Version History: 
; 
;Action: 
; Uses ADODB to retrieve information from Active Directory using LDAP filter. 
; 
;Syntax: 
; fnLDAPADQuery( 
;    aAttributes, 
;    sADsPath, 
;    strFilter, 
;    Optional ORDER BY, 
;    Optional SCOPE 
;    Optional user 
;    optional password 
; ) 
; 
;Parameters: 
; aAttributes  : Required. Attribute (or array of attributes) to retrieve For 
;                each object. If you specify *, the query retrieves only the 
;                ADsPath of each object. 
; 
; sADsPath     : 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'. 
; 
; strFilter    : Required. 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 entries with a common name equal to "bob": 
;                    "cn=bob" 
;                Get entries with a common name beginning with "bob": 
;                    "cn=bob*" 
;                Get entries containing "bob" somewhere in the common name: 
;                    "cn=*bob*" 
;                Get entries with "bob" as the common name and "dull" as the surname: 
;                    "(&(cn=bob)(sn=dull))" 
; 
; ORDER BY     : Optional. An optional statement that generates a server-side
;                sort. Charaters string with fields name separated by comma. 
;                Active Directory supports the sort control, but it can impact server 
;                performance if the results set is large.
;                Be aware that Active Directory supports only a single sort key. 
;                Be aware that some fields are multivalued. Sort isn't allowed on these fields. 
;                The default is no sorting. 
;
;                Order by surname:
;                    "sn"
;
; SCOPE        : Optional. Specifies the scope of a directory search. 
; 
;                ADS_SCOPE_BASE = 0 
;                    Limits the search to the base object. The result contains, 
;                    at most, one object. 
;                ADS_SCOPE_ONELEVEL = 1 
;                    Searches one level of the immediate children, excluding 
;                    the base object. 
;                ADS_SCOPE_SUBTREE = 2 (DEFAULT) 
;                    Searches the whole subtree, including all the children and 
;                    the base object itself. 
; 
; user         : Optional. username to connect to the directory. 
; password     : Optional. password to connect to the directory. 
; 
;Remarks: 
; 
; 
;Returns: 
; 
; An array of attributes entered in the order specified in the aAttributes 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 = fnLDAPADQuery($aAttributes,$sADsPath,$strFilter,"name", 2) 
; @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=*))" 
; 
; $aResults = fnLDAPADQuery($aAttributes,$sADsPath,$strFilter,"", 2) 
; @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 fnLDAPADQuery($What, $sADsPath, $strFilter, Optional $OrderBy, Optional $Scope, Optional $user, Optional $Pwd)
    Dim $, $oCon, $oCommand, $oRS, $sF, $sGV, $strQuery, $sAttribsToReturn

    dim $adUseClient, $adCmdText, $adSecureAuthentication, $adOpenForwardOnly, $adLockReadOnly
    $adUseClient = 3
    $adCmdText = 1
    $adSecureAuthentication = 1
    $adLockReadOnly = 1
    $adOpenForwardOnly = 0

    If VarType($What) & 8192
        dim $sep
        $sep = ""
        $sGV = ""
        For Each $sF in $What
            $sGV = $sGV + $sep + '$' + 'oRS.Fields("' + $sF + '").Value'
            $sep = ","
        Next

        $sAttribsToReturn = Join($What,',')
    Else
        $sGV='$'+'oRS.Fields("'+$What+'").Value'

        $sAttribsToReturn = $What
    EndIf

    If $Scope <> "base" AND $Scope <> "onelevel" AND $Scope <> "subtree" $Scope = "subtree" EndIf

    ;-- Create ADO connection object for Active Directory -- 
    $oCon = CreateObject("ADODB.Connection")
    $oCon.Provider = "ADsDSOObject"
    $oCon.Properties("Encrypt Password").value = "true"
    $oCon.Properties("ADSI Flag").value = $adSecureAuthentication
    If $User AND $Pwd
        $oCon.Properties("User ID").Value = $User
        $oCon.Properties("Password").Value = $Pwd
    EndIf
    $oCon.Open( "Active Directory Provider" )
    If @ERROR Exit @ERROR EndIf

    ;-- Create ADO command object for the connection -- 
    $ocommand = CreateObject("ADODB.Command")
    $ocommand.ActiveConnection = $oCon

    ;-- Build the filter element of the commandtext -- 
    $strQuery = "<" + $sADsPath + ">;" + $strfilter + ";" + $sAttribsToReturn + ";" + $Scope
    $ocommand.CommandText = $strQuery
    If @ERROR Exit @ERROR EndIf

    ;-- set options -- 
    $oCommand.Properties( "Page Size" ).Value = 8192
    $oCommand.Properties( "Timeout"    ).Value = 60
    $oCommand.Properties( "Cache Results" ).Value = 1

    ;-- The server-side sort does not return any results if sort by DistinguishedName -- 
    ;   (see http://support.microsoft.com/kb/842637/en-us) 
    if $OrderBy="distinguishedName"
        ;-- Create ADO recordset -- 
        $oRS = CreateObject("ADODB.Recordset")

        ;-- Instead, the client-side sort succeeds -- 
        $oRS.CursorLocation = $adUseClient
        $oRS.Sort = $OrderBy

        ;-- Execute the query -- 
        $oRS.Open( $strQuery, $oCon, $adOpenForwardOnly, $adLockReadOnly, $adCmdText )
        If @ERROR Exit @ERROR EndIf
    else
        if $OrderBy
            $ocommand.Properties("Sort On").value = $OrderBy
        endif

        ;-- Execute the query -- 
        $oRS = $ocommand.Execute
        If @ERROR Exit @ERROR EndIf
    endif

    if $oRS.recordcount=0
        exit 2
    endif

    ;-- dim array because size is already known -- 
    dim $R, $vP
    redim $fnLDAPADQuery[$oRS.recordcount-1]

    $R = 0
    $oRS.MoveFirst
    do
        $=Execute('$vP='+$sGV)
        $fnLDAPADQuery[$R]=$vP
        $R=$R+1
        $=$oRS.MoveNext
    until $oRS.EOF
    exit 0
EndFunction

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.
_________________________
Christophe

Top
#139572 - 2005-05-11 06:29 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
ChristopheM Offline
Hey THIS is FUN
*****

Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
My email is wrong in the header of the function
(bad copy and past with christopher'email).

mine is christophe.melin@cus-strasbourg.net.

when christophe works on christopher functions !!!
_________________________
Christophe

Top
#139573 - 2005-05-12 11:04 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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
    
    $sQ = "<"+$From+">;"+$Filter+";"+Iif(VarType($What)>8192,Join($What,','),$What)+";"+$Scope
    
    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")
     
    $oCMD=CreateObject("ADODB.Command")
    $oCMD.ActiveConnection=$oCon
    $oCMD.CommandText=$sQ
    $oCMD.Properties("Page Size").Value=1000
    $oCMD.Properties("Timeout").Value=30
    $oCMD.Properties("Cache Results").Value=0
    
    If $OrderBy="distinguishedName"
        $oRS = CreateObject("ADODB.Recordset")
        $oRS.CursorLocation=3
        $oRS.Sort=$OrderBy
        $oRS.Open($sQ,$oCon,0,1,1)
    Else
        If $OrderBy
            $oCMD.Properties("Sort On").Value=$OrderBy
        EndIf
        $oRS = $oCMD.Execute
    EndIf
    If @ERROR Exit @ERROR EndIf
    If $oRS.BOF AND $oRS.EOF Exit @ERROR EndIf
    
    Do 
        $nul=Execute('$'+'vP='+$sGV)
        $aR[$R]=$vP
        $oRS.MoveNext
        $R=$R+1
        ReDim Preserve $aR[$R]
    Until $oRS.EOF
    ReDim Preserve $aR[$R-1]
    $fnLDAPQuery=$aR
EndFunction
Top
#139574 - 2005-05-17 02:38 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Christophe, shall we mark it as "done" and post it to the UDF forum?
Top
#139575 - 2005-05-18 05:45 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
ChristopheM Offline
Hey THIS is FUN
*****

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.
_________________________
Christophe

Top
#139576 - 2005-05-18 06:47 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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.
Top
#139577 - 2005-05-18 10:11 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Are there any other AD users willing to test?
Top
#139578 - 2005-05-19 03:43 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
maciep Offline
Korg Regular
*****

Registered: 2002-06-14
Posts: 947
Loc: Pittsburgh
Did some basic testing and it worked fine, regardless of the Cache Results value. AD specs: ~34k computers, ~64k users.
Top
#139579 - 2005-05-20 04:43 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
ChristopheM Offline
Hey THIS is FUN
*****

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.
_________________________
Christophe

Top
#139580 - 2005-05-20 06:10 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
ChristopheM Offline
Hey THIS is FUN
*****

Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
I confirm the issue with recordcount.

by default, recordset is opened with :
cursortype = adOpenForwardOnly (=0)

I am looking for a solution to open with an other parameter that allows to get recordcount before the loop.

try to do that during the week-end.
it's time to make backup and then go home...
_________________________
Christophe

Top
#139581 - 2005-05-20 06:34 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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.
Top
#139582 - 2005-05-21 12:57 AM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
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.

Top
#139583 - 2005-05-31 06:31 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
Dependencies: Add(), FlipCTime(), Replace(), fnLDAPQuery(), DSRM
Thanx for the help Chris, this worked great!

Code:

BREAK ON
$ = SetOption("WrapAtEOL")

$Date = "2005/3/1"
$Time = "00:00:00"


$sDate=""+FlipcTime($Date,$Time,-4)
$sDate=Add('11644473600',$sDate)+"0000000"


$sWhat = "ADsPath"


;$sFrom = "LDAP://"+GetObject("LDAP://rootDSE").Get("defaultNamingContext")
$sFrom = "LDAP://OU=test,DC=Microsoft,DC=Com"


; 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))"


$sScope = "subtree"


$aResults = fnLDAPQuery($sWhat,$sFrom,$sFilter,"Name",$sScope)


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

? UBound($aResults) ?

Get $
Top
#139584 - 2005-05-31 09:05 PM Re: RFC: fnLDAPQuery() - Uses ADODB to retrieve information from AD
Chris S. Offline
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().
Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 132 anonymous users online.
details
Newest Members
MaikSimon, kvn317, kixtarts2025, SERoyalty, mytar
17872 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.068 seconds in which 0.025 seconds were spent on a total of 13 queries. Zlib compression enabled.
Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org