topic Re: Active Directory load in QlikView

Active Directory load

Anonymous — Mon, 09 Jun 2014 14:44:52 GMT

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

Re: Active Directory load

Bill_Britt — Mon, 09 Jun 2014 15:00:47 GMT

I would think you could use something like:

FROM ''LDAP://DC= test.com'' WHERE memberOf= 'CN=[groupname]'

Bill

Re: Active Directory load

Anonymous — Mon, 09 Jun 2014 15:38:48 GMT

Thanks Bill,

This was the first thing I've tried, and it returns no records. (I checked Group names when loading Groups separately).

I also tried to use "like":
WHERE memberOf like 'CN= ABC%',
But in this case just getting error on reload.

Any other ideas?

Re: Active Directory load

erikzions — Mon, 09 Jun 2014 15:49:44 GMT

This is how I do that. Tough to be fair I am sure I got this from Rob Wunderlich. Modified slightly to meet my needs.

ADGroups:

LOAD

name as GroupName,

distinguishedName as GroupDN,

info as GroupInfo

;

SQL select

name, distinguishedName,

info

FROM 'LDAP://$(RootDse)' WHERE objectCategory='group'

AND name = 'QV_*';

Re: Active Directory load

rwunderlich — Mon, 09 Jun 2014 15:55:14 GMT

The way Bill posted is the way I do it and it works for me. Check out Qlikview Cookbook: Load Users and Groups from Active Directory for sample code.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Active Directory load

Anonymous — Mon, 09 Jun 2014 18:47:08 GMT

Thanks Eric,

You syntax works but it doesn't answer my question - can I load users that belong to a particular group without loading the group itself?

Certainly I can load all, and remove the data I don't need, but it is rather inefffective...

Regards,

Michael

Re: Active Directory load

Anonymous — Mon, 09 Jun 2014 19:07:39 GMT

Thanks Rob,

My current "AD load" app is in fact based on your older version, the one with macro. I've downloaded your newer "no-macro" version and going to try it. If nothing else, the absence of macro has value by itself.

Regards,

Michael

Re: Active Directory load

erikzions — Mon, 09 Jun 2014 19:08:16 GMT

Try this. Again I got this from Rob, so you can refer to his code too.

CONNECT TO 'Provider=ADsDSOObject';

ADGroups:

LOAD

name as GroupName,

distinguishedName as GroupDN,

info as GroupInfo

;

SQL select

name, distinguishedName,

info

FROM 'LDAP://$(RootDse)' WHERE objectCategory='group'

AND name = 'QV_*';

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

LET arg= NoOfRows('ADGroups');

For i = 0 to NoOfRows('ADGroups') - 1

LET Group = peek('GroupDN', $(i), 'ADGroups');

ADGroupMembers:

LOAD DISTINCT

distinguishedName as UserDN, '$(Group)' as GroupDN, name, userPrincipalName,

left(userPrincipalName,(FindOneOf(userPrincipalName,'@')-1)) as zNumber;

SELECT distinguishedName, name, userPrincipalName

FROM 'LDAP://$(RootDse)' WHERE MemberOf='$(Group)';

NEXT i;

Re: Active Directory load

rwunderlich — Mon, 09 Jun 2014 19:10:54 GMT

The no-macro version runs a lot slower -- but it work in server reloads, which is usually critical.

-Rob

Re: Active Directory load

Anonymous — Mon, 09 Jun 2014 20:24:52 GMT

I see, load the groups I need first, and get members after this. Looks like working, after some script cleanup, but sometimes fails depending on groups. Something to figure out...

Re: Active Directory load

Anonymous — Mon, 09 Jun 2014 20:32:13 GMT

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.

Re: Active Directory load

NZFei — Thu, 11 Dec 2014 03:43:10 GMT

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

Re: Active Directory load

rwunderlich — Thu, 11 Dec 2014 17:05:55 GMT

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

Re: Active Directory load

NZFei — Thu, 11 Dec 2014 23:26:29 GMT

Thanks Rob!

Re: Active Directory load

peterwh — Wed, 22 Apr 2015 11:43:43 GMT

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

Re: Active Directory load

rwunderlich — Wed, 22 Apr 2015 16:45:03 GMT

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

Re: Active Directory load

peterwh — Fri, 24 Apr 2015 06:38:01 GMT

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

Re: Active Directory load

peterwh — Fri, 24 Apr 2015 07:52:05 GMT

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

Re: Active Directory load

jt422605 — Mon, 23 Sep 2019 17:55:09 GMT

@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

Re: Active Directory load

rwunderlich — Thu, 26 Sep 2019 17:13:01 GMT

//FIRST 1000

commented or not in your script?

-Rob