Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

my AD Query Kills Exchange Server

Hi ,

This is my code below,am not sure is this anyway around to connect with Active Directory and pull the data efficiently.

when ever it hits this method it shows up exchange server is running 100% proc memory.

Some one advise how to do this efficiently,thanks in Advance

 public void FixUserPhoneNumberfromAD(EmployeeExport item, SqlConnection sqlConnection1)
        {
            string phonenumber = string.Empty; // we will get this value from the AD
            string EmailID = string.Empty;
            string employeeNumber = string.Empty; // we will get this from AD. 
            string UserId = string.Empty; // we will get a  user Id from AD.

            try
            {
                
                DirectoryEntry entry = new DirectoryEntry("DomainName");
                DirectorySearcher searcher = new DirectorySearcher();
                searcher.Filter = "(otherpager=" + item.EmployeeNumber + ")";
                foreach (SearchResult result in searcher.FindAll())
                {
                    phonenumber = result.GetDirectoryEntry().Properties["telephonenumber"].Value.ToString();
                    EmailID =     result.GetDirectoryEntry().Properties["mail"].Value.ToString();
                                     
                    entry.Close();
                    if (!String.IsNullOrEmpty(phonenumber))
                    {
                        ///go to the database and update the phone number
                        /// 
                        UpdateDatabasewithPhoneNumber(item.EmployeeNumber, phonenumber, sqlConnection1);
                        UpdateDatabasewithEmail(item.EmployeeNumber, EmailID, sqlConnection1);
                        
                       // entry.Close();
                       
                    }
                    break;
                } 
            }
            catch (System.Exception se) // exception on the console. 
            {
                Console.WriteLine(se.Message);
            }
        }

Open in new window

0
Sha1395
Asked:
Sha1395
  • 13
  • 10
3 Solutions
 
Todd GerbertIT ConsultantCommented:
Well, only thing I think I might suggest is change your filter to "(&(objectClass=person)(otherPager=blah)" (no sense querying things like OU's and User Groups for an employee number).  And you can pass a string array containing the properties you're interested in to the searcher.  And line 19 just caught my eye, you probably don't want to close the entry on each iteration of the foreach loop, and if a given employee number is expected to result in only one user you can use seracher.FindOne().

Are you runnnig Exchange on a domain controller?  Is this an SBS server?  Is this code running on the Exchange server?

using System;
using System.DirectoryServices;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			string filter = "(&(objectClass=person)(telephoneNumber=1145))";
			string[] propertiesToLoad = { "telephoneNumber", "mail" };

			DirectorySearcher searcher = new DirectorySearcher(filter, propertiesToLoad);

			SearchResult result = searcher.FindOne();

			if (result == null)
				Console.WriteLine("Not Found");
			else
				Console.WriteLine(result.Properties["telephoneNumber"][0] + " : " + result.Properties["mail"][0]);

			Console.ReadKey();
		}
	}
}

Open in new window

0
 
Sha1395Author Commented:
Awesome tgerbert,

Thanks for your comment, am running on Exchange Server,is this a problem. every time i start my process the exchange server shoot up the processor level 100%.

Thanks Again for your great help
0
 
Todd GerbertIT ConsultantCommented:
Well, maybe not technically a problem - but given that Exchange is so important to me, and it can be a little finicky, I usually try my best to not run anything that isn't 100% necessary on the Exchange server (e.g. if something goes wrong with my program and it starts sucking CPU cycles that may cause problems for Exchange).

Some people might say to never-ever put anything on the Exchange box, but I realize that's kind of an idealistic viewpoint - not every organization has the budget to buy a new server every time they have another piece of software to run.

If you have another server, or even a workstation, that's available to run this code I'd recommend it.

Are you sure it's the AD query that's giving you grief, and not UpdateDatabasewithPhoneNumber() or UpdateDatabasewithEmail()?  Is the SQL server also running on the Exchange server?

public void FixUserPhoneNumberfromAD(EmployeeExport item, SqlConnection sqlConnection1)
{
	string phonenumber = string.Empty; // we will get this value from the AD
	string EmailID = string.Empty;
	string employeeNumber = string.Empty; // we will get this from AD. 
	string UserId = string.Empty; // we will get a  user Id from AD.
		
	string filter = String.Format("(&(objectClass=person)(otherPager={0}))", item.EmployeeNumber);
	string[] propertiesToLoad = { "mail", "telephoneNumber" };
		
	try
	{
		// Putting the DirectorySearcher in this "using" block will ensure
		// that's .Dispose() method is always called at the end of the block,
		// even if an exception is thrown

		// The DirectorySearcher defaults to the current domain, and searches the sub-tree
		// If you know the path to the container with the user ahead of time, try using
		// DirectoryEntry("LDAP://OU=MyUsers,OU=MyDept,DC=mydomain,DC=local"), filter, propertiesToLoad)
		// or DirectoryEntry("LDAP://CN=Users,DC=mydomain,DC=local"), filter, propertiesToLoad)
		// to limit how much work the searcher has to do
		// Giving the DirectorySearcher a list of properties to loads will cause it to get
		// those properties during the search, so you don't have to call GetDirectoryEntry
		// to retrieve'em
		DirectorySearcher ds = new DirectorySearcher(new DirectoryEntry("LDAP://OU=MyUsers,OU=MyDept,DC=mydomain,DC=local"), filter, propertiesToLoad)
		using (DirectorySearcher searcher = new DirectorySearcher(filter, propertiesToLoad))
		{
			// I assume a given employee number will only ever map to one AD user
			SearchResult result = searcher.FindOne();

			// Properties on AD objects can have more than one value, make
			// sure there's at least one set before attempting to read it
			if (result.Properties["telephoneNumber"].Count > 0)
				phonenumber = result.Properties["telephoneNumber"][0].ToString();

			if (result.Properties["mail"].Count > 0)
				EmailID = result.Properties["mail"][0].ToString();

			if(!String.IsNullOrEmpty(phonenumber))
				UpdateDatabasewithPhoneNumber(item.EmployeeNumber, phonenumber, sqlConnection1);

			if(!String.IsNullOrEmpty(EmailID))
				UpdateDatabasewithEmail(item.EmployeeNumber, EmailID, sqlConnection1);
		}
	}
	catch (System.Exception se) // exception on the console. 
	{
		Console.WriteLine(se.Message);
	}
}

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Todd GerbertIT ConsultantCommented:
Oops, line 25 isn't supposed to be in there.
0
 
Sha1395Author Commented:
thanks tgerber,

Am damn sure my AD query giving a problem.

I tried testing with out AD,the whole process took 2 mins to update and insert 2000 records in DB.

I have another idea,grab all the active users one time and insert in to dummy table in Database,then i will use my app to query the DB.

is that make sense ?

Thanks for all your help
0
 
Todd GerbertIT ConsultantCommented:
How many users, and do they all happen to be in the same OU?
0
 
Sha1395Author Commented:
around 2000 users
0
 
Sha1395Author Commented:
Hi tgerbert,

I run your code,you know it takes 16 Sec to get a value back.

Am stuck in this way believe.

The only way i can see right now,query all the active users in AD and dump it in DB.

Then my process will query db.

Please correct me if am wrong
0
 
Todd GerbertIT ConsultantCommented:
Well, you can use the OleDbProvider to get a list of all enabled users at once and put'em in a DataTable.  Then you can loop through the data table rows.

See: http://msdn.microsoft.com/en-us/library/ms810638.aspx

This gets all enabled users in the domain at one shot, puts the results in a DataTable, and disconnects from the Active Directory.  Then loops through the DataTable and for each entry that has a value in "otherPager", updates the phone number and e-mail.
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

class Program
{
	static void Main(string[] args)
	{
		DataTable users = GetEnabledUsers();

		using (SqlConnection cnx = new SqlConnection("Your Connection String"))
		{
			cnx.Open();
			using (SqlCommand cmd = cnx.CreateCommand())
			{
				foreach (DataRow user in users.Rows)
				{
					if (user["otherPager"] != DBNull.Value && !String.IsNullOrEmpty((string)user["otherPager"]))
					{
						if (user["mail"] != DBNull.Value && !String.IsNullOrEmpty((string)user["mail"]))
						{
							cmd.CommandText = "UPDATE EmployeesTable SET EmailAddress=@mail WHERE EmployeeNumber=@empid";
							cmd.Parameters.AddWithValue("@mail", user["mail"]).DbType = DbType.String;
							cmd.Parameters.AddWithValue("@empid", user["otherPager"]).DbType = DbType.String;
							cmd.ExecuteNonQuery();
							cmd.Parameters.Clear();
						}

						if (user["telephoneNumber"] != DBNull.Value && !String.IsNullOrEmpty((string)user["telephoneNumber"]))
						{
							cmd.CommandText = "UPDATE EmployeesTable SET PhoneNumber=@phone WHERE EmployeeNumber=@empid";
							cmd.Parameters.AddWithValue("@phone", user["telephoneNumber"]).DbType = DbType.String;
							cmd.Parameters.AddWithValue("@empid", user["otherPager"]).DbType = DbType.String;
							cmd.ExecuteNonQuery();
							cmd.Parameters.Clear();
						}
					}
				}
			}
			cnx.Close();
		}
	}

	static DataTable GetEnabledUsers()
	{
		string connectionString = "Provider=ADSDSOObject";
		string searchRoot = "<LDAP://DC=yourdomain,DC=com>";
		string activeUsersFilter = "(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))";
		string attributes = "sAMAccountName, telephoneNumber, mail, otherPager";
		string searchScope = "subtree";
		string commandText = String.Format("{0};{1};{2};{3}",
			searchRoot, activeUsersFilter, attributes, searchScope);

		DataTable enabledUsers = new DataTable();
		using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandText, connectionString))
		{
			dataAdapter.Fill(enabledUsers);
		}

		return enabledUsers;
	}
}

Open in new window


It could be optimized if you can do a specific OU instead of searching the entire sub-tree.
0
 
Sha1395Author Commented:
Hi tgbert,

I have one more question regarding periodic update from AD to DB.

Even i have to do periodic update but is that a better way to do ,at present in my mind once the AD is updated in DB the next time runs it will find only date modified or date changed users only and update that particular users only in my DB.

Is that any other way to achieve this ?
0
 
Todd GerbertIT ConsultantCommented:
You know, I was thinking...if you've got 2,000+ users I'd say that's a large enough organization to warrant making sure you're not running on the Exchange server, even if it means purchasing a new server. ;)

Also note that by default Active Directory will only return a maximum of 1,000 records-per query; you can change the MaxPageSize in your Active Directory config, or use queries that you know will return fewer than 1,000 results (e.g. by querying individual OUs as opposed to the entire tree). I'm not sure if this still applies to Server 2008/2008 R2, but definitely Server 2003 & earlier. http://support.microsoft.com/kb/315071

You can query by date last modified, this would be the modifyTimeStamp attribute in Active Directory. LDAP date queries need the date/time to be in "yyyymmddhhmmss", in UTC time, and have a ".0Z" tacked on the end - so 12:00AM today would look like: 20110316000000.0Z

There's a set of functions here, one to format a DateTime object as the string needed for the LDAP query, GetEnabledUsers(), GetDisabledUsers() and GetEnabledUsersModifiedSince() should all be self-explanatory and will handle the most common queries; GetADObjects is the actual meat, and you can call it directly if you have a query that doesn't fall into one of the other methods. The values for the searchRoot, filter, attributeList and searchScope parameters are described here: http://msdn.microsoft.com/en-us/library/ms810638.aspx

static DataTable GetEnabledUsers()
{
	return GetADObjects("<LDAP://DC=yourdomain,DC=com>", "(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))",
		"sAMAccountName,telephoneNumber,otherPager,mail", "subtree");
}

static DataTable GetDisabledUsers()
{
	return GetADObjects("<LDAP://DC=yourdomain,DC=com>", "(&(objectCategory=person)(objectClass=user)((userAccountControl:1.2.840.113556.1.4.803:=2)))",
		"sAMAccountName,telephoneNumber,otherPager,mail", "subtree");
}

static DataTable GetEnabledUsersModifiedSince(DateTime ModifiedTimeLocal)
{
	return GetADObjects("<LDAP://DC=yourdomain,DC=com>",
		String.Format("(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(modifyTimeStamp>={0}))",
			DateTimeToLDAPDateTime(ModifiedTimeLocal)), "sAMAccountName,telephoneNumber,otherPager,mail", "subtree");
}

static DataTable GetADObjects(string searchRoot, string filter, string attributeList, string searchScope)
{
	string connectionString = "Provider=ADSDSOObject";
	string commandText = String.Format("{0};{1};{2};{3}",
		searchRoot, filter, attributeList, searchScope);

	DataTable enabledUsers = new DataTable();
	using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandText, connectionString))
	{
		dataAdapter.Fill(enabledUsers);
	}

	return enabledUsers;
}

static string DateTimeToLDAPDateTime(DateTime localTime)
{
	return String.Format("{0:yyyyMMddHHmmss}.0Z", localTime.ToUniversalTime());
}

Open in new window

0
 
Sha1395Author Commented:
hi Tgerbert,


Thanks again for the great explanation and code.Honestly am a novice in AD.still half of the part i couldn't understand
but i will try to consolidate your code and come up with (hopefully) the working coed for AD sync with DB on periodic base.

I can't thank enough for all your great help.
0
 
Sha1395Author Commented:
Hi tgerbert,

i was running your code i got this error message "A referral was returned from the server."

What am missing ?
0
 
Sha1395Author Commented:
here are the two places i changed in your code

 string searchRoot = "<LDAP://DC=au.pfoxint.com,DC=com>";
 using (SqlConnection cnx = new SqlConnection(@"Data Source=SYDTESTSVR05;Initial Catalog=DLAPGateway_DEV;Integrated Security=SSPI"))


but exception throws at

 dataAdapter.Fill(enabledUsers);
0
 
Todd GerbertIT ConsultantCommented:
You have more than one domain?
0
 
Sha1395Author Commented:
no i have only one domain.

at present we have app written in VB,they are using below string to retrieve values from AD

Private Function provideLdapQuery(ByVal domainName As String) As String
       
        Dim ldap As String = "LDAP://AUPFOXINT/DC=AU,DC=PFOXINT,DC=COM"
        Return ldap
    End Function

even though i use to connect AD using the below string

 DirectoryEntry entry = new DirectoryEntry("GC://PFOXINT", "aupfoxint\\LdapUser", "Ld@pusr1", AuthenticationTypes.Secure);

can i use either one in your LDAP string to execute the code ?
0
 
Sha1395Author Commented:
ignore my earlier message ,i just chage the LADAP string ,now i got different error message

'ADSDSOObject' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).
0
 
Sha1395Author Commented:
Hi tgerbert,

i changed my connection string like below

string connectionString = "Provider=SQLOLEDB;Data Source=SYDTESTSVR05;Initial Catalog=DLAPGateway_DEV;Integrated Security=SSPI";

now throwing exception like Incorrect syntax near '<;'.

here is the command text

"<LDAP://DC=yourdomain,DC=com>;
(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)));

0
 
Todd GerbertIT ConsultantCommented:
The OleDb provider for Active Directory is a little limited, I only suggested it because it provides a convenient way to fill a DataTable and System.DirectoryServices seemed to be causing you some issues. I think you can change the CommandText used with the OleDbCommand that queries the Active Directory to "<GC://domain.com>;((&objectCategory=person));mail,otherPager;subtree" to attempt to bind to a Global Catalog server, which resolve your referral issue.

Ideally, use the System.DirectoryServices.DirectorySearcher on separate machine; and re-think your strategy (e.g. query individual OUs at staggered intervals), 2,000 objects is a lot to retrieve from the AD and is probably going to cause performance issues no matter the method.

Make sure you're using the right connection strings with the right connection object, 1 is for acessing SQL the other the AD.

Accessing the SQL database: SqlConnection object, connection string = "Provider=SQLOLEDB;Data Source=...", command text = "UPDATE SomeTable SET SomeField='SomeValue'"

Active Directory: OleDbConnection object, connection string = "Provider=ADSDSOObject", command text = "<LDAP://DC=yourdomain,DC=com>;(&(objectClass=person)(objectCategory=user));mail,telephoneNumber,otherPager;subtree"

I've never had a need myself, so I have no idea how it works, but I have seen references in various forums to creating tables in SQL that automatically link to the Active Directory (or perhaps they import once or twice a day) - if you manage to set that up, then you could do your work against a SQL database instead of directly against the directory.
0
 
Sha1395Author Commented:
thanks tgerbert, based on your suggestion i will change the string and let you know the out come
0
 
Sha1395Author Commented:
hi tgerbert,

Here is the code below to retrieve the value from AD and store in DB(am store in my own table).

I need your help to do periodic update,am not sure yet where i have to change in my code.Am planning to run this code every hour,eg: if am going to run at 10Am then the code will look for any modified or changes happend after 9Am then it will pull accordingly.

Thanks in Advance


static void Main(string[] args)
        {
           
            DirectorySearcher searcher = new DirectorySearcher();
            searcher.Filter = "(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@dlaphillipsfox.com))";
            //Default maxpage size is 1000 in AD config
            //so we max the page size to 3000
            searcher.PageSize = 3000;

            string EmailID = string.Empty;
            string phoneNo = string.Empty;
            string AccountName = String.Empty;
            string EmployeeNo = string.Empty;
            foreach (SearchResult result in searcher.FindAll())
            {
                var Phone = result.GetDirectoryEntry().Properties["telephonenumber"].Value;
                if (Phone != null)
                {
                    phoneNo = Phone.ToString();
                    
                }
                
                var mail = result.GetDirectoryEntry().Properties["mail"].Value;
                if(mail!=null)
                {
                    EmailID = mail.ToString();
                    
                   
                }

                var Name = result.GetDirectoryEntry().Properties["sAMAccountName"].Value;
                if (Name != null)
                {
                    AccountName = Name.ToString();
                   
                }

                var Emply = result.GetDirectoryEntry().Properties["otherpager"].Value;
                if (Emply != null)
                {
                    EmployeeNo = Emply.ToString();

                }
                else
                {
                     
                    EmployeeNo =null ;
                }
              
                //i use substring to get  User Id seperate from Email ID
                string subStringString = EmailID;
                string[] stringSeparators = new string[] { "@" };
                string[]UsersID;
              UsersID = subStringString.Split(stringSeparators, StringSplitOptions.None);
                string UserID = UsersID[0].ToString();
              
               
                //SQl Insert
                try
                {

                    System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(your string");
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                    cmd.CommandType = System.Data.CommandType.Text;
                    //i will write store procedure for this query
                    cmd.CommandText = "INSERT ADUser (EmployeeNo,Name,UserID,Email,PhoneNo ) VALUES("
                                                      + "'" + EmployeeNo + "'" + ","
                                                       + "'" + AccountName.Replace("'", "''") + "'" + ","
                                                       + "'" + UserID.Replace("'", "''") + "'" + ","
                                                       + "'" + EmailID.Replace("'", "''") + "'" + ","
                                                       + "'" + phoneNo + "'" + ")";

                    cmd.Connection = sqlConnection1;
                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection1.Close();

                }
                catch (System.Exception se)
                {
                    Console.WriteLine(se);
                }

            } 
//
            searcher.Dispose();
            entry.Close();

Open in new window

0
 
Todd GerbertIT ConsultantCommented:
Just add modifyTimeStamp to your LDAP query.  Just remember that the format is the date/time string in your LDAP query is expected to be UTC, and today at 9:35AM would look like "20110321093500.0Z"  String.Format will do that nicely: String.Format("{0:yyyyMMddHHmmss}.0Z", localTime.ToUniversalTime())

searcher.Filter = "(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@dlaphillipsfox.com)(modifyTimeStamp>=20110321093500.0Z)";

Open in new window

0
 
Todd GerbertIT ConsultantCommented:
Missed a parentheses:
searcher.Filter = "(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@dlaphillipsfox.com)(modifyTimeStamp>=20110321093500.0Z))";

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 13
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now