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

    Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

      Hello!

      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');
      GroupMembers:

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

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

       

      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
      DO 
       GroupTable:
       LOAD 
        cn as FullGroupName,
        name as GroupName, 
        distinguishedName as GroupDN,
        sAMAccountName as GroupUPN
       ;
       SQL select 
        cn, 
        sAMAccountName, 
        name, 
        distinguishedName  // Fields to select 
       FROM 'LDAP://$(RootDse)'  
        WHERE 
          objectCategory='Group'
         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);
      LOOP
      
      
      

       

       

      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!

       

      Johan