Active Directory load

[Anonymous]

I'm loading data from Active Directory.  The user select looks more or less like this:

SQL SELECT
distinguishedName,
cn,
displayName,
mail
FROM '$(vLDAP)';

Question: is it possible to load only the users who belong to a certain Group or several Groups before loading Groups themsleves?

Thanks,

Michael

[Anonymous]

Yes, it is slow, but in most cases it's not the highest priority.

So, If I load Groups (limited subset) in the beginning, followed by the Group members, it should be close enough, and with reasonable relaod time.

[NZFei]

Why when I run the script to load data from AD, the same script works for some customers' server but not the others?

I got this error message for a server:

11/12/2014 16:36:04: 0014  select
11/12/2014 16:36:04: 0015    name, distinguishedName 
11/12/2014 16:36:04: 0016   FROM 'LDAP://customerserver.com'  WHERE objectCategory='person'
11/12/2014 16:36:04:        2 fields found: distinguishedName, name, 
11/12/2014 16:36:04:       QVX_UNEXPECTED_END_OF_DATA:
11/12/2014 16:36:04:       Error: OLEDB read failed

Is it a driver issue? How can we fix it?

Thanks

Fei

[rwunderlich]

I see this problem occasionally and work around it by adding a

FIRST 1000 LOAD

before the SQL SELECT. See:

Load Users and Groups from Active Directory http://qlikviewcookbook.com/recipes/download-info/load-users-and-groups-from-active-directory/

for an example.

-Rob

[NZFei]

Thanks Rob!

[peterwh]

Hello Mr. Wunderlich,

I've used your script for reading our Active Directory and I had to use the "first"-statement, because of the "QVX_UNEXPECTED_END_OF_DATA".

In my opinion there is a small bug which caused that not all records where loaded and some where loaded two or three times (I didn't use "distinct"). If I load all users I have a difference of nearly 5.000 entries.

My solution was to add an "ORDER BY name;" at the end of the SQL-statements.

Kind regards

Peter

[rwunderlich]

Peter,

Thanks for the feedback. The code as posted on QlikviewCookbook.com does include the "order by name" for the users. I note it doesn't for the groups. Is it the groups you were having trouble with?

-Rob

[peterwh]

Hello Rob,

I just downloaded the file from http://qlikviewcookbook.com/recipes/download-info/load-users-and-groups-from-active-directory/ (it's Revision = 1) and I've found no "order by" not even on the "Users"-tab. Am I missing something?

I found the problem as I read the users from AD. I have to extract data from 3 ADs and the last one was the only one with the 1000-restriction. So I looked for a solution other than contacting the AD-Administrators for setting the "MaxPageSize" to a higher value.

Kind regards

Peter

[peterwh]

Hello Rob,

I've just found another problem in my setup:

the generated qvd's have an invalid xml-header. If I load the generated QVDs in QlikView theres no problem. If I try to open a qvd with QViewer, I get the message, that it has an invalid xml-header. I also get an error, if I load the QVD-Header in QlikView with something like that:

LOAD

    '$(QVD_Id)'      as QVD_Id,

    CreatorDoc,

    NoOfRecords

    FROM [$(QVD)] (XmlSimple, Table is [QvdTableHeader]);

I think it comes from "LET arg=chr(01)". I've changed it to "LET arg=chr(33)" and now I don't have the mentioned problems.

Kind regards

Peter

[jt422605]

@rwunderlich  - I am following your script but still  cannot get rid off the 1000 row limit .. 

The following error occurred:

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 1000 rows.

What going wrong - I am not sure.. can you please suggest  ?  

LET arg=chr(01);	// Set search start name to a very low value
DO 
	ADUsers:
	//FIRST 1000		// Workaround for undefined SQL error. 
	LOAD DISTINCT
	*
	;
	SQL select 
	// Add addtional Fields as required
	 displayName,employeeID,distinguishedName,mail,objectsid, userprincipalname, cn
    ,telephoneNumber,streetaddress, pager, company, title	// Fields to select 
	FROM 'LDAP://$(RootDse)'  
    WHERE  
    objectCategory='person'  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

[rwunderlich]

Is 

//FIRST 1000

commented or not in your script?

-Rob