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.
Hi Rob, All,
For me loading 'TOP 999' did the trick together with adding a line 'Order BY name' (otherwise I missed a lot of records). What I noticed however that with each increment the loads get slower. So the first 999 records are loaded within 8 seconds; when reaching the 100,000 it takes almost 2 minutes. I'm wondering what is causing this. Dos anyone have an idea?
Mu guess is that the ADDSO filtering may not be very sophisticated. When you get the second and subsequent sets with a "where=" clause it's probably doing a relatively slow crawl to qualify rows, as opposed to using an index or something like that.
Has anyone every pulled in the pwdlastset field? When I pull it in I cannot get it to display as a date just a bunch of numbers. I have tried setting it as a timestamp, date and nothing seems to work.