topic Re: Reading GroupMembers from active directory with QlikView in QlikView

Reading GroupMembers from active directory with QlikView

qw_johan — Wed, 17 Jan 2018 16:26:36 GMT

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

Re: Reading GroupMembers from active directory with QlikView

Nicole-Smith — Wed, 17 Jan 2018 17:16:15 GMT

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
;

Re: Reading GroupMembers from active directory with QlikView

qw_johan — Wed, 17 Jan 2018 17:44:12 GMT

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

Re: Reading GroupMembers from active directory with QlikView

qw_johan — Fri, 19 Jan 2018 14:53:23 GMT

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

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;

Re: Reading GroupMembers from active directory with QlikView

AmitKakkad — Thu, 19 Aug 2021 14:21:07 GMT

I am trying to do this in QlikSense.

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