topic Re: Qlik Sense and LDAP content in App Development

Qlik Sense and LDAP content

Tue, 29 Nov 2016 06:15:13 GMT

Dear all,

Are there any Scripting functions / variables available in Qlik Sense to "collect" LDAP content on the user, besides the function OSUser()?

Thank you

Re: Qlik Sense and LDAP content

Gysbert_Wassenaar — Tue, 29 Nov 2016 07:50:46 GMT

Not as part of the authentication. You can of course use the LDAP as a data source and retrieve any information you think you need. How you do that depends on how and where the LDAP data is stored. Perhaps it's a database that can be accessed over an ODBC connection.

Re: Qlik Sense and LDAP content

shraddha_g — Tue, 29 Nov 2016 07:55:23 GMT

Refer how to extract data from active directory.

Connecting to and Querying Active Directory for Users - Here you will get sample code attached in Rob's Reply

Re: Qlik Sense and LDAP content

Tue, 29 Nov 2016 15:14:02 GMT

Hi Shraddha

Thank you for the Information. I found there also some other examples, following Rob Wunderlich's thread you pointed me to.

Thus, establishing a Qlik Sense Connector to the LDAP/Active Directory was successful, at least the Connection testing returned 'OK'. However, the SQLfrom Rob's example as well as a looping script throw an error "QVX_UNEXPECTED_END_OF_DATA".

Anyone knows how to deal with that?

Kind regards,

Chris

Re: Qlik Sense and LDAP content

shraddha_g — Tue, 29 Nov 2016 16:07:19 GMT

At the start of load statement add First 1000

Re: Qlik Sense and LDAP content

Tue, 29 Nov 2016 16:33:52 GMT

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);

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