Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cpaagh2013
New Contributor III

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
Contributor

Re: Fetch users from Active Directory in Sense script.

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

cpaagh2013
New Contributor III

Re: Fetch users from Active Directory in Sense script.

I am affraid not :-(

Employee
Employee

Re: Fetch users from Active Directory in Sense script.

lucienorrin
Contributor

Re: Fetch users from Active Directory in Sense script.

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.

Employee
Employee

Re: Fetch users from Active Directory in Sense script.

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
Contributor

Re: Fetch users from Active Directory in Sense script.

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!

Employee
Employee

Re: Fetch users from Active Directory in Sense script.

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
Contributor

Re: Fetch users from Active Directory in Sense script.

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

Employee
Employee

Re: Fetch users from Active Directory in Sense script.

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.