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
Found the source of the error. The LDAP address was incorrect. I have corrected that and the code now works !
Thanks
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.
Any suggestions?
Br
cris
I think you set the rootDSE like:
SET rootDSE = 'OU=xxx, DC=mycompany, Dc=net';
Not sure about the nested OUs though.
-Rob
Hi Rob,
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
SQL select
name, distinguishedName
FROM 'LDAP://DC=EU, DC=CORP' WHERE objectCategory='person'
AND name>''
Add "first 1000" prefix to your SQL like:
FIRST 1000 SQL SELECT ...
-Rob
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
(groupType:1.2.840.113556.1.4.803:=2147483648)
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/
-Rob
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 ?
Hi Guys,
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?
I don't know why you couldn't query two LDAP directories as long as you did one at a time.
-Rob