Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Rob,
I tried your example from the cookbook site, changed "connect to..." to "lib connect to..." for Sense and I still get QVX_UNEXPECTED_END_OF_DATA:
EDIT: un-commented the "First 1000" line and still get the same error
Hi
My customer is experiencing exactly the same problem.
We had to do a fallback solution. Running the same application in version 10 which works perfectly.
Hello,
I'm testing QV12.20 and it seems to be a bug (from IR to SR6), that "First 1000" and even "First 1" is ignored and you get the "QVX_UNEXPECTED_END_OF_DATA"-error. So there seems to be a bug in QlikSense and QlikView.
With QV12.00SR5 everything is working correctly.
I've sent a bugreport to our QV-partner, but it didn't get forwarded to Qlik.
In the meantime our IT-department changed the pagesize-parameter of the AD to avoid this error and I can't test it anymore.
Kind regards
Peter
Hi, is it possible to retrieve the AccountExpiryDate for each user?
Is it also possible to load Nested users? Example:
AD GroupName: Manager
Users:
Name | Path |
User 1 | Users/Vienna/rootdse |
Test User | Users/Vienna/rootdse |
AdminGroup | security_groups/Group/Vienna/rootdse |
User 2 | O365/Users/Vienna/rootdse |
At the moment the Qlik Cookbook does not load the AdminGroup users properly per group because its a group in the members area, the path is different, is it somehow possible for the script to load those users from the Nested Group 'AdminGroup'?
I would think the AdminGroup member would be loaded as part of the Group load? If so, then I think the question would be how you want to model this nested relationship. Perhaps build a link table such that when you select group=Manager, it links to both Manager and AdminGroup in the Groups table.
-Rob
hello,
did anyone have issues with retrieving the object "description" field ?
i'm getting a blank field...
Hello,
I also enocutered the error 'EQ_QVX_SIZELIMIT_EXCEEDED'..
We tried to comment / uncomment the 1000 line, but it doesn't change anything.
Anyone has idea on how to overcome it ?
We are using QS August 2022 Patch.
Attaching screenshot of the error.
Thank you!
reduce the number of queried rows ?
First 800 instead of 1000...
@_Adam_ : From my vantage point, it looks like a limitation of the OLE DB driver that I use. For my purposes, I opted to loop over user's by the first character of their name. Example:
FOR v=ORD('a') TO ORD('z')
LET q=CHR($(v));
LIB CONNECT TO 'active_directory';
ActiveDirectory:
LOAD
[Country]
,[Division]
,[EmployeeID]
,[employeeType]
,[First Name]
,[Location]
,[Email Address]
,[Manager]
,[Last Name]
,[logonCount]
,[Last Logon]
,[sAMAccountName]
,[Contracted]
,[Title]
,IF([Status] = 'Disabled' AND [Start Date] > [Last Logon] AND [Start Date] > Today(), 'New Hire', [Status]) AS [Status]
,[Name]
,[State]
,[Start Date]
,[memberOf]
,[Region]
,IF([Region] LIKE '*-*' OR [Region] LIKE '*–*', Left([Region],3), [Region]) AS [CountryCode]
,Year([Last Logon]) AS [Last Logon Date Year]
,Month([Last Logon]) AS [Last Logon Date Month]
,Year([Start Date]) AS [Start Date Year]
,Month([Start Date]) AS [Start Date Month]
,[Last Logon] - [Start Date] AS [Tenure]
;
LOAD
//IF(len([co]) > 1, [co], '<Unknown>') AS [Country],
[co] AS [Country],
division AS [Division],
[employeeID] AS [EmployeeID],
[employeeType],
givenName AS [First Name],
l AS [Location],
mail AS [Email Address],
Subfield((SubField([manager],',',1)),'=',2) AS [Manager],
sn AS [Last Name],
[logonCount],
If(lastLogon > lastLogonTimestamp,
(IF(lastLogon > 0, Date(Floor(Date(Left(lastLogon,11)/86400-109205, 'YYYY-MM-DD'))),Null())),
(IF(lastLogonTimestamp > 0, Date(Floor(Date(Left(lastLogonTimestamp,11)/86400-109205, 'YYYY-MM-DD'))),Null())))
AS [Last Logon],
Upper(sAMAccountName) AS [sAMAccountName],
IF(Left(Upper(sAMAccountName),4)='EXT_','True','False') AS [Contracted],
title AS [Title],
ApplyMap('AccountStatusMapping',Num(userAccountControl),'Undefined') AS [Status],
displayName AS [Name],
st AS [State],
Date(Floor(Date(If(Len(extensionAttribute5)>=1,extensionAttribute5,WhenCreated))), 'YYYY-MM-DD') AS [Start Date],
memberOf AS [memberOf],
physicalDeliveryOfficeName AS [Region]
WHERE Len(title)>=7;
SQL SELECT
co,
division,
employeeID,
employeeType,
givenName,
l,
mail,
memberOf,
manager,
sn,
lastLogon,
lastLogonTimestamp,
logonCount,
sAMAccountName,
extensionAttribute5,
title,
userAccountControl,
displayName,
st,
physicalDeliveryOfficeName,
WhenCreated
FROM 'LDAP://<domain>.com'
WHERE objectCategory = 'person' AND displayName ='$(q)*';
NEXT ;
For my purposes, it works fine since I don't have >1000 users with the same first letter. For larger ADs, an alternative approach may be needed.
@mikaelsc : For the AD that I am working with the attribute department uses a multi-valued attribute (https://learn.microsoft.com/sv-se/windows/win32/adsi/single-vs--multiple-value-attributes) which I couldn't sort out how to parse using the bundled OLE DB driver. So I opted to externalize the data fetch to PowerShell:
<Qlik Script>:
// Build a bespoke table for ingestion by PowerShell script to determine departments
// which Microsoft's Active Directory OLE DB driver does not natively parse
UserTable:
NoConcatenate
LOAD
sAMAccountName AS [userid]
Resident ActiveDirectory;
STORE [UserTable] INTO [lib://Data/activeDirectory/users-raw.csv] (txt);
Drop Table [UserTable];
// Step 2 (further in the load)
JOIN([ActiveDirectory])
//departments:
LOAD
Upper(SamAccountName) AS [sAMAccountName],
departmentNumber AS [department]
FROM [lib://Data/activeDirectory/usersWithDepartments.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines) WHERE departmentNumber <> 'Microsoft.ActiveDirectory.Management.ADPropertyValueCollection';
<PowerShell>:
# Adjust to suitable location
Set-Location \\<server>\Data\activeDirectory
$users = Import-Csv -Path .\users-raw.csv
$Output = ForEach ($user in $users){
$tmp = Get-ADUser -Filter "samaccountname -eq '$($user.userid)'" -Properties "departmentNumber"
$tmp | Select-Object SamAccountName,departmentNumber -ExpandProperty departmentNumber
New-Object -TypeName PSObject -Property @{
SamAccountName = $tmp.SamAccountName
departmentNumber = $tmp | Select-Object departmentNumber -ExpandProperty departmentNumber
} | Select-Object SamAccountName,departmentNumber
}
$Output | Export-Csv .\usersWithDepartments.csv