Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
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
Contributor III
Contributor III

Found the source of the error.  The LDAP address was incorrect. I have corrected that and the code now works !

Thanks

Highlighted
Creator II
Creator II

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

Highlighted
MVP & Luminary
MVP & Luminary

I think you set the rootDSE like:

SET rootDSE = 'OU=xxx, DC=mycompany, Dc=net';

Not sure about the nested OUs though.

-Rob

Highlighted
Not applicable

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>''

Highlighted
MVP & Luminary
MVP & Luminary

Add "first 1000" prefix to your SQL like:

FIRST 1000 SQL SELECT ...

-Rob

http://masterssummit.com

http://robwunderlich.com

Highlighted
Contributor III
Contributor III

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 ?

Highlighted
MVP & Luminary
MVP & Luminary

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

http://masterssummit.com

http://robwunderlich.com

Highlighted
Contributor III
Contributor III

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 ?

Highlighted
Creator
Creator

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?

Highlighted
MVP & Luminary
MVP & Luminary

I don't know why you couldn't query two LDAP directories as long as you did one at a time.

-Rob