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;
