Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

Active Directory: Extract Users and Groups

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
ToniKautto
Employee
Employee

Active Directory: Extract Users and Groups

Last Update:

Jan 29, 2018 10:39:19 PM

Updated By:

ToniKautto

Created date:

Jan 29, 2018 10:39:19 PM

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;

Comments
lucienorrin
Creator
Creator

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

0 Likes
jerrysvensson
Partner - Specialist II
Partner - Specialist II

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.

0 Likes
peterwh
Creator II
Creator II

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

0 Likes
prees959
Creator II
Creator II

Hi,  is it possible to retrieve the AccountExpiryDate for each user?

 

0 Likes
JPT
Partner - Contributor II
Partner - Contributor II

@rwunderlich 

Is it also possible to load Nested users? Example:

AD GroupName: Manager

Users:

NamePath
User 1Users/Vienna/rootdse
Test UserUsers/Vienna/rootdse
AdminGroupsecurity_groups/Group/Vienna/rootdse
User 2O365/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'?

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

0 Likes
mikaelsc
Specialist
Specialist

hello, 

did anyone have issues with retrieving the object "description" field ? 

i'm getting a blank field... 

 

mikaelsc_0-1651218953613.png

 

0 Likes
_Adam_
Contributor III
Contributor III

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.

EQ_QVX_SIZELIMIT_EXCEEDED.png

 Thank you!

0 Likes
mikaelsc
Specialist
Specialist

reduce the number of queried rows ?

First 800 instead of 1000... 

Levi_Turner
Employee
Employee

@_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

 

Version history
Last update:
‎2018-01-29 10:39 PM
Updated by: