Problem with export Active Directory

qlikconsultant — Mon, 17 Jun 2024 14:07:12 GMT

I have the following code:

LET arg=chr(01); // Set search start name to a very low value Do ADUsers: First 1000 // Workaround for undefined SQL error. Don't forget to sort the entries, so the loop continues Load Distinct // Add addtional Fields As required name As UserName, sn As Nachname, givenName As Vorname, mail As Email, telephonenumber As Festnetz, mobile As Mobilnummer, manager, company As Firma, department As Abteilung, title As Position, lastLogonTimestamp As Letzter_Login, lastLogon, userAccountControl As Status, otherPager, physicalDeliveryOfficeName as Office ; SQL Select //Fields to select //Add addtional Fields As required name, sn, givenName, distinguishedName, mail, telephonenumber, mobile, manager, company, department, title, objectCategory, objectClass, lastLogonTimestamp, lastLogon, userAccountControl, otherPager, physicalDeliveryOfficeName From 'LDAP://$(RootDse)' Where objectCategory='person' And objectClass = 'user' And name>'$(arg)' // Get rows where "name" is GT the arg 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('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

This works fine! And i have the following code:

LET arg=chr(01); // Set search start name to a very low value DO ADGroups: LOAD Distinct name As GroupName, distinguishedName As GroupDN, info As GroupInfo ; SQL SELECT name, distinguishedName, info FROM 'LDAP://$(RootDse)' WHERE objectCategory='group' AND name>'$(arg)' // Get rows where "name" is GT the arg 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('GroupName') = '$(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 LOOP

Here i get the following script error:

EQ_QVX_SIZELIMIT_EXCEEDED: The size limit for this request was exceeded.
This query was stopped because it tried to read beyond a restricted limit. One of the tables in the query has a limit of maximum 5000 rows.
ADGroups:
LOAD Distinct
name As GroupName,
distinguishedName As GroupDN,
info As GroupInfo

Any ideas how I can fix that?

Re: Problem with export Active Directory

AndyC — Fri, 21 Jun 2024 07:51:01 GMT

The first script has 'relief valve' FIRST 1000 which stops the query from returning too many rows at once.

The code at the bottom is used to pass back the last vale into the select so that the DO loop keeps rolling.

AND name>'$(arg)'

I think you just need to include FIRST 1000 in your second script (same as the first).

Re: Problem with export Active Directory

qlikconsultant — Fri, 21 Jun 2024 08:18:22 GMT

Hi,

thanks, I put First 1000 in the code

LET arg=chr(01); // Set search start name to a very low value DO ADGroups: First 1000 // Workaround for undefined SQL error. Don't forget to sort the entries, so the loop continues LOAD Distinct name As GroupName, distinguishedName As GroupDN, info As GroupInfo ;

but same Error message.

topic Re: Problem with export Active Directory in App Development

Problem with export Active Directory

Re: Problem with export Active Directory

Re: Problem with export Active Directory