Active Directory: Extract Users and Groups

    This is a short summary of how I connect to my corporate Active Directory and extract employee records and group memberships for theses employees. This is the most time efficient way I have found that works in my environment, but there might other query options.

     

    NOTE: The below example connects to domain.local. Please change these references to your domain name.

     

    First step is to define a connection in Qlik Sense. I have found that OLEDB and ADsDSOObject provider works. For server side app deployment, make sure the service account is allowed to connect to the AD, or add name and password or a authorized user in the connection settings.

     

    OLEDB CONNECT TO [Provider=ADsDSOObject;Integrated Security=SSPI;Data Source=ldap://domain.local;]

     

    In the load script connect to the directory with a LIB CONNECT string.

     

    LIB CONNECT TO 'LDAP Domain.local (tko)';

     

    The below load statement extract all person records from AD. In the SQL query define the field names exactly as they are named in your AD. Notice, the FROM definition requires the LDAP URL to your domain.

     

    ActiveDirectory:

    LOAD *

    WHERE Not(userAccountControl bitand 2); // Exclude inactive accounts

    SQL SELECT

    co,

    department,

    division,

    employeeType,

    givenName,

    l,

    mail,

    manager,

    mobile,

    pager,

    sn,

    sAMAccountName,

    telephoneNumber,

    title,

    userAccountControl,

    displayName,

    streetAddress,

    postalcode,

    st,

    physicalDeliveryOfficeName

    FROM 'LDAP://domain.local'

    WHERE objectCategory = 'person' AND memberOf = '*'; // The * wildcard returns all records in Persons's objectCategory

     

    Next step is to extract a list of all the existing groups in AD. Notice that your domain name has to be referred both in the FROM and WHERE in the SQL query.

     

    ActiveDirectoryGroups:

    LOAD

    name AS GroupName,

    name ,

    distinguishedName;

    SQL SELECT 

    name,

    distinguishedName

    FROM 'LDAP://domain.local'

    WHERE objectCategory = 'CN=Group,CN=Schema,CN=Configuration,DC=domain,DC=local';

     

    Extracting all members of each group enables pairing groups with their exact members. Each group is queried to extract the member list.

     

    FOR i = 0 to NoOfRows('ActiveDirectoryGroups')-1

     

         // Pick next group name and distinguished name

         LET vGroupName = Peek('name' , $(i), 'ActiveDirectoryGroups');

         LET vDistinguishedName = Peek('distinguishedName' , $(i), 'ActiveDirectoryGroups');

     

         TRACE $(i) of $(#vNoOfRowsActiveDirectoryGroups) : $(vGroupName);

     

         // Load person identifier from the current distinguished group

         // The result is stored in Temporary table

         // Each iteration is added to the same Temporary table

     

         TmpSamAccountNameGroups:

         LOAD

         sAMAccountName,

         '$(vGroupName)' AS GroupName

         ;

         SQL SELECT

              sAMAccountName

         FROM 'LDAP://domain.local'

         WHERE objectCategory = 'person' AND memberOf = '$(vDistinguishedName)';

     

    NEXT

     

    The extracted members are left joined to employee table, to map employees with their group membership. With the result of one table showing all employees and their AD group membership

     

    // Left join to exclude sAMAccounts from group view that are not active

    Left Join ('ActiveDirectory')

    LOAD *

    Resident TmpSamAccountNameGroups;

     

    // Drop temporary tables

    DROP Tables TmpSamAccountNameGroups, ActiveDirectoryGroups;