Solved

Active Directory Periodic Update

Posted on 2011-03-21
16
565 Views
Last Modified: 2012-06-21
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
Comment
Question by:Sha1395
  • 8
  • 8
16 Comments
 
LVL 33

Expert Comment

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

Accepted Solution

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

Author Comment

by:Sha1395
ID: 35185064
Thanks tgerbert.

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

Thanks again
0
 

Author Comment

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

Author Comment

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

Expert Comment

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

Expert Comment

by:Todd Gerbert
ID: 35195151
0
 

Author Comment

by:Sha1395
ID: 35195616
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35195730
A quick test would seem to indicate that "@gmail.com" is perfectly valid.
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:Todd Gerbert
ID: 35213880
Either or, 6 of one half dozen of the other.
0
 

Author Comment

by:Sha1395
ID: 35213888
am not getting you
0
 
LVL 33

Expert Comment

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

Author Comment

by:Sha1395
ID: 35213918
Thanks for the early morning help ;). Look like we both on other end of the world its night here.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
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…

760 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

22 Experts available now in Live!

Get 1:1 Help Now