Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Florentin74
Partner - Creator
Partner - Creator

Fetch users from Active Directory in Sense script.

Hi there,

I have not been able to fetch users from Active Directory in a Qlik Sense script via the OLE DB provider for Microsoft Directory Services. I am able to do it in Qlikview with the following script:

OLEDB CONNECT32 TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET vLDAP_UNC = 'LDAP://*****.COM';
LET vUsername=chr(01); // Set search start name to a very low value

DO

ADUsers:
FIRST 901  // Workaround for undefined SQL error. Uncomment this line if you are getting the error
LOAD
  name     as userName,
  distinguishedName  as userDN,
  sAMAccountName  as userID,
  company,
  department,
  OU     as orgUnit,
  c     as country,
  '$(vUsername)'   as vUsername
;
SQL SELECT
  name,
  distinguishedName,
  sAMAccountName,
  company,
  department,
  OU,
  c
FROM
  '$(vLDAP_UNC)'
WHERE
  SAMAccountName >'$(vUsername)'
;

EXIT DO WHEN ScriptError > 1; // Stop loop if SELECT has error
EXIT DO WHEN NoOfRows('ADUsers') = 0;  // Stop loop if SELECT returns nothing
EXIT DO WHEN peek('userID') = '$(vUsername)';  // If the last "name" read is EQ to arg -- no more entries

LET vUsername = peek('userID'); // Set the arg to the last "name" read
LOOP

In Sense I have to create a connection... All I type as source is LDAP://<domain>.com ...

How do I go on from here? Does anyone know?

Please notice that I cannot switch to legacy mode, because this will affect all apps on the server.

21 Replies
lucienorrin
Creator
Creator

Looking for an answer to this also. Were you able to get it working?

Florentin74
Partner - Creator
Partner - Creator
Author

I am affraid not 😞

Tyler_Waterfall
Employee
Employee

lucienorrin
Creator
Creator

Thanks for the tip! We have an older Qlik View environment that uses the Logparser method you referenced. While it does work, it must be installed, which may not be an option for some environments. I cobbled together a powershell script to export the AD info I needed. It works and it doesn't require any installation. It is a bit clunky and requires additional documentation for the next admin (as does logparser), needs to be scheduled outside of Qlik as well.

I am a bit frustrated Qlik provides a built-in connector for MS Active Directory but using a 3rd party tool to extract data from AD seems to be the accepted standard around the community.

Tyler_Waterfall
Employee
Employee

I just used the script in the example from Rob, not with the log parser.

Or, if you are running Sense, you can get some user information directly from the users QRS endpoint or in the License Monitor app. Let me know if you are interested in that.

lucienorrin
Creator
Creator

I don't follow, the link to Rob's example was using Logparser. You ran the script from Sense and it pulled data from AD?

What my customer wants to do is be able to pull basically any and all AD account (user/group/computer) attributes from AD for analysis. Qlik users would be included in that but they do not want it limited to users who have been synced into Qlik Sense, they need all AD accounts.

Thanks!

Tyler_Waterfall
Employee
Employee

Sorry it wasn't clear. I meant his recipe in his QlikView Cookbook.

load-users-and-groups-from-active-directory

Cookbook page: Recipes | Qlikview Cookbook

lucienorrin
Creator
Creator

After changing the rootdse to my domain I get the following error when I load dataCapture.PNG

Tyler_Waterfall
Employee
Employee

Ooh, using Sense! You would need to figure out how to create a data connection to active directory in Sense.

I was just using QlikView in my example, and I needed to uncomment the line in the load script First 1000.