39 Replies Latest reply: Nov 9, 2016 9:10 AM by Christoph Goertz RSS

    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