Active Directory Query

[Anonymous]

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);

DO

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

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');

[quwok]

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

[Anonymous]

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);

DO

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

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);

DO

[EU Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

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');

[quwok]

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.

[Anonymous]

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.

[quwok · Accepted solution]

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

DO

[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,

l,

st,

co,

c

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.

[Anonymous]

Thanks Kwok. Your idea is working very well.

[Anonymous]

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