Solved

Active Directory Periodic Update

Posted on 2011-03-21
16
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 

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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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