Reading GroupMembers from active directory with QlikView

[qw_johan]

Hi

I am having problems reading all group members from an active director.

With code from QV Cookbook ( http://robwunderlich.com/downloads/‌ ) I was

able to read ADUsers, ADGroups and ADGroupMembers. But because of the limitation in MaxPageSize (1000) on our LDAP servers I'm not getting all groupmembers. If a group has more than 1000 members, I still only get 1000.

I have seen posts about this limitation but not a working solution. There must be a way to do it in QlikView.
And they won’t change MaxPageSize on our servers I already checked. 🙂

This is how I retrieve group members.

LET arg = NoOfRows('ADGroups');
For i = 0 to NoOfRows('ADGroups') - 1
LET Group = peek('GroupDN', $(i), 'ADGroups');

ADGroupMembers:
FIRST 1000
     LOAD DISTINCT
distinguishedName as UserDN,
'$(Group)' as GroupDN
;
SELECT distinguishedName
FROM 'LDAP://ad.xxx.com'  WHERE MemberOf='$(Group)'
     ;

LET arg=peek('GroupDN'); 

NEXT i;

Any help is appreciated.

Johan

[Nicole-Smith]

I don't know about the MaxPageSize stuff, but you do have a FIRST 1000 on your load which will only bring in the first 1000 rows.  Have you tried removing that line?

ADGroupMembers:
FIRST 1000
     LOAD DISTINCT
distinguishedName as UserDN,
'$(Group)' as GroupDN
;

[qw_johan]

First 1000 is needed to avoid the error "QVX_UNEXPECTED_END_OF_DATA: ", It's a workaround for undefined SQL error.

[qw_johan]

The solution that helped me was to loop twice, creating a do...loop inside the for...next loop.

My final script looks somehing like this:

LET arg = NoOfRows('ADGroups');

For i = 0 to NoOfRows('ADGroups') - 1

LET Group = peek('GroupDN', $(i), 'ADGroups');

LET varg=chr(01); // Set search start name to a very low value

DO

ADGroupMembers:

FIRST 1000 // Workaround for undefined SQL error.

LOAD DISTINCT

distinguishedName as UserDN,

'$(Group)' as GroupDN,

name as MyUser

;

SELECT distinguishedName, name

FROM 'LDAP://ad.xxx.com'  WHERE MemberOf='$(Group)'

AND name>'$(varg)' ORDER BY name;

;

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('MyUser') = '$(varg)';  // If the last "name" read is EQ to arg -- no more entries

LET varg=peek('MyUser'); // Set the arg to the last "name" read

LOOP

LET arg=peek('GroupDN'); // Set the arg to the last "name" read

NEXT i;

[AmitKakkad]

I am trying to do this in QlikSense.

Do you know how I would "Connect to" 'LDAP://ad.xxx.com' ? thanks