Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

nucleus
Not applicable

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
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

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.

5 Replies
rajeshvaswani77
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

Hi Johan,

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

thanks,

Rajesh Vaswani

nucleus
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

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
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

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

regards,

Rajesh Vaswani

nucleus
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

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
Not applicable

Re: Retriving more than 1000 groupmembers from LDAP via ADsDSOObject

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.