Active Directory: Extract Users and Groups

[ToniKautto]

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;

[OmarBenSalem]

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

U made mu day dear Toni

[fosterma]

Thanks, Worked perfectly with minor modifications for my environment.

I'm already excited for the potential use cases.

[lucienorrin]

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

[ToniKautto]

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?

[evan_kurowski]

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.

[ToniKautto]

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.

[rwunderlich]

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.

[ToniKautto]

Thanks Rob, great example!

[evan_kurowski]

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 }

[evan_kurowski]

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;