Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator
Creator

Hi Rob,

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

Highlighted
Not applicable

It beats me why

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

  AND name LIKE 'PREFIXOFGROUP%';

does not seem to work.

Highlighted
Not applicable

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

AND name = 'PREFIXOFGROUP*';

Highlighted
Contributor
Contributor

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.

Highlighted
Not applicable

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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.

Highlighted
Employee
Employee

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

Highlighted
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

Highlighted
Partner
Partner

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