Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
Thanks Rob!
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
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
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
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
@rwunderlich - I am following your script but still cannot get rid off the 1000 row limit ..
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
Is
//FIRST 1000
commented or not in your script?
-Rob