5 Replies Latest reply: Sep 22, 2014 12:00 PM by Johan Idberg RSS

    Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

    Johan Idberg


      I´ve been reading and testing several solutions for retrieving user/group data form AD to qlikivew. My problem is that I´m not getting the full set of groupmembers for a group that contains more than 1000 members. I´ve tried rewriting the script with a loop but it wont work.

      Current script for retrieving groupmembers:


      LET arg= NoOfRows('GroupTable');

      For X = 0 to arg
      LET Group = peek('GroupDN', $(X), 'GroupTable');

      load distinguishedName as UserDN, '$(Group)' as GroupDN;

      SELECT distinguishedName FROM 'LDAP://$(RootDse)' WHERE MemberOf='$(Group)';


      My initial thought was to use the same method used to retrive users and groups. See example script below:


      Read names from Active Directory using AdsDSO.
      Active Directory returns a maximum number (pagesize) of rows per call. 
      The loop below will get all the rows regardless of the pagesize. 
      LET arg=chr(01); // Set search start name to a very low value
        cn as FullGroupName,
        name as GroupName, 
        distinguishedName as GroupDN,
        sAMAccountName as GroupUPN
       SQL select 
        distinguishedName  // Fields to select 
       FROM 'LDAP://$(RootDse)'  
         AND sAMAccountName>'$(arg)' // Get rows where "name" is GT the arg
         order by sAMAccountName;
       EXIT DO WHEN ScriptError > 1 // Stop loop if SELECT has error
       EXIT DO WHEN NoOfRows('nameTable') = 0;  // Stop loop if SELECT returns nothing
       EXIT DO WHEN peek('GroupUPN') = '$(arg)';  // If the last "name" read is EQ to arg -- no more entries
       LET arg=peek('GroupName'); // Set the arg to the last "name" read
       trace $(arg);



      But when I try to order the result from the GroupMembers query, it fails.

      Could it be that a query returning a single column/field can´t include a order by clause?


      Hoping for assistance with the script.


      Thanks in advance!