Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Connecting to and Querying Active Directory for Users

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

42 Replies
Anonymous
Not applicable
Author

Hi Rob,

I dont know how to include the 2nd LDAP source in the same script...

Not applicable
Author

It beats me why

FROM 'LDAP://$(RootDse)'  WHERE objectCategory='group'

  AND name LIKE 'PREFIXOFGROUP%';

does not seem to work.

Not applicable
Author

Ok, I figured it out. If anyone cares: use

AND name = 'PREFIXOFGROUP*';

pjtaylor
Contributor II
Contributor II

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.

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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.

Tyler_Waterfall
Employee
Employee

Thanks Rob! (I know - commenting on an old post again!)

bnichol
Specialist
Specialist

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

chrisg
Partner - Creator III
Partner - Creator III

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

Do or Do Not. There is no try!