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];
let valuecount = SubStringCount(values,',');
for i = 1 to valuecount + 1
let value = subfield(values,',', i) & '*';
sql select cn, sAMAccountName, displayName from 'LDAP://xx.yyyy.com' where objectClass = 'User' and objectCategory = 'Person' and sAMAccountName = '$(value)';
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.
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
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.
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.
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.
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?
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 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.
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
Thanks for any help.
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?