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
Hi Rob,
I dont know how to include the 2nd LDAP source in the same script...
It beats me why
FROM 'LDAP://$(RootDse)' WHERE objectCategory='group'
AND name LIKE 'PREFIXOFGROUP%';
does not seem to work.
Ok, I figured it out. If anyone cares: use
AND name = 'PREFIXOFGROUP*';
Just a heads up for anyone else, we came across this issue when we upgraded from 11.0 to 11.2. FIRST 500 fix seems to have worked.
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?
Daniel
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.
-Rob
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.
Thanks Rob! (I know - commenting on an old post again!)
I use the following logic to process the pwdlastset field to a date...
IF(pwdLastSet > 0, (date(left(pwdLastSet,11)/86400-109207,'YYYY-MM-DD')),'') as Last_Password_Reset
Hello,
we run Qlikview12.0.20400 SR5 - and i try to limit the datasets over FIRST 100.
We could not avoid the error.
Any hind for us?
Many thx
Christoph