• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Active Directory Periodic Update

Hi All


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
Sha1395
Asked:
Sha1395
  • 8
  • 8
1 Solution
 
Todd GerbertIT ConsultantCommented:
Ah, I just tacked this on to the end of your other question... ;)

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


I assume you'll be scheduling the code with some other mechanism, like the Windows task scheduler, so don't need that logic here.  Get the current hour (i.e. we're running at 10:00AM), subtract 1 hour (because we want changes since 9:00AM), convert it to UTC time and format as above.
DateTime previousHour = DateTime.Now.AddHours(-1);
string ldapDateString = String.Format("{0:yyyyMMddHH}0000.0Z", previousHour.ToUniversalTime());
string filter = String.Format("(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@dlaphillipsfox.com)({0}))", ldapDateString);

Open in new window

0
 
Todd GerbertIT ConsultantCommented:
Typos abound today.  Corrected:
DateTime previousHour = DateTime.Now.AddHours(-1);
string ldapDateString = String.Format("{0:yyyyMMddHH}0000.0Z", previousHour.ToUniversalTime());
string filter = String.Format("(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@dlaphillipsfox.com)(modifyTimeStamp>={0}))", ldapDateString);

Open in new window

0
 
Sha1395Author Commented:
Thanks tgerbert.

haven't test yet,i will update the code and result

Thanks again
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Sha1395Author Commented:
Hi tgerbert,

here is the code i modified based on your input, can you please advise me a doing the right thing here ?

static void Main(string[] args)
        {

            //Getting present time and date

            DirectoryEntry entry = new DirectoryEntry(YourDomain);
            DirectorySearcher searcher = new DirectorySearcher();

            DateTime previousHour = DateTime.Now.AddHours(-1);
            string ldapDateString = String.Format("{0:yyyyMMddHH}0000.0Z", previousHour.ToUniversalTime());
            string filter = String.Format("(&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mail=*@gmail.com)(modifyTimeStamp>={0}))", ldapDateString);

           
            
            //update AD User Table every Hour

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

            //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 DLA User Id seperate from Email ID
                string subStringString = EmailID;
                string[] stringSeparators = new string[] { "@" };
                string[] DLAUsersID;
                DLAUsersID = subStringString.Split(stringSeparators, StringSplitOptions.None);
                string DLAUserID = DLAUsersID[0].ToString();
              
               
                //SQl Insert
                try
                {

                    System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(@"Data Source=SYDTESTSVR05;Initial Catalog=DLAPGateway_DEV;Integrated Security=SSPI");
                    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,DLAUserID,Email,PhoneNo ) VALUES("
                                                      + "'" + EmployeeNo + "'" + ","
                                                       + "'" + AccountName.Replace("'", "''") + "'" + ","
                                                       + "'" + DLAUserID.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
 
Sha1395Author Commented:
The reason for post my code above,i was debugging its doing the same thing after using your search filter string.

That's why i feel it am doing some thing wrong
0
 
Todd GerbertIT ConsultantCommented:
That looks right, except that on line 11 you have a string variable named "filter", yet on line 17 you're setting searcher.Filter by hand using a hard-coded date/time of 3/21/2011 09:35AM Greenwich Mean Time - which is like 1:35AM Pacific Daylight Time (remember that the LDAP search filter expects the date/time to be GMT).  Try changing line 17 to:
searcher.Filter = filter;

Open in new window


The only other thing that comes to mind, and really I have no idea one way or the other without having tested it, is that the "@" sign might not be valid in an LDAP filter string, so you might need to use (mail=*gmail.com) instead of (mail*@gmail.com). Like I said, though, that's sheer speculation - total shot in the dark. ;)
0
 
Sha1395Author Commented:
thanks tgerbert,

sorry i didn't notice that line 17 (that's the reason why its doing the same thing it does earlier),apart from mail part at present my search filter using  (mail*@gmail.com) and it works fine but i will try with out @ and see what's going and any changes.

Thanks a lot again tgerbert
0
 
Todd GerbertIT ConsultantCommented:
A quick test would seem to indicate that "@gmail.com" is perfectly valid.
0
 
Sha1395Author Commented:
Hi tgerbert,

i came across new situation now, when i schedule this task for an hourly run.

Eg: the process runs at 10Am and look for updates happened from 9Am to 10Am right.I gotta network problem so the 10Am job failed.
the next run was 11AM and start looking the updates from 10Am to 11Am .I just missed the 9Am to 10 Am updates.

How can i handle this situation.Am schedule this task on windows scheduler.

Thanks in Advance
0
 
Todd GerbertIT ConsultantCommented:
You'll just need to record the last time you had a successful run, saving it to your application's settings file would probably work well, and then use that DateTime for your modifyTimeStamp filter.
0
 
Sha1395Author Commented:
Thanks for the comment tgerbert.Even am planning to ad a new column in my table "LastRun" and whenever it runs successfully it leaves the timestamp here,so i can use it for next run.

Am not sure which one is feasible,please correct me
0
 
Todd GerbertIT ConsultantCommented:
Either or, 6 of one half dozen of the other.
0
 
Sha1395Author Commented:
am not getting you
0
 
Todd GerbertIT ConsultantCommented:
That's because it's early (the sun's not even up yet), and my brain is still working at half speed.

I meant that storing the last run time in a settings file in your application, or storing it in the SQL database - both approaces will work equally well, use whichever you prefer.
0
 
Sha1395Author Commented:
Thanks for the early morning help ;). Look like we both on other end of the world its night here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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