43 Replies Latest reply: Sep 12, 2018 8:54 AM by Marcelo Gittermann 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) & '*';
      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.