Connecting to and Querying Active Directory for Users

[#1]

Hello all,

I thought I'd share this as I couldn't find a specific instance of this code.

I had a need to read all the users from an active directory.  Aside from getting the connectivity correct I also ran into MS's 1000 row limit on querying the AD.  After reading a couple of blogs I came up with the code for the Load Script below:

// Connection string

CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Data Source=LDAP://xx.yyyy.com:389;Mode=Read];

set values='A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z';

let valuecount = SubStringCount(values,',');
for i = 1 to valuecount + 1
let value = subfield(values,',', i) & '*';
[ADUsers]:
sql select cn, sAMAccountName, displayName from 'LDAP://xx.yyyy.com' where objectClass = 'User' and objectCategory = 'Person' and sAMAccountName = '$(value)';
next

This code basically queries the AD for each letter in the alphabet as a wildcard.  So if you have less than 26,000 entries in your AD you're good, as long as they are not more than 1000 by letter.  🙂

If this doesn't return all the data you might want to change the values for make it a nested loop to do AA, AB, AC, etc.

If there's a better way, aside from tweaking the AD, please let me know!

Hope this helps someone.

Russ

[jpbartolomeo]

Hi Community,

I have this script:

LET arg=chr(01);

DO

ADUsers:

First 500

LOAD

name,

sAMAccountName as [8ID],

cn as NombreCompleto,

DistinguishedName as KEY,

If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

If(IsNull(title), '*-*', title ) as Puesto,

ipphone as TelTrabajo,

If(IsNull(mail), '*-*', mail) as Email,

company as Compañia,

If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

If(IsNull(genero), '*-*', genero) as Genero,

If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

If(IsNull(estado), '*-*', estado) as Estado,

tsalida as TipoSalida,

Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

Date#(fsalida, 'DDMMYYYY') as FechaSalida;

SQL SELECT

name,

sAMAccountName,

DistinguishedName,

cn,

physicaldeliveryofficename,

department,

title,

ipphone,

mail,

company,

manager,

genero,

tcontrato,

estado,

tsalida,

fingreso,

fsalida

FROM 'LDAP://192.168.0.1'     

WHERE objectCategory = 'user'

  and name > '$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

EXIT DO WHEN NoOfRows('ADUsers') = 0;

EXIT DO WHEN peek('name') = '$(arg)';

LET arg = peek('name');

LOOP;

It works ok in Qlikview, but fails in QlikSense Server June 2017 Patch2 (11.11.3) Any thoughts?

Thanks!

[jpbartolomeo]

BTW, the error is the classic "QVX_UNEXPECTED_END_OF_DATA".

[marcelo_7]

I was still having problems because not all accounts were being loaded. I modified the script and although you can probably make it better at least I am now getting all accounts. This is assuming there are less that 1000 people per letter.

CONNECT TO 'Provider=ADsDSOObject';

for each letter in 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'


let startChr = chr(ord('$(letter)'));
let endChr = chr(ord('$(letter)')+1);


ADUsers:
FIRST 1000 // Workaround for undefined SQL error. 
LOAD DISTINCT
// Add addtional Fields as required
name as UserName,
distinguishedName as UserDN,
sAMAccountName
;
 SQL select 
// Add addtional Fields as required
name, 
distinguishedName,
sAMAccountName
FROM 'LDAP://$(RootDse)'  WHERE objectCategory='person' 
AND sAMAccountName>='$(startChr)' AND sAMAccountName<'$(endChr)'; // Get rows where "name" is GT the arg


next;