topic Re: QlikSense - Connection to Active Directory in QlikView

QlikSense - Connection to Active Directory

jpbartolomeo — Wed, 20 Dec 2017 17:46:34 GMT

Dear Community:

I have the next code running ok in Qlikview. But the same code doesn't run in Qliksense, even with the connector ok I've already checked the thread Connecting to and Querying Active Directory for Users:

LET arg=chr(01);

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;

The error is "QVX_UNEXPECTED_END_OF_DATA".

Thanks in advance.

Re: QlikSense - Connection to Active Directory

Anil_Babu_Samineni — Thu, 21 Dec 2017 00:58:44 GMT

I believe you've connected thru ODBC, And my suggestion is OLEDB to resolve this issue

And one more thought, I have do you have any non english fields data with you?

Re: QlikSense - Connection to Active Directory

jpbartolomeo — Thu, 21 Dec 2017 16:22:51 GMT

Anil:

Many thanks for the response.

I'm using OLE DB to connect but, like you said, I have in the data non-english fields (my language is Spanish).

Do you know how to retrieve them?

Re: QlikSense - Connection to Active Directory

Anil_Babu_Samineni — Tue, 26 Dec 2017 12:19:16 GMT

That means, you have English and Non English data with you? or only non English data if so we need to change our extension of En-US to required one

Re: QlikSense - Connection to Active Directory

jpbartolomeo — Tue, 26 Dec 2017 17:59:23 GMT

Anil:

Basicaly we have non-english data. But I suppose it doen't affect having english data too. With non-english data we have special characters, plus all the characters of the English language.

What do you mean with changing the extension to requiered one? The only place I know to configure the language is with the variable CollationLocale in the Main tab, which currently is 'es-CL' .

Thanks!

Re: QlikSense - Connection to Active Directory

jpbartolomeo — Thu, 28 Dec 2017 19:45:40 GMT

Anil:

If I query this, it works fine:

SQL select *

FROM 'LDAP://10.148.1.6'

WHERE objectCategory='person' and name='a*' ;

But obviously, I'm quering the user starting with "a".

Hope it helps to find the answer.

Re: QlikSense - Connection to Active Directory

Anil_Babu_Samineni — Fri, 29 Dec 2017 01:12:32 GMT

This will work, May be ObjectCategory and name carrying the person and a values. Do you want this in Qlik?

Re: QlikSense - Connection to Active Directory

jpbartolomeo — Fri, 29 Dec 2017 13:41:45 GMT

Anil:

I want all the users.

We found a workaround for this issue, looping through all the first letter's alphabet characters and numbers.

I'll post the result when I finish.

Thanks!

Re: QlikSense - Connection to Active Directory

jpbartolomeo — Tue, 02 Jan 2018 17:30:36 GMT

So, this is my workaround:

LIB CONNECT TO 'LDAP-- (user)';

//Users begining with 0-9

For i=48 to 57 //Unicode chr(48)=0, chr(57)=9

LET char = chr($(i));

AD:

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='person' and name='$(char)*' ;

Next i;

// Users begining with A-Z

For i=65 to 90

LET char = chr($(i)); //Unicode chr(65)= a, chr(90)= z (AD is not case sensitive)

AD:

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='person' and name='$(char)*' ;

Next i;

I hope this will be useful. Thanks Anil for your help!