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];
let valuecount = SubStringCount(values,',');
for i = 1 to valuecount + 1
let value = subfield(values,',', i) & '*';
sql select cn, sAMAccountName, displayName from 'LDAP://xx.yyyy.com' where objectClass = 'User' and objectCategory = 'Person' and sAMAccountName = '$(value)';
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.
This is great stuff but what if I want to make a narrow search to a specifik "OU" three levels down in the AD-tree?
Is it possible to have the exact path? What is the syntax? I don't know how to change the "From" statement in the SQL querying the AD. I would like to add several "OU=xxx", "OU=yyy" but it doesn't work.
Thank you for the script. I am getting the below error.
I am also getting error on lines 21,22,23.
Kindly advise. Thanks again.
OLEDB read failed
FROM 'LDAP://DC=EU, DC=CORP' WHERE objectCategory='person'
I have been able to query user anf group objects with the code from this thread.
How do I query security groups. The LDAP filter that to use to filter on security groups is
How do I specify this filter in the SQL query ?
Check out this sample. It does both Users and Groups and the links between them.
Qlikview Cookbook: Load Users and Groups from Active Directory http://qlikviewcookbook.com/recipes/download-info/load-users-and-groups-from-active-directory/
I also needed to add the 'order by name' to get all the entries.
Our AD has 20,000+ users and 20,000+ groups.
The load took longer than 1 hr and Qlikview ran out of memory when it tries to display the group members table.
Anyone has this problem ?
I am using the solution and it is working great.
One doubt. Like FROM 'LDAP://xx.yyyy.com' I have to query another LDAP as LDAP://yy.aaa.com
is it possible to include the LDAP://yy.aaa.com in the same script?