Bindling User details from AD using LDAP

Question

I have got a request from a client to fetch all client user details from AD and finally dump to a db so that they can use it for reporting. I have used DirectoryEntry and PrincipalContext class to retrieve all the information.The user volume is approx. 7500 and for each user I am binding 35 AD attributes in a list of object.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;
using System.IO;
using AccountCheck;
using Microsoft.ActiveDirectory.Management.Commands;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace testproj1
{
    class Class1
    {
        //private static DateTime LonTS1;

        //static void Main(string[] args)

        // public static int LoadADUsers()
        static void Main(string[] args)
        {

            int userCount = 0;
            int maxPasswordAge = 90;
            string LDAP_QUERY = "LDAP://DC=xyz,DC=com";
            string LDAP_FILTER = "(&(objectClass=user)(objectCategory=person))";
            //string LDAP_FILTER = "(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))";


            DirectoryEntry objDirEntry = new DirectoryEntry(LDAP_QUERY);
            string[] aryPropertiesToRetrieve =
            {"sAMAccountName","Company","whenCreated","department","description","Enabled","displayName","distinguishedName","mail","employeeID","accountExpires", "extensionAttribute11", "extensionAttribute12",
             "extensionAttribute13", "extensionAttribute14","extensionAttribute7", "extensionAttribute9","givenName" ,"Initials","title","location","sn","LastLogoff","LastLogon","manager","ChangePasswordAtLogon",
             "physicalDeliveryOfficeName", "pwdLastSet","PasswordNeverExpires","PasswordNotRequired","nTSecurityDescriptor","ProtectedFromAccidentalDeletion","usercannotchangepassword","userAccountControl","userPrincipalName","lastlogontimestamp",
            };
            List<string> adPropertyList = new List<string>(aryPropertiesToRetrieve);
            DirectorySearcher objSearch = new DirectorySearcher(objDirEntry, LDAP_FILTER, aryPropertiesToRetrieve);
            objSearch.Asynchronous = true;
            objSearch.PageSize = 500;
            objSearch.SizeLimit = 1000;
            objSearch.SearchScope = SearchScope.Subtree;
            SearchResultCollection objResults = objSearch.FindAll();
            User adUser = new User();
            List<User> allUsers = new List<User>();
            //NewADObjectParameterSet na = new NewADObjectParameterSet();
            //string SQL = string.Empty;
            int userAccountControl = 0;
            DateTime accountExpiration = DateTime.Now;
            //DateTime? accountExpiration;
            DateTime passwordLastSet = DateTime.Now;
            int daysUntilPasswordExpiration = 0;
            DateTime passwordExpiration = DateTime.Now;
            int daysUntilAccountExpiration = 0;
            double passwordAge = 0;
            DateTime? LLon=null;
            string Mgr;
            bool MCPANL;
            string pfcd;
            foreach (SearchResult result in objResults)
            {
                PrincipalContext context = new PrincipalContext(ContextType.Domain);
                UserPrincipal user = UserPrincipal.FindByIdentity(context, IdentityType.SamAccountName, GetPropertyValue(result, "sAMAccountName").ToString());

                //accountExpiration = (DateTime)GetPropertyValue_name(result, "accountExpires", "displayName", "sAMAccountName");
                //accountExpiration = (DateTime)GetPropertyValue(result, "accountExpires");
                accountExpiration = (DateTime)GetPropertyValue(result, "accountExpires");
                //accountExpiration = user.AccountExpirationDate.HasValue ? (DateTime)user.AccountExpirationDate : (DateTime?)null;
                daysUntilAccountExpiration = accountExpiration.Subtract(DateTime.Now).Days;
                userAccountControl = (int)GetPropertyValue(result, "userAccountControl");
                passwordLastSet = (DateTime)GetPropertyValue(result, "pwdLastSet");
                pfcd = (string)GetPropertyValue(result, "ProtectedFromAccidentalDeletion");
                if (passwordLastSet == null)
                { MCPANL = true; }
                else { MCPANL = false; }
                Mgr = GetPropertyValue(result, "manager").ToString();
                if (Mgr == "" || Mgr == null)
                { Mgr = ""; }
                else
                { Mgr = Mgr.Substring(3, Mgr.IndexOf(",") - 3); }
                //LLoff = (string)GetPropertyValue(result, "LastLogoff");     
               // LLon = user.LastLogon.HasValue ? (DateTime)user.LastLogon : (DateTime?) null;
                LLon = user.LastLogon;
                if (userAccountControl > 10000) //password never expires
                {
                    daysUntilPasswordExpiration = daysUntilAccountExpiration;
                    passwordExpiration = accountExpiration;
                }
                else
                {
                    passwordAge = DateTime.Now.Subtract(passwordLastSet).TotalDays;
                    daysUntilPasswordExpiration = maxPasswordAge - (int)Math.Round(passwordAge + 1);
                    passwordExpiration = DateTime.Now.AddDays(daysUntilPasswordExpiration);
                }

                adUser = new User()
                {
                    Name = GetPropertyValue(result, "sAMAccountName").ToString(),
                    Company = GetPropertyValue(result, "Company").ToString(),
                    Creation_Date = GetPropertyValue(result, "whenCreated").ToString(),
                    Department = GetPropertyValue(result, "department").ToString(),
                    Description = GetPropertyValue(result, "description").ToString(),
                    Enabled = user.Enabled,
                    Display_Name = GetPropertyValue(result, "displayName").ToString(),
                    Distinguished_Name = GetPropertyValue(result, "distinguishedName").ToString(),
                    Email = GetPropertyValue(result, "mail").ToString(),
                    EmployeeID = GetPropertyValue(result, "employeeID").ToString(),
                    Expiration_Date = accountExpiration,
                    extensionAttribute11 = GetPropertyValue(result, "extensionAttribute11").ToString(),
                    extensionAttribute12 = GetPropertyValue(result, "extensionAttribute12").ToString(),
                    extensionAttribute13_Room = GetPropertyValue(result, "extensionAttribute13").ToString(),
                    extensionAttribute14_Ext = GetPropertyValue(result, "extensionAttribute14").ToString(),
                    extensionAttribute7_IAM_ID = GetPropertyValue(result, "extensionAttribute7").ToString(),
                    extensionAttribute9_CostCenter = GetPropertyValue(result, "extensionAttribute9").ToString(),
                    First_Name = GetPropertyValue(result, "givenName").ToString(),
                    Initials = GetPropertyValue(result, "Initials").ToString(),
                    Job_Title = GetPropertyValue(result, "title").ToString(),
                    //Last_Known_Location = GetPropertyValue(result, "location").ToString(),
                    Last_Name = GetPropertyValue(result, "sn").ToString(),
                    // lastLogoff = new DateTime(1601, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddTicks(LLoff),
                    //lastLogoff=
                    lastLogon = LLon,
                    Manager = Mgr,
                    Must_Change_Password_At_Next_Logon = MCPANL,
                    Office = GetPropertyValue(result, "physicalDeliveryOfficeName").ToString(),
                    Password_Age_In_Days = passwordAge,
                    Password_Expiration_Date = passwordExpiration,
                    Password_Last_Changed = passwordLastSet,
                    Password_Never_Expire = user.PasswordNeverExpires,
                    Password_Not_Required = user.PasswordNotRequired,
                    physicalDeliveryOfficeName = GetPropertyValue(result, "physicalDeliveryOfficeName").ToString(),
                    //Protected_From_Accidental_Deletion = GetPropertyValue(result, "ProtectedFromAccidentalDeletion").ToString(),
                    User_Cannot_Change_Password = user.UserCannotChangePassword,
                    userAccountControl = GetPropertyValue(result, "userAccountControl").ToString(),
                    Username = GetPropertyValue(result, "userPrincipalName").ToString(),
                    Username_pre_2000 = GetPropertyValue(result, "sAMAccountName").ToString(),
                    //lastLogon_value= LLonTS1
                };

                allUsers.Add(adUser);

                userCount++;
                //Console.WriteLine("the count is" + userCount);
                // Console.ReadLine();



            } // end foreach SearchResult loop

            string connectionstring = ConfigurationManager.ConnectionStrings["LDAP_ALLUSER"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {


                conn.Open();
                string query = "truncate table [dbo].[adlook] ";
                SqlCommand cmd1 = new SqlCommand(query, conn);
                cmd1.ExecuteNonQuery();
                SqlCommand cmd =
               new SqlCommand(
                   "INSERT INTO [dbo].[ADlook] (name, company, creation_date,department,description,enabled,display_name,distinguished_name,email,employeeid,expiration_date,extensionattribute11,extensionattribute12,extensionattribute13_room,extensionattribute14_ext,extensionattribute7_iam_id,extensionattribute9_costcenter,first_name,initials,job_title,last_name,lastlogon,manager,must_change_password_at_next_logon,office,password_age_in_days,password_expiration_date,password_last_changed,password_never_expire,password_not_required,physicaldeliveryofficename,user_cannot_change_password,useraccountcontrol,username,username_pre_2000) " +
                   " VALUES (@name,@company,@creation_date,@department,@description,@enabled,@display_name,@distinguished_name,@email,@employeeid,@expiration_date,@extensionattribute11,@extensionattribute12,@extensionattribute13_room,@extensionattribute14_ext,@extensionattribute7_iam_id,@extensionattribute9_costcenter,@first_name,@initials,@job_title,@last_name,@lastlogon,@manager,@must_change_password_at_next_logon,@office,@password_age_in_days,@password_expiration_date,@password_last_changed,@password_never_expire,@password_not_required,@physicaldeliveryofficename,@user_cannot_change_password,@useraccountcontrol,@username,@username_pre_2000)");
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.Parameters.Add("@name",DbType.String); cmd.Parameters.Add("@company",DbType.String); cmd.Parameters.Add("@creation_date",DbType.DateTime); cmd.Parameters.Add("@department",DbType.String); cmd.Parameters.Add("@description",DbType.String); cmd.Parameters.Add("@enabled",DbType.Boolean);
                cmd.Parameters.Add("@display_name",DbType.String); cmd.Parameters.Add("@distinguished_name",DbType.String); cmd.Parameters.Add("@email",DbType.String); cmd.Parameters.Add("@employeeid",DbType.String); cmd.Parameters.Add("@expiration_date",DbType.DateTime); cmd.Parameters.Add("@extensionattribute11",DbType.String);
                cmd.Parameters.Add("@extensionattribute12",DbType.String); cmd.Parameters.Add("@extensionattribute13_room",DbType.String); cmd.Parameters.Add("@extensionattribute14_ext",DbType.String); cmd.Parameters.Add("@extensionattribute7_iam_id",DbType.String); cmd.Parameters.Add("@extensionattribute9_costcenter",DbType.String); cmd.Parameters.Add("@first_name",DbType.String);
                cmd.Parameters.Add("@initials",DbType.String); cmd.Parameters.Add("@job_title",DbType.String); cmd.Parameters.Add("@last_name",DbType.String); cmd.Parameters.Add("@lastlogon",DbType.DateTime); cmd.Parameters.Add("@manager",DbType.String); cmd.Parameters.Add("@must_change_password_at_next_logon",DbType.Boolean);
                cmd.Parameters.Add("@office",DbType.String); cmd.Parameters.Add("@password_age_in_days",DbType.Int32); cmd.Parameters.Add("@password_expiration_date",DbType.DateTime); cmd.Parameters.Add("@password_last_changed",DbType.DateTime); cmd.Parameters.Add("@password_never_expire",DbType.Boolean); cmd.Parameters.Add("@password_not_required",DbType.Boolean);
                cmd.Parameters.Add("@physicaldeliveryofficename",DbType.String); cmd.Parameters.Add("@user_cannot_change_password",DbType.Boolean); cmd.Parameters.Add("@useraccountcontrol",DbType.String); cmd.Parameters.Add("@username",DbType.String); cmd.Parameters.Add("@username_pre_2000",DbType.String);

                foreach (var item in allUsers)
                {
                    cmd.Parameters[0].Value = item.Name; cmd.Parameters[1].Value = item.Company; cmd.Parameters[2].Value = item.Creation_Date; cmd.Parameters[3].Value = item.Department; cmd.Parameters[4].Value = item.Description;
                    cmd.Parameters[5].Value = item.Enabled; cmd.Parameters[6].Value = item.Display_Name; cmd.Parameters[7].Value = item.Distinguished_Name; cmd.Parameters[8].Value = item.Email; cmd.Parameters[9].Value = item.EmployeeID;
                    cmd.Parameters[10].Value = item.Expiration_Date; cmd.Parameters[11].Value = item.extensionAttribute11; cmd.Parameters[12].Value = item.extensionAttribute12; cmd.Parameters[13].Value = item.extensionAttribute13_Room; cmd.Parameters[14].Value = item.extensionAttribute14_Ext;
                    cmd.Parameters[15].Value = item.extensionAttribute7_IAM_ID; cmd.Parameters[16].Value = item.extensionAttribute9_CostCenter; cmd.Parameters[17].Value = item.First_Name; cmd.Parameters[18].Value = item.Initials; cmd.Parameters[19].Value = item.Job_Title;
                    cmd.Parameters[20].Value = item.Last_Name;cmd.Parameters[21].Value = (object)item.lastLogon ?? DBNull.Value;cmd.Parameters[22].Value = item.Manager; cmd.Parameters[23].Value = item.Must_Change_Password_At_Next_Logon; cmd.Parameters[24].Value = item.Office;
                    cmd.Parameters[25].Value = item.Password_Age_In_Days; cmd.Parameters[26].Value = item.Password_Expiration_Date; cmd.Parameters[27].Value = item.Password_Last_Changed; cmd.Parameters[28].Value = item.Password_Never_Expire; cmd.Parameters[29].Value = item.Password_Not_Required;
                    cmd.Parameters[30].Value = item.physicalDeliveryOfficeName; cmd.Parameters[31].Value = item.User_Cannot_Change_Password; cmd.Parameters[32].Value = item.userAccountControl; cmd.Parameters[33].Value = item.Username; cmd.Parameters[34].Value = item.Username_pre_2000;


                    cmd.ExecuteNonQuery();
                }

                conn.Close();

            }







        }


        ///////////////////////////////////////// 1st method starts
        private static object GetPropertyValue(SearchResult result, string propertyName)
        {
            object propValue = null;


            if (result.Properties.Contains(propertyName))
            {
                if (result.Properties[propertyName].Count > 0)
                {
                    propValue = result.Properties[propertyName][0];

                    if (propertyName == "accountExpires" || propertyName == "pwdLastSet")
                    {
                        long dateValue = (long)propValue;
                        long maxDate = DateTime.MaxValue.ToFileTime();

                        if (dateValue == 0 || dateValue > maxDate || dateValue == null) //never expires
                        {
                            propValue = Convert.ToDateTime("12/31/2100"); //new DateTime(2100, 12, 31);
                        }
                        else //expires
                        {
                            propValue = DateTime.FromFileTime(dateValue);
                        }
                    }
                }
            }
            else
            {
                propValue = string.Empty;

            }

            return propValue;
        }
        ////////////////////////////// 1st method ends
    }

}

There are no errors as of now and it is just code that takes long to retrieve information. The code iterates each user and adds the to the object allUsers. It takes approximately 13 mins to add all users to this object which seems like a long time and will appreciate if someone can help me if I am doing anything wrong in coding part.

I have written a User class where all the property definitions are there and connection string to dump into db is being inherited from App.Config.

In the below code allUsers.Add(adUser); is the part where it will add user details one by one, until all users (almost 7500) are added. This takes at-least 14 mins of time.

Please help so that I can do any changes to code to make this process faster and I can add all user data in the object faster.

I have rolled back your last edit. Please see What should I do when someone answers my question?: Do not add an improved version of the code after receiving an answer. Including revised versions of the code makes the question confusing, especially if someone later reviews the newer code. — t3chb0t, May 24, 2019 at 16:24

dfhwze · Accepted Answer · 2019-05-24 16:28:53Z

General Guidelines

Please put any technical restrictions in the initial post. Like, for instance, framework 3.5 :-(
Declare variables as var rather than their type for readability.
Split big methods into smaller ones for readability and testibility.

Performance

Your biggest bottleneck is the sequential loop of 7000 users. You could use the TPL to maximize performance.

 foreach (SearchResult result in objResults)
 {
    // your code ..
    // var adUser = new User(// your code..
    allUsers.Add(adUser);
 }

tasks run in a different synchronization context. By default, the .NET ThreadPool is used to dispatch your 7000 operations.

 var allUsers = new ConcurrentBag<User>();
 var tasks = objResults.OfType<SearchResult>().Select(item => Task.Factory.StartNew((arg) =>
    {
        var objResult = (SearchResult)arg;
        // .. your code
        var adUser = new User(// your code..
        allUsers.Add(adUser);

 }, item)).ToArray();

 Task.WaitAll(tasks);

 var faultedTasks = tasks.Where(x => x.IsFaulted);
 //Log(faultedTasks); // if you decide to do something when certain tasks failed

Explanation

Since we now work with multiple threads, a synchronized collection should be used to store your users.

var allUsers = new ConcurrentBag<User>();

The foreach is replaced with a TPL factory method. Each operation gets queued to a background thread and will run when scheduled. Since we run over a collection, it is best practise to marshal the elements item to the operation arg.

var tasks = objResults.OfType<SearchResult>().Select(item => Task.Factory.StartNew((arg) =>
   {
       // ..
   }, item)).ToArray();

You can await all the operations to finish. This could be extended to use a timeout and CancellationToken. I leave this augmentation up to you to discover :-)

Task.WaitAll(tasks);

Once all operations have finished, you can obtain the users from your bag and continue processing. Note that you could decide to log any operations that did not succeed, or even propagate any error up the stack.

var faultedTasks = tasks.Where(x => x.IsFaulted);
//Log(faultedTasks); // if you decide to do something when certain tasks failed

Proposed Solution Framework 3.5

Use a combination of ThreadPool and WaitHandle instead of Task. Rather than waiting on tasks to complete, you now wait on the wait handles to complete. You should add in your own exception handling though.

var allUsers = new List<User>();
const int size = 64;
var population = objResults.OfType<SearchResult>();
for (var j = 0; j < population.Count(); j += size) {
    var samples = population.Skip(j).Take(size).ToArray();
    var waitHandles = samples.Select(item => new ManualResetEvent(false)).ToArray();
    for (var k = 0; k < samples.Count(); k++) {
         ThreadPool.QueueUserWorkItem((arg) => 
         {
             var kvp = (KeyValuePair<SearchResult, int>)arg;
             var objResult = kvp.Key;
             var index = kvp.Value;

             // your code ..
             var adUser = new User(); // your code ..
             lock (allUsers) {
                 allUsers.Add(adUser);
                 waitHandles[index].Set();
             }

         }, new KeyValuePair<SearchResult, int>(samples[k], k));
    }
    WaitHandle.WaitAll(waitHandles);
}

thank you for your valuable add on . However, the sad part is i am using .net framework v3.5 which doesn't accept this reference to be added as dll. — TheSacredKiller, May 24, 2019 at 12:26
@TheSacredKiller this can be rewritten in v2.0 using docs.microsoft.com/en-us/dotnet/api/…. It would have been good to know your limitation to v3.5 :-) — dfhwze, May 24, 2019 at 12:28
thank you ... i am really new to this thing and i am getting a lot of error while doing changes for your code.could you please help fitting my code to right places in urs? — TheSacredKiller, May 24, 2019 at 13:23
@TheSacredKiller yes I will post a new answer that is v3.5 compliant. — dfhwze, May 24, 2019 at 14:17
'var waitHandles = objResults.Select(item => new ManualResetEvent(false)).ToArray(); objResults.Select((item, i) => ThreadPool.QueueUserWorkItem((arg) =>' — TheSacredKiller, May 24, 2019 at 15:27

Stack Exchange Network

Bindling User details from AD using LDAP

1 Answer 1

General Guidelines

Performance

Explanation

Proposed Solution Framework 3.5

Your Answer

Not the answer you're looking for? Browse other questions tagged
c#
performance
active-directory
ldap
or ask your own question.

Hot Network Questions

Bindling User details from AD using LDAP

1 Answer 1

General Guidelines

Performance

Explanation

Proposed Solution Framework 3.5

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged c#performanceactive-directoryldap or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
c#
performance
active-directory
ldap
or ask your own question.