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

    how to extract data from active directory.

      Hi All,

       

      Please let me know step by step qlikview script for extracting data from Active directory.

       

       

       

      Regards

      Ashish

        • Re: how to extract data from active directory.
          sunil jain

          You can list local users and groups using the Net Localgroup command and direct the output to a file.

          http://www.windows-commandline.com/2010/09/list-of-user-groups-command-line.html

           

          Or you can write a VBScript or Powershell script:

          http://gallery.technet.microsoft.com/scriptcenter/efd18173-21d6-4805-bf04-97f7b30d91be

          • Re: how to extract data from active directory.
            Israr Khan

            Hi,

            1) Create a connection, Set the Database drop-down list to OLEDB and click on Connect.

            2013-11-05_145409.png

            2) Select OLEDB Provider for Microsoft Directory Services and click on Next. On the

            Connection tab, select Use Windows NT Integrated Security and click on OK.

             

            3) Enter the following script after the database Connect statement (modify the LDAP

            URI as appropriate for your domain):


            Load *,SubField(distinguishedName, ',') As Properties;

            SQL SELECT

            displayName,

            distinguishedName,

            SAMAccountName,

            mail,

            userPrincipalName,

            SN,

            givenName,

            physicalDeliveryOfficeName,

            userAccountControl

            FROM 'LDAP://mydomain.local'

            WHERE objectCategory = 'Person' AND objectClass = 'user';

            4). Reload the script.

             

            The provider allows  to make these calls against the Active Directory.

            The distinguishedName field will return the full level of the name; for example:


            CN=Angela Bloggs,OU=Marketing,OU=Other Users,DC=mydomain,DC=local

             

             

            Hope it Helps :

             

            Khan

            • Re: how to extract data from active directory.

              Hi Ashish,

               

              Thank you for the example and I tried this script with our Qlik Sense Environment. Connector works fine, at least the Connectivity Testing responded with an "OK". The script runs through the connect, but then throws an error QVX_UNEXPECTED_END_OF_DATA.

               

              I know that there's a discussion out on the net, that LDAP in fact is not a database. But at least its' a hierarchical datastructure none the less.

               

              Thus anyone has an idea how to tackle that error?

               

              Best regards,

              Chris

                • Re: how to extract data from active directory.

                  Hi

                   

                  I tried several approaches. Here's a quick & dirty one: I found out that there is this max number of records you can select with loading from LDAP, which is exactly 1000. 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.

                   

                  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 when not setting the Limit of FIRST 1000 the script throws an error QVX_UNEXPECTED_END_OF_DATA.

                   

                  Kind regards,

                  Chris