Connecting to and Querying Active Directory for Users

[#1]

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

[Anonymous]

This was actually in the Community a few years back but was seemly lost when they re-worked the message boards. So thanks for contributing this!

[#3]

I found another snippet that used a DO/LOOP after I posted my message.  When I tried that code I got a lot less records returned.  I think there's a bad assumption in the order that records are being returned.

I got the idea from here:

http://www.bravesoft.com/blog/?p=24

[#4 · Accepted solution]

So I modified the DO/LOOP code so now it works and returns a more complete list even than the previous code.

LET arg=chr(01);

DO

[nameTable]:

SQL select name, sAMAccountName FROM 'LDAP://xx.yyyy.com' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

EXIT DO WHEN NoOfRows('nameTable') = 0;

EXIT DO WHEN peek('name') = '$(arg)'; 

LET arg=peek('name');

LOOP;

I like this code better now because it doesn't suffer the same limitations.  Never leave well enough alone!

Russ

[rwunderlich]

The above code snippet is from the QV Cookbook available here:

http://robwunderlich.com/downloads/

When searching for code, don't forget to consider the Cookbook.

-Rob

http://robwunderlich.com

http://qlikviewcomponents.org

[Anonymous]

Thanks Rob. Your code in the Cookbook is probably what I remember seeing.

[#7]

bgarland wrote:

Thanks Rob. Your code in the Cookbook is probably what I remember seeing.

Thanks Rob.  I pulled it from a post that came up after I posted mine, from you of course!

The 'order by name' was the secret sauce for me that got it to work properly.  I should have iterated through to see why it was dropping users.

[qlikviewforum]

Hi,

The QVW which tries to load the data from LDAP loads only the partial data frequently. We are unable to find the root cause of the issue as the QVW doesn't fails but it loads only the partial data.Can someone please help us to identify the root cause of the issue. It has been happening from quite a long time but we didn't succeed in finding the root cause of the issue. Hope someone can help us for the same.

Regards,

qvforum

[rwunderlich]

Attached is my latest script for loading Active Directory. It does not use a macro, and therefore does not require System Access and will work in a server reload.

Note the comment on line 12 of the "Users" script tab. At some installations, I've received an "undefined SQL Error" message and fixed it by uncommenting this line. I've have not figured out the cause,

-Rob

http://masterssummit.com

http://robwunderlich.com

[qlikviewforum]

Hi,

I will try to modify the code so that it fits for my requirement and will keep you updated on that. I hope if I successfuly managed to understand the code  and do the required change then it should retrieve the data from LDAP/AD without any problem.

Regards,

qvforum