Hi
Yeah, I found that limiting factor for extracting data out of LDAP.
But in the mean time I tried several approaches. Here's a quick & dirty one: With only 1000 rows to be extracted in one go there's a mighty chance that you miss out some users. Thus, if you miss any users, you will need to duplicate/triplicate the whole Loop as many times until all of the LDAP data has been loaded. The variable arg needs to reach the last name of the users.
You can add another Do LOOP for the duplication..
SET RootDse = 'LDAP://host.name:port';
CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Data Source=LDAP://host.name:port;Mode=Read];
LET arg=chr(01);
DO
ADUsers:
FIRST 1000
LOAD DISTINCT
cn,
sAMAccountName as Login,
name as UserName
;
SQL select
cn,
sAMAccountName,
name
FROM '$(RootDse)'
WHERE
objectCategory='person' AND
objectClass = 'User' AND
name>'$(arg)'
order by name; // Get rows where "name" is GT the arg
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('UserName') = '$(arg)'; // If the last "name" read is EQ to arg -- no more entries
LET arg=peek('UserName'); // Set the arg to the last "name" read
LOOP
Yes, duplicating/triplicating - It's not nice coding, but it works 🙂
Otherwise, as mentioned, when not setting the Limit of FIRST 1000 the script throws an error QVX_UNEXPECTED_END_OF_DATA.
Kind regards,
Chris
