Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
First 1000 is needed to avoid the error "QVX_UNEXPECTED_END_OF_DATA: ", It's a workaround for undefined SQL error.
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;
I am trying to do this in QlikSense.
Do you know how I would "Connect to" 'LDAP://ad.xxx.com' ? thanks