?
Solved

Active Directory Periodic Update

Posted on 2011-03-21
16
Medium Priority
?
599 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 2000 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
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.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

862 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