5 Replies Latest reply: Nov 29, 2016 11:33 AM by Christoph Schlunegger RSS

    Qlik Sense and LDAP content

    Christoph Schlunegger

      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

          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
              Christoph Schlunegger

              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 Gajare

                  At the start of load statement add First 1000

                    • Re: Qlik Sense and LDAP content
                      Christoph Schlunegger

                      Hi

                       

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

                      DO

                      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