Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
BTW, the error is the classic "QVX_UNEXPECTED_END_OF_DATA".
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;