Solved

my AD Query Kills Exchange Server

Posted on 2011-03-14
23
667 Views
Last Modified: 2012-06-21
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
Comment
Question by:Sha1395
  • 13
  • 10
23 Comments
 
LVL 33

Accepted Solution

by:
Todd Gerbert earned 500 total points
ID: 35141202
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
 

Author Comment

by:Sha1395
ID: 35142890
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
 
LVL 33

Assisted Solution

by:Todd Gerbert
Todd Gerbert earned 500 total points
ID: 35143060
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35143064
Oops, line 25 isn't supposed to be in there.
0
 

Author Comment

by:Sha1395
ID: 35144071
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35144075
How many users, and do they all happen to be in the same OU?
0
 

Author Comment

by:Sha1395
ID: 35144129
around 2000 users
0
 

Author Comment

by:Sha1395
ID: 35144154
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
 
LVL 33

Assisted Solution

by:Todd Gerbert
Todd Gerbert earned 500 total points
ID: 35144262
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
 

Author Comment

by:Sha1395
ID: 35146607
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35148029
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Sha1395
ID: 35152924
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
 

Author Comment

by:Sha1395
ID: 35153421
Hi tgerbert,

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

What am missing ?
0
 

Author Comment

by:Sha1395
ID: 35153426
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35156712
You have more than one domain?
0
 

Author Comment

by:Sha1395
ID: 35160967
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
 

Author Comment

by:Sha1395
ID: 35160978
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
 

Author Comment

by:Sha1395
ID: 35161538
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35164581
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
 

Author Comment

by:Sha1395
ID: 35165399
thanks tgerbert, based on your suggestion i will change the string and let you know the out come
0
 

Author Comment

by:Sha1395
ID: 35179478
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35180628
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35180638
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now