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
