topic Re: Active Directory Query in QlikView

Active Directory Query

Anonymous — Thu, 30 Apr 2015 12:04:18 GMT

Hi Guys,

I am pulling the user attributes from AD using the below script. And it is working great. But I have another LDAP source as eu.corp

How to include this in the same script?

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://na.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

st,

co,

FROM 'LDAP://na.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

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

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

LET arg=peek('name');

Re: Active Directory Query

quwok — Thu, 30 Apr 2015 13:21:54 GMT

QlikView can only connect to one data source at a time, you'll need to do something like:

CONNECT source1

Load all tables required from source1

CONNECT source2

Load all tables required from source2

script to relate source1 and source2 tables

Re: Active Directory Query

Anonymous — Thu, 30 Apr 2015 13:35:55 GMT

So I added 2 scripts like below. One for NA and One for EU.

But after reload. I am not able to see the 2nd table for EU in the table viewer???

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://na.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

st,

co,

FROM 'LDAP://na.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

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

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

LET arg=peek('name');

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://eu.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

[EU Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

st,

co,

FROM 'LDAP://eu.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

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

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

LET arg=peek('name');

Re: Active Directory Query

quwok — Thu, 30 Apr 2015 13:45:57 GMT

Looking at your script, both tables have the same structure, QlikView might have concatenated both into a single table. If you like to load each source into their own table, try changing the column names on the second source.

Re: Active Directory Query

Anonymous — Thu, 30 Apr 2015 13:55:04 GMT

Thanks for the reply. Need your help here

I tried renaming the SN in the 2nd table as SN as 'ABCD' but I am getting error.

Can you please in renaming the column name in the 2nd table.

Re: Active Directory Query

quwok — Thu, 30 Apr 2015 14:13:06 GMT

You can use LOAD to rename the column, i.e.

[EU Users]:

LOAD

name as eu_name,

givenName as eu_givenName,

sn as eu_sn,

sAMAccountName as eu_sAMAccountName,

physicalDeliveryOfficeName as eu_physicalDeliveryOfficeName,

l as eu_l,

st as eu_st,

co as eu_co,

c as eu_c

;

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

st,

co,

FROM 'LDAP://eu.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

You can see in the above script that we have renamed the columns with a preceding "eu_", you can name them to anything you like. There are other ways such as using QUALIFY, but i think that might be a topic once you are more comfortable with QlikView scripting.

Re: Active Directory Query

Anonymous — Tue, 19 May 2015 09:04:36 GMT

Thanks Kwok. Your idea is working very well.

Re: Active Directory Query

Anonymous — Sat, 21 Nov 2015 01:27:28 GMT

Hi,

Do you need a special permission to query LDAP? When I tested the connection it gave me these messages.

"Test connection failed because of an error in setting the window handle property. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Continue with test connection?"

I clicked Yes and the second message came up.

"Test connection succeeded but some setting were not accepted by the provider."

It generated the connection string.

I issued the statement below and reloaded. I got a script error showing the statement below in the pop up.

SQL

select name

FROM 'ldap://dc-1.local';

Do I need to do anything different?

Thanks,

Stewart