article Active Directory: Extract Users and Groups in Qlik Sense Documents

Active Directory: Extract Users and Groups

ToniKautto — Tue, 30 Jan 2018 03:39:19 GMT

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,

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)';

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;

Re: Active Directory: Extract Users and Groups

OmarBenSalem — Tue, 30 Jan 2018 15:00:49 GMT

Thank u for sharing; I was just looking for something like this !

U made mu day dear Toni

Re: Active Directory: Extract Users and Groups

fosterma — Fri, 02 Feb 2018 16:04:07 GMT

Thanks, Worked perfectly with minor modifications for my environment.

I'm already excited for the potential use cases.

Re: Active Directory: Extract Users and Groups

lucienorrin — Tue, 06 Feb 2018 20:42:25 GMT

Have you run into the "QVX_UNEXPECTED_END_OF_DATA" error when returning a large number of objects?

Re: Active Directory: Extract Users and Groups

ToniKautto — Thu, 08 Feb 2018 00:15:39 GMT

I have not extracted more than 5000 users and 10000 groups with this script, and have not seen any problem. Possible the execution time might get long if you have a very large directory, where the connection or server side might time out.

Do you get the error when extracting users or during looping through groups?

Re: Active Directory: Extract Users and Groups

evan_kurowski — Mon, 12 Feb 2018 18:46:43 GMT

We read from Active Directory all the time (usually via Powershell & the server module snap-ins dsa.msc & dsac.exe).

I was excited to try this Qlik-centric method for parsing the AD catalog, and while a connection successfully establishes via ADsDSOObject, the results getting returned from the query appear limited. A few issues having on my end:

The query won't return catalog listing unless the where clause narrows in pretty specifically, ran into the "QVX_UNEXPECTED_END_OF_DATA" any time the where clause attempted to widen out via wildcard
The field names of the AD elements don't appear to match the same used in Microsoft.ActiveDirectory Powershell module or the snap-ins. (i.e. the ADsDSOObject query will return the fields Name, samAccountName, but can't find fields Surname, SID, or Givenname. Attempting to use the 'Select' button in QV script editor to attempt schema browsing doesn't pull up anything)

While I'd like to see more examples of how an ADsDSOObject connection to ActiveDirectory works, a Powershell routine recursively dumping all AD objects out into a .csv (rows for all container types: groups, accounts, devices, etc..blended together, letting UI selection then separate them) and then reparsed back in, has been the most expedient method experienced. Always open to a quicker way though.

Re: Active Directory: Extract Users and Groups

ToniKautto — Mon, 12 Feb 2018 21:55:07 GMT

My best guess on the QVX_UNEXPECTED_END_OF_DATA is that it due to the ADsDSOObject not managing the wider query. I use a wildcard in my query above to retrieve the personal records, which in my case are well below 10,000.

Does your powershell script have the exact same LDAP queries as you attempt in Qlik Sense?

The script above is a working example from my side, so the field names do come through as I expect. Unfortunately the Select option is not likely to enable browsing the schema, as the native OLEDB wizard does not understand LDAP.

Re: Active Directory: Extract Users and Groups

rwunderlich — Tue, 13 Feb 2018 04:05:55 GMT

This example

Qlikview Cookbook: Load Users and Groups from Active Directory http://qlikviewcookbook.com/recipes/download-info/load-users-and-groups-from-active-directory/

shows how to deal with the AD return limits by looping. It also shows that you can use the FIRST nnn prefix to work around the QVX_UNEXPECTED_END_OF_DATA error. It's a QV example, but works in QS if you change the connection to a lib.

Re: Active Directory: Extract Users and Groups

ToniKautto — Tue, 13 Feb 2018 04:12:22 GMT

Thanks Rob, great example!

Re: Active Directory: Extract Users and Groups

evan_kurowski — Tue, 13 Feb 2018 16:54:01 GMT

Hello Rob, Toni,

In this second example cited, the connection established to AD via ADsDSOObject works as well, and AD queries prepared in 'Load-Users-and-Groups-from-Active-Directory.qvw' offer a little more guidance returning results for a poll of ADUsers & also members of a specific ADGroup.

However, limitations still apply. The query stopped at 1,000 rows (the script produced an error, but clicking past it, the post-reload data model still held 1,000 returned results). Circumventing this would be a big help as domains we’re polling will have tens of thousands of users (and most enterprise clients would probably size the same).

I expected changing the value of arg would alter filter results, and assigning arg to chr(65) ‘A’ would return the list of users starting with that letter, but it appears the same list as chr(01) was returned.

LET arg=chr(01); // Set search start name to a very low value

An issue with the results from group membership was the localgroup cross-domain visibility. When I hardcoded the query to itemize a localgroup that had poly-domain members, all the members not in the current domain were absent.
(do these omissions result from the query being performed from a specific domain, or permissions of programmer performing the query being allowed to peer into other domains?)

While the technique of 'net' represents an iteration backwards, using this older command will include membership of poly-domain entrants (who’s output can then be text parsed):

net localgroup /domain my_localgroup

The text parsing can be cumbersome, open to alternatives re: domain scope issues.

For Powershell queries:

#Exporting AD contents will be comprehensiveGet-ChildItem -Recurse | select -property ObjectClass, Name, PSPath

#List ADGroup membership, will limit to just domain visibility (localgroup entries may be incomplete)

Get-ADGroup -filter {Name -like "Qlik*"} | % {Get-ADGroupMember -Recursive $_.Name } | % {$_.name | get-aduser | Select -Property Name, Surname, Givenname, SID }

Re: Active Directory: Extract Users and Groups

evan_kurowski — Wed, 14 Feb 2018 16:19:30 GMT

I doubled back on this today with additional testing. Looking over the original code I thought maybe uncommenting FIRST 1000 was the key to unlocking (though this was tried yesterday as well, but yesterday's screenshot does not reflect this). I expected being able to report everything was working, and AD was extracting in a series of 1,000 row chunks.

However, in trying the verbatim sequence from the original, still getting an error. To verify the LDAP connection & field names are valid, the first query does return a 1 row result matching a specific samAccountname.

Repointing the RootDse to another domain is able to read the presence of single accounts/groups in other targeted domains, but when requesting membership of a localgroup defined in domain A, having accounts from both domain A & B, the listings returned skip the membership set from domain B.

Setting ErrorMode = 0 and then resetting it back to ErrorMode = 1 has started preventing the EXIT DO WHEN ScriptError > 1 from exiting the loop. Noticed this line also doesn’t end with a semi-colon like the other EXIT DOs.

SET RootDse = 'xxxxxxxxxx';

OLEDB CONNECT TO [Provider=ADsDSOObject;Data Source=LDAP://xxxxxxxxxx;];

//This is returning a row
ActiveDirectory:
LOAD name, distinguishedName;
SQL SELECT name, distinguishedName
FROM 'LDAP://$(RootDse)' WHERE samAccountname = 'xxxxxxx';

LET arg=chr(01);     // Set search start name to a very low value
DO
ADUsers:
FIRST 1000      // Workaround for undefined SQL error.
     LOAD DISTINCT
// Add addtional Fields as required
           name as UserName,
distinguishedName as UserDN
;
SQL select
// Add addtional Fields as required
           name, distinguishedName         // Fields to select
     FROM 'LDAP://$(RootDse)' WHERE objectCategory='person'
AND name>'$(arg)';   // 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

exit script;

Re: Active Directory: Extract Users and Groups

lucienorrin — Thu, 12 Apr 2018 14:26:15 GMT

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

Re: Active Directory: Extract Users and Groups

jerrysvensson — Fri, 06 Jul 2018 10:19:38 GMT

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.

Re: Active Directory: Extract Users and Groups

peterwh — Wed, 22 Aug 2018 08:00:51 GMT

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

Re: Active Directory: Extract Users and Groups

prees959 — Thu, 12 Sep 2019 10:17:37 GMT

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

Re: Active Directory: Extract Users and Groups

JPT — Mon, 16 Aug 2021 12:07:28 GMT

@rwunderlich

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'?

Re: Active Directory: Extract Users and Groups

rwunderlich — Tue, 17 Aug 2021 18:46:49 GMT

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

Re: Active Directory: Extract Users and Groups

mikaelsc — Fri, 29 Apr 2022 07:56:11 GMT

hello,

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

i'm getting a blank field...

Re: Active Directory: Extract Users and Groups

_Adam_ — Tue, 02 May 2023 10:53:40 GMT

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!

Re: Active Directory: Extract Users and Groups

mikaelsc — Tue, 02 May 2023 15:49:04 GMT

reduce the number of queried rows ?

First 800 instead of 1000...