Help in LDAP query

Last post 11-04-2009 5:17 AM by perumbully. 8 replies.

Sort Posts:

  • Help in LDAP query

    08-30-2004, 9:40 AM
    • Member
      208 point Member
    • nasirf
    • Member since 10-17-2003, 2:55 PM
    • Posts 39
    Hello,

    This is how i am making LDAP queries. (I do not want to use DirectoryEntry and DirectorySearch since it is very slow in retrieving large number of records.)

    OleDbConnection ADConnection = new OleDbConnection("Provider=ADsDSOObject;");
    ADConnection.Open();

    string strTest = "<LDAP://corp.epec.com>;(&(objectCategory=group)(sAMAccountname=B*));distinguishedName,description,objectGUID;subTree";


    OleDbCommand MyCommand = new OleDbCommand(strTest ADConnection);
    OleDbDataReader GroupReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

    1. I only wants to retrieve records from EPGroup Organizational Unit. What should i change in my query?

    2. Is there anyway i can use range clause in my query? Right now i am using * wild card to overcome 1000 records limit. (I do not want to use Directory entry and DirectorySearch).

    Thanks in advance,

    Nasir

  • Re: Help in LDAP query

    08-30-2004, 2:48 PM
    • Star
      9,098 point Star
    • dunnry
    • Member since 06-24-2002, 4:17 PM
    • http://directoryprogramming.net
    • Posts 1,806
    1. Your query does not contain where it should search. Instead that is the job of your binding entity. In this case, you would need to change your LDAP:// string to bind lower on your directory tree - specifically, bind using somthing like "LDAP://OU=EP Group, DC=corp,DC=epec,DC=com".

    2. The method you choose to search with needs to support paging. DirectorySearcher has this support buillt-in. The * character has nothing to do with paging. If your search returns more than 1000 entries then they will be truncated at 1000 by default without paging.

    Finally, I am not sure why you would think that DirectorEntry and DirectorySearcher are slower than using ADsDSOObject provider. I believe under the covers, they are both resolving down to the native ADSI layer, so there should be no speed difference. I have used DirectorySearcher many times successfully and very quickly. I think your issue likely has more to do with the specific query rather than the underlying technique.

    Do you have any performance tests that you can refer to to show that ADO.NET is faster than using DirectoryEntry/Searcher ?
  • Re: Help in LDAP query

    08-30-2004, 5:23 PM
    • Member
      208 point Member
    • nasirf
    • Member since 10-17-2003, 2:55 PM
    • Posts 39
    Thanks for your reply Ryan.


    I do not know much about active directory or LDAP. I am working on it on and off for about two weeks. This is the extent of my knowledge about LDAP and active directory. Is there any way i can use range with my current code. (we can remove the wild card part.)

    1. Based on your input I change the the string. Would you please correct this? :-)

    string strTest = "<LDAP://OU=EPGroup,DC=corp,DC=epec,DC=com>;(&(objectCategory=group)(sAMAccountname=B*));distinguishedName,description,objectGUID;subTree";

    This is the erorr I am getting.

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

    Additional information: No error information available: DB_E_NOTABLE(0x80040E37).

    2. Since i was not able to use paging correctly this was one other way i was able to create batches. I have functions that increment the A, B, C and retrieve records that starts with these char. If there are more then 1000 records stated with A it will do AA, AB, AC... onword. This is just a way to get the desired result. I know i need to use range clause so i can get rid of wild card and that was my question.

    I need to retrieve about 10000 reords after a certain frequency. I tried to retrieve about 1000 reocrds from the Active directory it took about 23 minutes. I tried the same number of records from link server it took about 1 minute and 4 seconds. When i used oleDb form C# it was almost the same.

    I noticed that if you do DirectorySearch it retrieve lots of properties even i am using
    search.PropertiesToLoad.Add("description"); ....
    I think this is the reason it is very slow. But again i donot have much information about this subject.

    To test the performance you can just retrieve 1000 reords and print them to see how much time is it taking.

    Any help will be appreciated.

    Thanks,
    Nasir



  • Re: Help in LDAP query

    08-31-2004, 11:59 AM
    • Star
      9,098 point Star
    • dunnry
    • Member since 06-24-2002, 4:17 PM
    • http://directoryprogramming.net
    • Posts 1,806
    Some questions for you:

    1. What does your directory structure look like? Is 'corp' the name of a domain, or the name of a domain controller you are using? Is the OU EPGroup located directly under the domain or nested further?

    2. Are all the groups located in the OU 'EPGroup'? Any further nesting of OU's under this one? Does the OU only contain groups or does it have other objects in it (like users)?

    3. How many groups are you trying to retrieve here?

    4. What information are you really trying to get from the Groups? Just the name? The members?

  • Re: Help in LDAP query

    08-31-2004, 3:42 PM
    • Member
      208 point Member
    • nasirf
    • Member since 10-17-2003, 2:55 PM
    • Posts 39
    Thanks Ryan for your response. below you will find answer to your questions

    1. What does your directory structure look like? Is 'corp' the name of a domain, or the name of a domain controller you are using? Is the OU EPGroup located directly under the domain or nested further?

    corp is the name of the domain.
    corp.epec.com is fully qualified domain name.
    OU EPGroup is located directly under the domain (Top level)

    2. Are all the groups located in the OU 'EPGroup'? Any further nesting of OU's under this one? Does the OU only contain groups or does it have other objects in it (like users)?

    I only need to search in OU 'EPGroup'
    It only contains groups
    For users we have a different OU EPUsers which is also directly under domain (Top level)

    3.How many groups are you trying to retrieve here?

    There are about 8000 groups in EPGroups. On the first load i will retrieve all of them. after that i use a process in window service to frequently check for whenchanged property value of groups and retrieve only those which are changed. The numbers may range from 1000 to 3000. when i retrieve member it will be a lot more.

    4. What information are you really trying to get from the Groups? Just the name? The members?

    I am retrieving
    sAMAccountName,
    objectGUID,
    type,
    distinguishedname,
    description,
    whenchanged,
    whencreated

    I have some important information in the Description field. so it is also very important.
    In another process i am retrieving
    objectGUID,description,member
    Since we have about 160000 members of these groups the initial load takes about 7 minutes to retrieve data from AD and update the database.

    Again thanks again for your response and your help.

    Nasir



  • Re: Help in LDAP query

    08-31-2004, 6:01 PM
    • Star
      9,098 point Star
    • dunnry
    • Member since 06-24-2002, 4:17 PM
    • http://directoryprogramming.net
    • Posts 1,806
    Well, I decided to try a test program and my results are considerably different than yours. I also added in the overhead of converting this to a DataTable and not just a collection of SearchResults. I only have a domain with 2247 groups, but this program returned within 3 seconds:
    using System;
    
    using System.Data;
    using System.DirectoryServices;

    namespace GroupGrabber
    {
    class Invoker
    {
    [STAThread]
    static void Main(string[] args)
    {
    if (args.Length != 3)
    {
    throw new ArgumentException("Invalid Arguments - use path, username, password");
    }

    string path = args[0];
    string username = args[1];
    string password = args[2];

    GroupGrabber gg = new GroupGrabber(path, username, password);

    try
    {
    // DataSet ds = new DataSet();
    // ds.Tables.Add(gg.GetGroups("(objectCategory=group)", new string[]{"description", "sAMAccountName"}));
    // ds.WriteXml(Console.Out);

    Console.WriteLine(gg.GetGroups("(objectCategory=group)", new string[]{"description", "sAMAccountName"}).Rows.Count);
    }
    catch(Exception ex)
    {
    Console.WriteLine(ex.ToString());
    }
    Console.WriteLine("Press Enter to Continue");
    Console.ReadLine();
    }
    }

    public class GroupGrabber
    {
    private string _adsPath;
    private DirectoryEntry _de;

    public GroupGrabber(string adsPath): this(adsPath, null, null)
    {
    }

    public GroupGrabber(string adsPath, string username, string password)
    {
    _adsPath = adsPath;
    _de = new DirectoryEntry(_adsPath, username, password, AuthenticationTypes.Secure);
    }

    public DataTable GetGroups(string ldapQuery, string[] properties)
    {
    //build a datatable, but you could really use anything here
    DataTable dt = new DataTable();

    foreach (string s in properties)
    {
    dt.Columns.Add(new DataColumn(s, typeof(string)));
    }

    //create our searchers
    DirectorySearcher ds = new DirectorySearcher(_de, ldapQuery, properties);
    SearchResultCollection src = null;

    //set paging
    ds.PageSize = 1000;

    try
    {
    src = ds.FindAll();

    foreach (SearchResult sr in src)
    {
    foreach (string s in properties)
    {
    if(sr.Properties.Contains(s))
    {
    DataRow dr = dt.NewRow();
    dr[s] = sr.Properties[s][0].ToString();
    dt.Rows.Add(dr);
    }
    }
    }
    }
    finally
    {
    if(src != null)
    src.Dispose();

    ds.Dispose();
    }

    return dt;
    }


    }
    }
    Sample command line: GroupGrabber.exe "LDAP://server:389/OU=SomeOU,DC=domain,DC=com" "username" "password"

    Just take the whole thing and drop it into a Console application. You can either hardcode or modify the Invoker class for your particulars. I passed in the entire domain and it was very quick. Originally, I had thought about not using the DirectorySearcher and binding directly to the OU and then accessing the Child collection. But, I think the DirectorySearcher is the most efficient.
  • Re: Help in LDAP query

    09-01-2004, 3:40 PM
    • Member
      208 point Member
    • nasirf
    • Member since 10-17-2003, 2:55 PM
    • Posts 39
    Thanks Ryan,

    It worked with almost the same speed as link server solution.

    Thanks again for solving this big problem.

    Nasir Faruqi
  • Re: Help in LDAP query

    05-24-2009, 3:03 PM

    Hi Guys,

     I have a piece of code (below) which grabs a list of users on an Active Directory. However it only seems to return the only1500 users. Does anybody know how to modify this code so it returns all the users?

    1    
    2    public ArrayList GetActiveADUsersInGroup()
    3    {
    4    ArrayList adUsersInGroup = new ArrayList();
    5    String queryString = "LDAP://CN=ALL Employees,OU=Distribution Lists,OU=Exchange Objects,OU=System Management,DC=xxx,DC=corp,DC=local";
    6    DirectoryEntry de = new DirectoryEntry(queryString);
    7    
    8    
    9    PropertyValueCollection members = (PropertyValueCollection)de.Properties["member"];
    10   
    11   foreach (object member in members)
    12   {
    13   String queryStringMember = "LDAP://" + member.ToString();
    14   DirectoryEntry deMember = new DirectoryEntry(queryStringMember);
    15   String windowsIdentityName = deMember.Properties["sAMAccountName"].Value.ToString();
    16   
    17   adUsersInGroup.Add(windowsIdentityName);
    18   }
    19   return adUsersInGroup;
    20   }
    21   
       
    

    Thanks in advance
    Srinu

     

  • Re: Help in LDAP query

    11-04-2009, 5:17 AM
    • Member
      2 point Member
    • perumbully
    • Member since 11-04-2009, 5:14 AM
    • Posts 1

    EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'suman.p@mydomain.com',@rmtpassword='xxxxxxx'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tf_GetAllUsersFromActiveDirectory]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[tf_GetAllUsersFromActiveDirectory]

    GO
    CREATE FUNCTION [dbo].[tf_GetAllUsersFromActiveDirectory]
    ()
    RETURNS
      @USERS TABLE
      ( 
         sAMAccountName VARCHAR(25)    PRIMARY KEY CLUSTERED    
       , givenName VARCHAR(200)
       , SN VARCHAR(200)
       , userAccountControl VARBINARY(8)
       , mail VARCHAR(200)
     
      )
    AS
    BEGIN

     

    INSERT INTO @Users
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=A*)(sAMAccountName=B*)(sAMAccountName=C*)(sAMAccountName=D" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=A*)(sAMAccountName=B*)(sAMAccountName=C*)(sAMAccountName=D">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=A*)(sAMAccountName=B*)(sAMAccountName=C*)(sAMAccountName=D*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=E*)(sAMAccountName=F*)(sAMAccountName=G*)(sAMAccountName=H" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=E*)(sAMAccountName=F*)(sAMAccountName=G*)(sAMAccountName=H">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=E*)(sAMAccountName=F*)(sAMAccountName=G*)(sAMAccountName=H*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=I*)(sAMAccountName=J*)(sAMAccountName=K*)(sAMAccountName=L" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=I*)(sAMAccountName=J*)(sAMAccountName=K*)(sAMAccountName=L">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=I*)(sAMAccountName=J*)(sAMAccountName=K*)(sAMAccountName=L*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=M*)(sAMAccountName=N*)(sAMAccountName=O*)(sAMAccountName=P" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=M*)(sAMAccountName=N*)(sAMAccountName=O*)(sAMAccountName=P">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=M*)(sAMAccountName=N*)(sAMAccountName=O*)(sAMAccountName=P*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=Q*)(sAMAccountName=R*)(sAMAccountName=S*)(sAMAccountName=T" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Q*)(sAMAccountName=R*)(sAMAccountName=S*)(sAMAccountName=T">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Q*)(sAMAccountName=R*)(sAMAccountName=S*)(sAMAccountName=T*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=U*)(sAMAccountName=V*)(sAMAccountName=W*)(sAMAccountName=X" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=U*)(sAMAccountName=V*)(sAMAccountName=W*)(sAMAccountName=X">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=U*)(sAMAccountName=V*)(sAMAccountName=W*)(sAMAccountName=X*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
    UNION ALL
    SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<;(&(objectClass=User)(|(sAMAccountName=Y*)(sAMAccountName=Z" mce_href="ldap://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Y*)(sAMAccountName=Z">LDAP://MyDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Y*)(sAMAccountName=Z*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')

     

    RETURN

     

    END

    GO

     

     

     

    Use the above script to create a UDF and call it from a Stored Procedure

Page 1 of 1 (9 items)