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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Partner
Partner

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

Highlighted
Not applicable


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

Highlighted
MVP & Luminary
MVP & Luminary

@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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor II
Contributor II

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.

ww

Highlighted
Contributor II
Contributor II

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

ww

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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?