Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
nucleus
Partner
Partner

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

1 Solution

Accepted Solutions
nucleus
Partner
Partner
Author

Hello.

I´ve had some progress to this issue and managed to solve it.

By using command line net group  and landing the output in an text-file and then reading the text-file I managed to circumvent the pagesize.

See my enclosed file for further details.

View solution in original post

5 Replies
rajeshvaswani77
Specialist III
Specialist III

Hi Johan,

Were you able to solve this? I am in to a similar issue.

thanks,

Rajesh Vaswani

nucleus
Partner
Partner
Author

Hi

I haven´t put any effort in to solving this issue since I posted my request for help.

Johan

Från: Rajesh Vaswani

Skickat: den 24 september 2013 17:46

Till: Idberg, Johan

Ämne: Re: - Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

QlikCommunity<http://community.qlik.com/>

Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

reply from Rajesh Vaswani<http://community.qlik.com/people/rajeshvaswani77?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/396478?et=watches.email.thread#396478>

rajeshvaswani77
Specialist III
Specialist III

thanks for reply Johan, in case i find something will let you know.

regards,

Rajesh Vaswani

nucleus
Partner
Partner
Author

I´ll post my findings or resolution on qlikcommunity.

Regards

Johan

Från: Rajesh Vaswani

Skickat: den 25 september 2013 07:08

Till: Idberg, Johan

Ämne: Re: - Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

QlikCommunity<http://community.qlik.com/>

Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

reply from Rajesh Vaswani<http://community.qlik.com/people/rajeshvaswani77?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/396703?et=watches.email.thread#396703>

nucleus
Partner
Partner
Author

Hello.

I´ve had some progress to this issue and managed to solve it.

By using command line net group  and landing the output in an text-file and then reading the text-file I managed to circumvent the pagesize.

See my enclosed file for further details.

View solution in original post