topic Re: Connecting to and Querying Active Directory for Users in QlikView

Connecting to and Querying Active Directory for Users

Tue, 03 Apr 2012 19:32:35 GMT

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

Connecting to and Querying Active Directory for Users

Anonymous — Tue, 03 Apr 2012 19:37:03 GMT

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!

Connecting to and Querying Active Directory for Users

Tue, 03 Apr 2012 19:57:05 GMT

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

Connecting to and Querying Active Directory for Users

Tue, 03 Apr 2012 21:05:09 GMT

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);

[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

Connecting to and Querying Active Directory for Users

rwunderlich — Tue, 03 Apr 2012 21:15:33 GMT

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

Connecting to and Querying Active Directory for Users

Anonymous — Tue, 03 Apr 2012 21:42:46 GMT

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

Connecting to and Querying Active Directory for Users

Wed, 04 Apr 2012 00:37:13 GMT

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.

Re: Connecting to and Querying Active Directory for Users

qlikviewforum — Mon, 22 Jul 2013 16:53:27 GMT

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

Re: Connecting to and Querying Active Directory for Users

rwunderlich — Tue, 23 Jul 2013 05:51:51 GMT

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

Re: Connecting to and Querying Active Directory for Users

qlikviewforum — Tue, 23 Jul 2013 12:16:35 GMT

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

Re: Connecting to and Querying Active Directory for Users

jeremy_fourman — Mon, 14 Oct 2013 15:46:23 GMT

Thanks for this application Rob! I am making use of it but there is one thing I am trying to figure out. I receive an

QVX_UNEXPECTED_END_OF_DATA

I say ok and the script continues to run pulling records. This happens more on the AD Users than AD Groups

I am trying to figure out what would cause it or what I need to try and bypass?

Thanks for any insight.

Jeremy

Re: Connecting to and Querying Active Directory for Users

rwunderlich — Mon, 14 Oct 2013 19:47:27 GMT

I'm seeing that error with a number of customers loading from SQL Server. This is the first I've heard of it with AD. My customers have not got resolution from QT Support. I suggest you report this error as well because I think it would be useful for support to understand the scope of this problem.

-Rob

Re: Connecting to and Querying Active Directory for Users

hopkinsc — Thu, 09 Jan 2014 12:36:30 GMT

Hi All,

Did anyone get a solution to this error? I too am getting the same error when extracting from the AD as Jeremy, and it continues to pull records after i click ok.

Thanks

Re: Connecting to and Querying Active Directory for Users

Wed, 22 Jan 2014 14:37:47 GMT

Hi Rob,

I am trying to query the AD with your Script but I always get following Error message:

Error Source: Active Directory, Error Msg:The specified domain either does not exist or could not be contacted

I definitely set up the correct AD domain - I can access it via AD Explorer but I am not able to query it with the SQL script. I need to load the members and their AD groups.

Any chance you can help me with this?

Thanks,

Marius

Re: Connecting to and Querying Active Directory for Users

rwunderlich — Fri, 24 Jan 2014 15:46:57 GMT

@hopkinsc

I got a report from someone else that they fixed the UNEXPECTED_END_OF_DATA error by un-commenting the FIRST 1000 script prefix and setting it to:

FIRST 100

-Rob

Re: Connecting to and Querying Active Directory for Users

rwunderlich — Fri, 24 Jan 2014 15:48:11 GMT

Marius,

I'm not sure where the error could be. Could it be authorization? Are you using the same userid in QV and AD Explorer?

-Rob

Re: Connecting to and Querying Active Directory for Users

wwistam — Fri, 27 Jun 2014 03:36:52 GMT

FIRST 500 seems to work for me. However I need to enter "FIRST 500" in three different places.

1. Users tab, Line 12 (above Load statement)

2. Groups tab, Line 12 (above Load statement)

3. GroupMembers, Line 11 (above Load statement)

Anyway, thanks Rob for the scripts, really appreciate you sharing it.

Re: Connecting to and Querying Active Directory for Users

wwistam — Fri, 27 Jun 2014 03:49:00 GMT

The UNEXPECTED_END_OF_DATA error seems to occur on Qlikview version 11. I can run it on version 9 with no problem.

Re: Connecting to and Querying Active Directory for Users

Anonymous — Wed, 24 Sep 2014 15:43:14 GMT

Hello,

This all has been very helpful, thank you!

One further question:

I'm trying to validate users memberships in groups.

Some of the groups are nested, How can I get the parents

groups etc...

Thanks for any help.

Tim

Re: Connecting to and Querying Active Directory for Users

Anonymous — Wed, 08 Oct 2014 23:38:25 GMT

I ran the script and got this error :

ErrorSource: Active Directory, ErrorMsg: A referral was returned from the server.

SQL select name, distinguishedName FROM 'LDAP://DC=autodesk, DC=com' WHERE objectCategory='person' AND name>' '

Ay idea what causes this error?