Fetch users from Active Directory in Sense script.

[Florentin74]

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.

[jasonmomoa]

Hi cpaagh2013‌,

I recently created an LDAP extractor to fetch some AD Groups and the users from them. Find below some guidelines. You'll need to adapt tou your provider and sources.

// Define set of ADGroups to search
SYS_ADGROUPS:
LOAD * INLINE [
 ADGroups
   YOUR_GROUPNAME_HERE 
];

// Connect to LDAP Stream
LIB CONNECT TO 'MY LDAP CONNECTION'; // Confiure it in the right panel

// Get distinguished adgroup name from shortened adgroup name
// Get users from distinguished names
FOR i = 0 to NoOfRows('SYS_ADGROUPS') -1
 LET group = Peek('ADGroups', $(i), 'SYS_ADGROUPS');

 M_ADGROUPS:
 LOAD
   name as GroupName,
   distinguishedName as DistinguishedGroupName;
 SQL Select
   name, distinguishedName
 FROM 'LDAP://SOURCE'
 WHERE objectClass='group' AND name='$(group)';

 LET distname = Peek('DistinguishedGroupName', $(i), 'M_ADGROUPS');

 M_ADGROUP_MEMBERS:
 LOAD Distinct
   SamAccountName as [User NTNAME],
  '$(group)' as [AD Group], 
   mail as Email;
 SELECT SamAccountName, mail
 FROM  'LDAP://SOURCE'
 WHERE  memberOf='$(distname)';

NEXT i;
Drop Tables SYS_ADGROUPS;

Hope this helps!

[ToniKautto]

See Active Directory: Extract Users and Groups for an opposite example, where I extract users first and then extract groups. The suitable solution might depends on directory configuration.

In my case I use OLEDB connection to access AD, with this connection string;

OLEDB CONNECT TO [Provider=ADsDSOObject;Integrated Security=SSPI;Data Source=ldap://domain.local;]