public bool EnableUser(string sAMAccountName, string ldapDomain, LogFile log)
{
string userDN;
userDN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, sAMAccountName, ldapDomain, log);
try
{
DirectoryEntry usr = new DirectoryEntry(userDN);
int val = (int)usr.Properties["userAccountControl"].Value;
usr.Properties["userAccountControl"].Value = val | ~(int)accountFlags.ADS_UF_ACCOUNTDISABLE;
usr.CommitChanges();
usr.Close();
usr.Dispose();
log.addTrn("enabled user account |" + userDN, "Transaction");
return true;
}
catch (System.DirectoryServices.DirectoryServicesCOMException E)
{
log.addTrn(E.Message.ToString() + " error enabling user " + userDN, "Error");
return false;
}
}
public void UpdateGmailUser(AppsService service, GmailUsers gusersyn, SqlDataReader usersToUpdate, LogFile log)
{
string userNickName = "";
string middlename = "";
try
{
while (usersToUpdate.Read())
{
try
{
UserEntry gmailUser = service.RetrieveUser((string)usersToUpdate[gusersyn.User_StuID]);
//special gmail username replace string only allows -_. special character thru
gmailUser.Login.UserName = System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%", "_");
gmailUser.Name.FamilyName = usersToUpdate[gusersyn.User_Lname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
gmailUser.Name.GivenName = usersToUpdate[gusersyn.User_Fname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
middlename = usersToUpdate[gusersyn.User_Mname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
service.UpdateUser(gmailUser);
log.addTrn("Updated " + System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A") + " because of name change. New Name is " + gmailUser.Name.FamilyName.ToString() + ", " + gmailUser.Name.GivenName.ToString(), "Transaction");
// if (gusersyn.Levenshtein == true)
// {
userNickName = GetNewUserNickname(service, gmailUser.Login.UserName, gmailUser.Name.GivenName, middlename, gmailUser.Name.FamilyName, 0, false);
log.addTrn("Added New Alias for " + gmailUser.Login.UserName + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain, "Transaction");
// }
}
catch (Exception ex)
{
log.addTrn("Failed update gmail account " + System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%", "_") + " exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
}
catch (Exception ex)
{
log.addTrn("Issue updating gmail users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void UpdateUsers(SqlDataReader users, string ldapDomain, UserSynch usersyn, LogFile log)
{
// requires distinguished name to be a field
// all field names must be valid AD field names
// does not blank out fields
int fieldcount = 0;
int i = 0;
string name = "";
string fdqn = "";
try
{
fieldcount = users.FieldCount;
while (users.Read())
{
DirectoryEntry user = new DirectoryEntry("LDAP://" + (string)users["distinguishedname"]);
for (i = 0; i < fieldcount; i++)
{
name = users.GetName(i);
// eliminiate non updatable fields
if (name != "password" && name != "CN" && name != "sAMAccountName" && name != "distinguishedname")
{
// mail needs some special handling
switch (name)
{
case "mail":
if ((string)users[name] != "")
{
// check to see if mail field has illegal characters
string hi = (System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("%40", "@").Replace("%5f", "_"));
string hi3 = (string)users[name];
if (System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("%40", "@").Replace("%5f", "_") == (string)users[name])
{
// no illegal characters input the value into AD
user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("%5f", "_").Replace(" ", "%20").Replace("%40", "@");
}
else
{
user.Properties[name].Value = "illegal Email";
}
}
break;
case "userAccountControl":
if ((string)users[name] != "")
{
int val = (int)user.Properties["userAccountControl"].Value;
user.Properties["userAccountControl"].Value = val | Convert.ToInt32(System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").ToString());
}
break;
case "manager":
if ((string)users[name] != "")
{
fdqn = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A"), ldapDomain, log);
if (!fdqn.Contains("CN"))
{
log.addTrn("Issue Updating User: "******"distinguishedname"] + " Invalid Manager selected. ", "Error");
}
else
{
user.Properties["manager"].Value = fdqn.Substring(fdqn.IndexOf("CN"));
}
}
break;
case "sn":
if ((string)users[name] != "")
{
user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A");
user.Properties["displayName"].Value = System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Lname]).Replace("+", " ").Replace("*", "%2A") + ", " + System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Fname]).Replace("+", " ").Replace("*", "%2A");
user.Properties["description"].Value = System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Lname]).Replace("+", " ").Replace("*", "%2A") + ", " + System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Fname]).Replace("+", " ").Replace("*", "%2A");
}
break;
case "givenName":
if ((string)users[name] != "")
{
user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A");
user.Properties["displayName"].Value = System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Lname]).Replace("+", " ").Replace("*", "%2A") + ", " + System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Fname]).Replace("+", " ").Replace("*", "%2A");
user.Properties["description"].Value = System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Lname]).Replace("+", " ").Replace("*", "%2A") + ", " + System.Web.HttpUtility.UrlEncode((string)users[usersyn.User_Fname]).Replace("+", " ").Replace("*", "%2A");
}
break;
default:
if ((string)users[name] != "")
{
user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A");
}
break;
}
//if (name != "mail")
//{
// if ((string)users[name] != "")
// {
// user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A");
// }
//}
//else
//{
// // check to see if mail field has illegal characters
// string hi = (System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("%40", "@"));
// string hi3 = (string)users[name];
// if (System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("%40", "@") != (string)users[name])
// {
// // no illegal characters input the value into AD
// user.Properties[name].Value = System.Web.HttpUtility.UrlEncode((string)users[name]).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%40", "@");
// }
// else
// {
// user.Properties[name].Value = "illegal Email";
// }
//}
}
}
user.CommitChanges();
log.addTrn("User updated |" + (string)users["distinguishedname"] + " ", "Transaction");
}
}
catch (Exception ex)
{
if (users != null)
{
log.addTrn("issue updating user " + name + " " + System.Web.HttpUtility.UrlEncode((string)users["distinguishedname"]).Replace("+", " ").Replace("*", "%2A") + "\n" + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
else
{
log.addTrn("issue updating users data reader is null " + "\n" + ex.Message.ToString(), "Error");
}
}
}
public void SelectNicknamesClosestToActualNameIntoNewTable(string table1, string table2, string pkey1, string pkey2, string newTable, ArrayList selectFields, string targetField, ArrayList valueFields, SqlConnection sqlConn, LogFile log)
{
//select distinct soc_sec,
//(Select top 1 nickname
// from FHC_TEST_gmailNicknamesTable left join FHC_TEST_sqlusersTable on FHC_TEST_gmailNicknamesTable.soc_sec = FHC_TEST_sqlusersTable.sAMAccountName
// where soc_sec = a.soc_sec
// order by soc_sec,
// CHARINDEX(FHC_TEST_sqlusersTable.sn, FHC_TEST_gmailNicknamesTable.nickname) DESC,
// CHARINDEX(FHC_TEST_sqlusersTable.givenName, FHC_TEST_gmailNicknamesTable.nickname) DESC,
// CHARINDEX(FHC_TEST_sqlusersTable.middleName, FHC_TEST_gmailNicknamesTable.nickname) DESC
//)
//as nickname,
// (Select top 1 email
// from FHC_TEST_gmailNicknamesTable left join FHC_TEST_sqlusersTable on FHC_TEST_gmailNicknamesTable.soc_sec = FHC_TEST_sqlusersTable.sAMAccountName
// where soc_sec = a.soc_sec
// order by soc_sec, CHARINDEX(FHC_TEST_sqlusersTable.sn, FHC_TEST_gmailNicknamesTable.nickname) DESC, CHARINDEX(FHC_TEST_sqlusersTable.givenName, FHC_TEST_gmailNicknamesTable.nickname) DESC, CHARINDEX(FHC_TEST_sqlusersTable.middleName, FHC_TEST_gmailNicknamesTable.nickname) DESC) as email
//FROM FHC_TEST_gmailNicknamesTable as a
//ORDER BY soc_sec;
// Assumes table1 holds the correct data and returns a data reader with the update fields columns from table1
// returns the rows which table2's concatenated update fields differ from table1's concatenated update fields
// eliminates rows which do not have a matching key in both tables
// adds convluted logic to deal with duplicates and select the one closest to the matching data from the table 2 assumed to be the correct first middle last name etc
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data | | Table1.ID Table1.DATA
//| 1 a | 1 a | NOT RETURNED |
//| 2 b | null null | NOT RETURNED |
//| 3 c | 3 null | RETURNED | 3 c
//| 4 d | 4 e | RETURNED | 4 e
//string table1 the table with the new nicknames
//string table2 the table with the original names
//string pkey1
//string pkey2
//string newTable = ; the name of the new Table We want To return
//ArrayList selectFields = new ArrayList( [ email] )List of fields we want returned fields should Be in table1
//string targetField = "nickname"; name of the field we want to be close to ie like a google search result validity must be in table1
//ArrayList valueFields = new ArrayList( [sn, givenName, middleName ] ); lift of fields which are being compared to the targetField to see how close it gets
string complexField = "";
foreach (string key in selectFields)
{
complexField += "(SELECT TOP 1 " + table1 + "." + key;
complexField += " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2;
complexField += " WHERE " + table1 + "." + pkey1 + " = extTable." + pkey1;
complexField += " ORDER BY " + table2 + "." + pkey2 + ",";
foreach (string value in valueFields)
{
// should have first middle last as the arraylist for valueFields1
complexField += " CHARINDEX(" + table2 + "." + value + ", " + table1 + "." + targetField + ") DESC,";
}
// comma remove
complexField = complexField.Remove(complexField.Length - 1);
complexField += ") AS " + key + ",";
}
// comma remove
complexField = complexField.Remove(complexField.Length - 1);
SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT extTable." + pkey1 + ", " + complexField + " INTO " + newTable + " FROM " + table1 + " AS extTable ORDER BY extTable." + pkey1, sqlConn);
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public ArrayList SetMultiPropertyUser(LinkedList<Dictionary<string, string>> userList, ArrayList propertyArray, string ldapDomain, LogFile log)
{
/*
* takes a dictionary like such
* sAMAccountName, userName
* property1, value
* property2, value
* ....
*
* and an arraylist of strings with the names of the keys for the properties
* ["property1", "property2", ...etc]
*
* RETURNS
* users not found
*/
LinkedListNode<Dictionary<string, string>> userListNode = userList.First;
string sAMAccountName;
string userProperty;
string usrDN;
ArrayList returnvalue = new ArrayList();
while (userListNode != null)
{
userListNode.Value.TryGetValue("sAMAccountName", out sAMAccountName);
usrDN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, sAMAccountName, ldapDomain, log);
if (usrDN == "")
{
returnvalue.Add(sAMAccountName);
}
//// get usr object for manipulation
DirectoryEntry user = new DirectoryEntry(usrDN);
foreach (string props in propertyArray)
{
userListNode.Value.TryGetValue(props, out userProperty);
user.Properties[props].Value = userProperty;
}
user.CommitChanges();
userListNode = userListNode.Next;
}
return returnvalue;
}
// SQL query tools
public SqlDataReader QueryNotExistsByPkey(string table1, string table2, SqlConnection sqlConn, string pkey1, string pkey2, LogFile log)
{
// Array list of pkeys is for use when the primary key is clustered (multiple columns are required to get a unique identification on the row)
// finds items in table1 who do not exist in table2 and returns the data fields table 1 for these rows
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data | | Table1.ID Table1.DATA
//| 1 a | 1 a | NOT RETURNED |
//| 2 b | null null | RETURNED | 2 b
//| 3 c | 3 null | NOT RETURNED |
//| 4 d | 4 e | NOT RETURNED |
//
// SqlCommand sqlComm = new SqlCommand("Select Table1.* Into #Table3ADTransfer From " + Table1 + " AS Table1, " + Table2 + " AS Table2 Where Table1." + pkey1 + " = Table2." + pkey2 + " And Table2." + pkey2 + " is null", sqlConn);
//SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT uptoDate.* FROM " + table1 + " uptoDate LEFT OUTER JOIN " + table2 + " outofDate ON outofDate." + pkey2 + " = uptoDate." + pkey1 + " WHERE outofDate." + pkey2 + " IS NULL;", sqlConn);
// Actual query
SqlCommand sqlComm = new SqlCommand("SELECT * "+
"FROM "+ table1 +
" WHERE " + pkey1 + " in ( SELECT " +
pkey1 +
" FROM " + table1 +
" EXCEPT "+
" SELECT "+
pkey2 +
" FROM " + table2 + " )", sqlConn);
// create the command object
SqlDataReader r;
try
{
sqlComm.CommandTimeout = 360;
r = sqlComm.ExecuteReader();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
return r;
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return null;
}
public void RemoveUserFromGroup(string userDn, string groupDn, LogFile log)
{
try
{
DirectoryEntry entry = new DirectoryEntry("LDAP://" + groupDn);
try
{
entry.Properties["member"].Remove(userDn);
log.addTrn("removed user from group | " + userDn + " | LDAP://" + groupDn, "Transaction");
}
catch (System.DirectoryServices.DirectoryServicesCOMException E)
{
log.addTrn(E.Message.ToString() + " error removing user from group " + userDn + " from LDAP://" + groupDn + " user may not be in group", "Error");
}
entry.CommitChanges();
entry.Close();
entry.Dispose();
}
catch (System.DirectoryServices.DirectoryServicesCOMException E)
{
log.addTrn(E.Message.ToString() + " error removing user from group " + userDn + " from LDAP://" + groupDn + " group object does not exist", "Warning");
}
}
public DataTable Get_Gmail_Nicknames(AppsService service, GmailUsers gusersyn, string table, LogFile log)
{
// nicknames retrieval
DataTable returnvalue = new DataTable();
DataRow row;
returnvalue.TableName = table;
int i = 0;
int count = 0;
string nickname = "";
returnvalue.Columns.Add(gusersyn.Writeback_primary_key);
returnvalue.Columns.Add("nickname");
returnvalue.Columns.Add("Email");
try
{
NicknameFeed usersNicknameList = service.RetrieveAllNicknames();
count = usersNicknameList.Entries.Count;
//result.AppendText("domain " + service.Domain + "\n");
//result.AppendText("app name " + service.ApplicationName + "\n");
//result.AppendText("users " + count + "\n");
row = returnvalue.NewRow();
for (i = 0; i < count; i++)
{
NicknameEntry userNicknameEntry = usersNicknameList.Entries[i] as NicknameEntry;
// special handling for userID due to % being an illegal character using _ as an escape character
row[0] = (System.Web.HttpUtility.UrlDecode(userNicknameEntry.Login.UserName.ToString().Replace("_", "%")));
// decode names due to encoding to remove <>= characters
nickname = (System.Web.HttpUtility.UrlDecode(userNicknameEntry.Nickname.Name.ToString()));
row[1] = nickname;
row[2] = nickname + "@" + gusersyn.Admin_domain.ToString();
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
}
catch (Exception ex)
{
log.addTrn("failed to pull gmail nickname list exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
// Gmail data pull
public DataTable Get_Gmail_Users(AppsService service, GmailUsers gusersyn, string table, LogFile log)
{
// nicknames will have to dealt with seperately
DataTable returnvalue = new DataTable();
DataRow row;
returnvalue.TableName = table;
int i = 0;
int count = 0;
returnvalue.Columns.Add(gusersyn.User_StuID);
returnvalue.Columns.Add(gusersyn.User_Fname);
returnvalue.Columns.Add(gusersyn.User_Lname);
try
{
UserFeed usersList = service.RetrieveAllUsers();
count = usersList.Entries.Count;
//result.AppendText("domain " + service.Domain + "\n");
//result.AppendText("app name " + service.ApplicationName + "\n");
//result.AppendText("users " + count + "\n");
row = returnvalue.NewRow();
for (i = 0; i < count; i++)
{
UserEntry userEntry = usersList.Entries[i] as UserEntry;
// special handling for userID due to % being an illegal character using _ as an escape character
row[0] = (System.Web.HttpUtility.UrlDecode(userEntry.Login.UserName.ToString().Replace("_", "%")));
// decode names due to encoding to remove <>= characters
row[1] = (System.Web.HttpUtility.UrlDecode(userEntry.Name.GivenName.ToString()));
row[2] = (System.Web.HttpUtility.UrlDecode(userEntry.Name.FamilyName.ToString()));
//userList.Add(userEntry.Login.UserName.ToString());
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
}
catch (Exception ex)
{
log.addTrn("failed to pull gmail user list exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public string GetAttributeValuesSingleString(string attributeName, string objectDn, LogFile log)
{
string strValue;
try
{
DirectoryEntry ent = new DirectoryEntry(objectDn);
strValue = ent.Properties[attributeName].Value.ToString();
ent.Close();
ent.Dispose();
}
catch
{
log.addTrn("failed to pull " + attributeName + " on object " + objectDn, "Error");
return null;
}
return strValue;
}
public string GetObjectDistinguishedName(objectClass objectCls, returnType returnValue, string objectName, string ldapDomain, LogFile log)
{
// LdapDomain = "DC=Fabrikam,DC=COM"
string distinguishedName = string.Empty;
string connectionPrefix = "LDAP://" + ldapDomain;
try
{
DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
DirectorySearcher mySearcher = new DirectorySearcher(entry);
switch (objectCls)
{
case objectClass.user:
mySearcher.Filter = "(&(objectClass=user)(|(CN=" + objectName + ")(sAMAccountName=" + objectName + ")))";
break;
case objectClass.group:
mySearcher.Filter = "(&(objectClass=group)(|(CN=" + objectName + ")(dn=" + objectName + ")))";
break;
case objectClass.computer:
mySearcher.Filter = "(&(objectClass=computer)(|(CN=" + objectName + ")(dn=" + objectName + ")))";
break;
case objectClass.organizationalunit:
mySearcher.Filter = "(&(objectClass=organizationalunit)(distinguishedname=" + objectName + "))";
break;
}
SearchResult result = mySearcher.FindOne();
if (result == null)
{
//throw new NullReferenceException
//("unable to locate the distinguishedName for the object " +
//objectName + " in the " + LdapDomain + " domain");
return string.Empty;
}
DirectoryEntry directoryObject = result.GetDirectoryEntry();
if (returnValue.Equals(returnType.distinguishedName))
{
distinguishedName = "LDAP://" + directoryObject.Properties
["distinguishedName"].Value;
}
if (returnValue.Equals(returnType.ObjectGUID))
{
distinguishedName = directoryObject.Guid.ToString();
}
entry.Close();
entry.Dispose();
mySearcher.Dispose();
}
catch (Exception ex)
{
log.addTrn("searcher failed " + ldapDomain + " " + objectName + " Exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return distinguishedName;
}
public DataTable EnumerateUsersInOUDataTable(string ouDN, ArrayList returnProperties, string table, SearchScope scope, LogFile log)
{
// note does not handle special/illegal characters for AD
// RETURNS ALL USERS IN AN OU NO INCULDING SUBLEVELS MATTER HOW DEEP
int count = returnProperties.Count;
int i;
DataTable returnvalue = new DataTable();
DataRow row;
// bind to the OU you want to enumerate
try
{
DirectoryEntry deOU = new DirectoryEntry("LDAP://" + ouDN);
// create a directory searcher for that OU
DirectorySearcher dsUsers = new DirectorySearcher(deOU);
// set depth to recursive
dsUsers.SearchScope = scope;
// set the filter to get just the users
dsUsers.Filter = "(&(objectClass=user)(objectCategory=Person))";
// add the attributes you want to grab from the search
for (i = 0; i < count; i++)
{
dsUsers.PropertiesToLoad.Add(returnProperties[i].ToString());
returnvalue.Columns.Add(returnProperties[i].ToString());
}
//dsUsers.PropertiesToLoad.Add("sAMAccountName");
// grab the users and do whatever you need to do with them
dsUsers.PageSize = 1000;
row = returnvalue.NewRow();
foreach (SearchResult oResult in dsUsers.FindAll())
{
//generate the array list with the user sam accounts
for (i = 0; i < count; i++)
{
try
{
row[i] = System.Web.HttpUtility.UrlDecode((Convert.IsDBNull(oResult.Properties[returnProperties[i].ToString()][0]) ? string.Empty : oResult.Properties[returnProperties[i].ToString()][0].ToString()));
}
catch
{
row[i] = string.Empty;
}
}
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
dsUsers.Dispose();
}
catch (Exception ex)
{
log.addTrn("Failure getting AD users exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public DataTable EnumerateUsersInGroupDataTable(string groupDN, string groupou, string coulumnNameForFQDN, string coulumnNameForGroup, string table, LogFile log)
{
// note does not handle special/illegal characters for AD
// it is optimal to have the two field names match the coumn names pulled from SQL do not use group fro group name it will kill the SQL
// groupDN "CN=Sales,OU=test,DC=Fabrikam,DC=COM"
// returns FQDN "CN=user,OU=test,DC=Fabrikam,DC=COM" & group "CN=Sales,OU=test,DC=Fabrikam,DC=COM" of users in group
DataTable returnvalue = new DataTable();
DataRow row;
int count = 0;
//string bladh = "LDAP://" + "CN=" + System.Web.HttpUtility.UrlEncode(groupDN).Replace("+", " ").Replace("*", "%2A") + groupou;
//DirectoryEntry group = new DirectoryEntry("LDAP://" + "CN=" + System.Web.HttpUtility.UrlEncode(groupDN).Replace("+", " ").Replace("*", "%2A") + groupou);
//DirectorySearcher groupUsers = new DirectorySearcher(group);
try
{
DirectoryEntry group = new DirectoryEntry("LDAP://" + "CN=" + System.Web.HttpUtility.UrlEncode(groupDN).Replace("+", " ").Replace("*", "%2A") + groupou);
DirectorySearcher groupUsers = new DirectorySearcher(group);
groupUsers.Filter = "(objectClass=*)";
row = returnvalue.NewRow();
returnvalue.TableName = table;
returnvalue.Columns.Add(coulumnNameForFQDN);
returnvalue.Columns.Add(coulumnNameForGroup);
uint rangeStep = 1000;
uint rangeLow = 0;
uint rangeHigh = rangeLow + (rangeStep - 1);
bool lastQuery = false;
bool quitLoop = false;
do
{
string attributeWithRange;
if (!lastQuery)
{
attributeWithRange = String.Format("member;range={0}-{1}", rangeLow, rangeHigh);
}
else
{
attributeWithRange = String.Format("member;range={0}-*", rangeLow);
}
groupUsers.PropertiesToLoad.Clear();
groupUsers.PropertiesToLoad.Add(attributeWithRange);
SearchResult results = groupUsers.FindOne();
groupUsers.Dispose();
foreach (string res in results.Properties.PropertyNames)
{
System.Diagnostics.Debug.WriteLine(res.ToString());
}
if (results.Properties.Contains(attributeWithRange))
{
foreach (object obj in results.Properties[attributeWithRange])
{
Console.WriteLine(obj.GetType());
if (obj.GetType().Equals(typeof(System.String)))
{
}
else if (obj.GetType().Equals(typeof(System.Int32)))
{
}
row[0] = obj.ToString();
row[1] = groupDN;
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
count++;
}
if (lastQuery)
{
quitLoop = true;
}
}
else
{
lastQuery = true;
}
if (!lastQuery)
{
rangeLow = rangeHigh + 1;
rangeHigh = rangeLow + (rangeStep - 1);
}
// if we are searching for the next set of members 1000-* and it did not return any records count == 0
if (attributeWithRange == String.Format("member;range={0}-*", rangeLow) && count == 0)
{
quitLoop = true;
}
count = 0;
}
while (!quitLoop);
}
catch (Exception ex)
{
log.addTrn("Failure getting AD users in a groups exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
//foreach (object dn in group.properties["member"])
//{
// row[0] = dn.tostring();
// row[1] = groupDN;
// returnvalue.row.add(row);
// row = returnvalue.newrow();
//}
return returnvalue;
}
public DataTable EnumerateGroupsInOUDataTable(string ouDN, ArrayList returnProperties, string table, LogFile log)
{
// note does not handle special/illegal characters for AD
int i;
int count = returnProperties.Count;
DataTable returnvalue = new DataTable();
DataRow row;
// bind to the OU you want to enumerate
try
{
DirectoryEntry deOU = new DirectoryEntry("LDAP://" + ouDN);
// create a directory searcher for that OU
DirectorySearcher dsUsers = new DirectorySearcher(deOU);
// set the filter to get just the users
dsUsers.Filter = "(&(objectClass=group))";
// make it non recursive in depth
dsUsers.SearchScope = SearchScope.OneLevel;
returnvalue.TableName = table;
// add the attributes you want to grab from the search
for (i = 0; i < count; i++)
{
dsUsers.PropertiesToLoad.Add(returnProperties[i].ToString());
returnvalue.Columns.Add(returnProperties[i].ToString());
}
// grab the users and do whatever you need to do with them
dsUsers.PageSize = 1000;
row = returnvalue.NewRow();
foreach (SearchResult oResult in dsUsers.FindAll())
{
//generate the array list with the user sam accounts
for (i = 0; i < count; i++)
{
try
{
row[i] = System.Web.HttpUtility.UrlDecode(oResult.Properties[returnProperties[i].ToString()][0].ToString());
}
catch
{
row[i] = string.Empty;
}
}
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
dsUsers.Dispose();
}
catch (Exception ex)
{
log.addTrn("Failure getting AD groups exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public SqlDataReader QueryInnerJoin(string table1, string table2, string pkey1, string pkey2, ArrayList additionalFields, SqlConnection sqlConn, LogFile log)
{
// additionalFields takes the field names " table.field,"
// Returns data from table1 where the row is in both table 1 and table2
// additional fields table2.data2
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data Data2 | | Table1.ID Table1.DATA Table2.data2
//| 1 a | 1 a e | RETURNED | 1 a e
//| 2 b | null null f | NOT RETURNED |
//| 3 c | 3 null g | RETURNED | 3 c g
//| 4 d | 4 e h | RETURNED | 4 d h
SqlDataReader r;
SqlCommand sqlComm;
string additionalfields = "";
foreach (string key in additionalFields)
{
additionalfields += key;
}
additionalfields = additionalfields.Remove(additionalfields.Length - 2);
if (additionalFields.Count > 0)
{
sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".*, " + additionalfields + " FROM " + table1 + " INNER JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".* FROM " + table1 + " INNER JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
}
try
{
sqlComm.CommandTimeout = 360;
r = sqlComm.ExecuteReader();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
return r;
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return null;
}
public void Levenshtein(string table1, string table2, string pkey1, string pkey2, string newTable, ArrayList selectFields, string targetField, ArrayList valueFields, SqlConnection sqlConn, LogFile log)
{
//SELECT dbo.LEVENSHTEIN((FHC_LDAP_sqlusersTable.givenName + '.' + FHC_LDAP_sqlusersTable.sn), FHC_LDAP_gmailNicknamesTable.nickname),
//(FHC_LDAP_sqlusersTable.givenName + '.' + FHC_LDAP_sqlusersTable.sn) as templateName,
//FHC_LDAP_gmailNicknamesTable.nickname,
//FHC_LDAP_gmailNicknamesTable.Email,
//FHC_LDAP_gmailNicknamesTable.soc_sec
//FROM FHC_LDAP_gmailNicknamesTable LEFT JOIN FHC_LDAP_sqlusersTable ON FHC_LDAP_gmailNicknamesTable.soc_sec = FHC_LDAP_sqlusersTable.sAMAccountName
//group by FHC_LDAP_gmailNicknamesTable.soc_sec, FHC_LDAP_gmailNicknamesTable.Email, FHC_LDAP_gmailNicknamesTable.nickname,
//(FHC_LDAP_sqlusersTable.givenName + '.' + FHC_LDAP_sqlusersTable.sn)
//order by FHC_LDAP_gmailNicknamesTable.soc_sec
//uses levensthein to calculate the closest nickname to the first.last and returns that nickname
//string table1 the table with the new nicknames
//string table2 the table with the original names
//string pkey1
//string pkey2
//string newTable = ; the name of the new Table We want To return
//ArrayList selectFields = new ArrayList( [ email] )List of fields we want returned fields should Be in table1
//string targetField = "nickname"; name of the field we want to be close to ie like a google search result validity must be in table1
//ArrayList valueFields = new ArrayList( [sn, givenName, middleName ] ); lift of fields which are being compared to the targetField to see how close it gets
string complexField = "";
foreach (string key in selectFields)
{
complexField += "(SELECT TOP 1 " + table1 + "." + key;
complexField += " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2;
complexField += " WHERE " + table1 + "." + pkey1 + " = extTable." + pkey1;
complexField += " ORDER BY " + table2 + "." + pkey2 + ",";
foreach (string value in valueFields)
{
// should have first middle last as the arraylist for valueFields1
complexField += " CHARINDEX(" + table2 + "." + value + ", " + table1 + "." + targetField + ") DESC,";
}
// comma remove
complexField = complexField.Remove(complexField.Length - 1);
complexField += ") AS " + key + ",";
}
// comma remove
complexField = complexField.Remove(complexField.Length - 1);
SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT extTable." + pkey1 + ", " + complexField + " INTO " + newTable + " FROM " + table1 + " AS extTable ORDER BY extTable." + pkey1, sqlConn);
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void QueryInnerJoinIntoNewTable(string table1, string table2, string pkey1, string pkey2, string newTable, SqlConnection sqlConn, LogFile log)
{
// Creates a table with data from table1 where the row is in both table 1 and table2
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data | | Table1.ID Table1.DATA ----> Table1.*
//| 1 a | 1 a | RETURNED | 1 a
//| 2 b | null null | NOT RETURNED |
//| 3 c | 3 null | RETURNED | 3 c
//| 4 d | 4 e | RETURNED | 4 d
SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".* INTO " + newTable + " FROM " + table1 + " INNER JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void Mass_Email_Shift(GmailUsers gusersyn, string sourceTable, string targetTable, string sourcePimaryKey, string targetPrimaryKey, ArrayList sourceColumnsAndTable, ArrayList targetColumnsAndTable, string whereClause, SqlConnection sqlConn, LogFile log)
{
// sourceColumnsAndTable and targetColumnsAndTable must have the same number of columns (its a one to one transfer)
// updates the targetColumnsAndTable with the data from the sourceColumnsAndTable
// does not overwrite secondary fields which contain the email domain from the gusersyn
//
// sourceColumnsAndTable expects "table.column"
// targetColumnsAndTable expects "table.column"
// massUpdateSameTable truely a useless flag to overload the function
//
//UPDATE table1
// SET table1.col = table2.col1
//FROM table1 INNER JOIN table2 ON table1.Pkey = table2.Pkey
//WHERE variable clause
//UPDATE a1
//SET a1.e_mail2 = a1.e_mail
//FROM address as a1 INNER JOIN (select * from address as a2 where rtrim(a2.gmail) <> '') as a2 ON a1.soc_sec = a2.soc_sec
string columnValues = "";
int sourceCount = sourceColumnsAndTable.Count;
int i = 0;
for (i = 0; i < sourceCount; i++)
{
columnValues += targetColumnsAndTable[i].ToString() + " = " + sourceColumnsAndTable[i].ToString() + ", ";
}
columnValues = columnValues.Remove(columnValues.Length - 2);
SqlCommand sqlComm;
if (whereClause.Length == 0)
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey + " WHERE " + sourceColumnsAndTable[0] + " not like '%" + gusersyn.Admin_domain + "%' AND " + sourceColumnsAndTable[0] + " <> '' AND " + sourceColumnsAndTable[0] + " <> '?' AND " + sourceColumnsAndTable[0] + " IS NOT NULL ", sqlConn);
}
else
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey + " WHERE " + sourceColumnsAndTable[0] + " not like '%" + gusersyn.Admin_domain + "%' AND " + sourceColumnsAndTable[0] + " <> '' AND " + sourceColumnsAndTable[0] + " <> '?' AND " + sourceColumnsAndTable[0] + " IS NOT NULL AND " + whereClause, sqlConn);
}
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void QueryNotExistsIntoNewTable(string table1, string table2, string newTable, SqlConnection sqlConn, string pkey1, string pkey2, LogFile log)
{
// finds items in table1 who do not exist in table2 and creates a table with the data fields from table 1
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data | | Table1.ID Table1.DATA
//| 1 a | 1 a | NOT RETURNED |
//| 2 b | null null | RETURNED | 2 b
//| 3 c | 3 null | NOT RETURNED |
//| 4 d | 4 e | NOT RETURNED |
// SqlCommand sqlComm = new SqlCommand("Select Table1.* Into #Table3ADTransfer From " + Table1 + " AS Table1, " + Table2 + " AS Table2 Where Table1." + pkey1 + " = Table2." + pkey2 + " And Table2." + pkey2 + " is null", sqlConn);
SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT uptoDate.* INTO " + newTable + " FROM " + table1 + " uptoDate LEFT OUTER JOIN " + table2 + " outofDate ON outofDate." + pkey2 + " = uptoDate." + pkey1 + " WHERE outofDate." + pkey2 + " IS NULL", sqlConn);
// create the command object
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void Mass_Table_update(string sourceTable, string targetTable, string sourcePimaryKey, string targetPrimaryKey, ArrayList sourceColumns, ArrayList targetColumns, string whereClause, SqlConnection sqlConn, LogFile log)
{
// sourceColumns and targetColumns must have the same number of columns (its a one to one transfer)
// updates the targetTable's targetColumns with the data from the sourceTable's sourceColumns
//
//UPDATE table1
// SET table1.col = table2.col1
//FROM table1 INNER JOIN table2 ON table1.Pkey = table2.Pkey
//WHERE variable clause
//UPDATE address
//SET address.gmail = name.gender
//FROM address INNER JOIN name ON address.soc_sec = name.soc_sec
//WHERE address.preferred = 1
string columnValues = "";
int sourceCount = sourceColumns.Count;
int i = 0;
for (i = 0; i < sourceCount; i++)
{
columnValues += targetTable + "." + targetColumns[i].ToString() + " = " + sourceTable + "." + sourceColumns[i].ToString() + ", ";
}
columnValues = columnValues.Remove(columnValues.Length - 2);
SqlCommand sqlComm;
if (whereClause.Length == 0)
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey, sqlConn);
}
else
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey + " WHERE " + whereClause, sqlConn);
}
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
// Log file utilities
public void savelog(LogFile log, ConfigSettings settingsConfig)
{
// create a file stream, where "c:\\testing.txt" is the file path
if (settingsConfig.LogType == "Text File")
{
string datetimeappend = DateTime.Today.Date.ToString() + DateTime.Today.TimeOfDay.ToString();
System.IO.FileStream fs = new System.IO.FileStream(settingsConfig.LogDirectory + datetimeappend, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite);
// create a stream writer
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);
StringBuilder result = new StringBuilder();
foreach (DataColumn dc in log.logTrns.Columns)
{
result.AppendFormat("{0}\t\t\t\t", dc.ColumnName);
}
result.Append("\r\n");
foreach (DataRow dr in log.logTrns.Rows)
{
foreach (DataColumn dc in log.logTrns.Columns)
{
result.AppendFormat("{0}\t\t\t\t", (Convert.IsDBNull(dr[dc.ColumnName]) ? string.Empty : dr[dc.ColumnName].ToString()));
}
result.Append("\r\n");
}
sw.Write(result.ToString());
// flush buffer (so the text really goes into the file)
sw.Flush();
// close stream writer and file
sw.Close();
fs.Close();
}
if (settingsConfig.LogType == "Database")
{
//create sql for log file if it does not exist
// sqlConn must be an open connection
string table = "FHC_LOG_ldap_magic";
DataTable data = new DataTable();
log.initiateTrn();
StringBuilder sqlstring = new StringBuilder();
SqlConnection sqlConn = new SqlConnection("Data Source=" + settingsConfig.LogDB + ";Initial Catalog=" + settingsConfig.LogCatalog + ";Integrated Security=SSPI;Connect Timeout=360;");
sqlConn.Open();
SqlCommand sqlComm;
sqlstring.Append("CREATE TABLE [" + table + "]([Message] [text], [Type] [varchar](50), [Timestamp] [datetime] NULL) ON [PRIMARY]");
sqlComm = new SqlCommand(sqlstring.ToString(), sqlConn);
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
log.addTrn("table created " + table, "Transaction");
}
catch (Exception ex)
{
log.addTrn("Table already exists or Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
//log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString());
}
// copy data into table
try
{
SqlBulkCopy sbc = new SqlBulkCopy(sqlConn);
sbc.DestinationTableName = table;
sbc.WriteToServer(log.logTrns);
sbc.Close();
}
catch (Exception ex)
{
log.addTrn("Failed SQL bulk copy " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
//create datatable with log information
//use append table to sql bulk copy to table
//optionally create blank table and use copy into/merge to get records into existing table
}
}
public void Mass_update_email_field(SqlDataReader users, SqlConnection sqlConn, GmailUsers gusersyn, LogFile log)
{
// string concatenation replaced with stringbuilder due to rumored performance increases
//
/*
UPDATE address
SET e_mail2 = e_mail,
e_mail = 'actual address'
WHERE primary_key = '####' AND where clause
* customized to work with GmailUsers object pulling the email field and primary keys for the transition form there
*/
StringBuilder sqlstring = new StringBuilder();
SqlCommand sqlComm;
try
{
while (users.Read())
{
// now add the data
sqlstring.Remove(0, sqlstring.Length);
sqlstring.Append("UPDATE " + gusersyn.Writeback_table + " ");
if (gusersyn.Writeback_transfer_email_checkbox == true)
{
sqlstring.Append("SET " + gusersyn.Writeback_secondary_email_field + " = " + gusersyn.Writeback_email_field + ",");
sqlstring.Append(gusersyn.Writeback_email_field + " = '" + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + "' ");
}
else
{
sqlstring.Append("SET " + gusersyn.Writeback_email_field + " = '" + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + "' ");
}
sqlstring.Append("WHERE " + gusersyn.Writeback_primary_key + " = '" + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + "' ");
if (gusersyn.Writeback_where_clause.ToString().Trim() != "")
{
sqlstring.Append("AND " + gusersyn.Writeback_where_clause);
}
sqlComm = new SqlCommand(sqlstring.ToString(), sqlConn);
// MessageBox.Show(sqlstring.Length.ToString());
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
log.addTrn("DB email writeback, user " + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + ", email " + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''"), "Transaction");
}
catch (Exception ex)
{
log.addTrn("DB email writeback failure, user " + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + ", email " + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
sqlComm.Dispose();
}
}
catch (Exception ex)
{
log.addTrn("Issue in DB writeback datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void SetAttributeValuesSingleString(string attributeName, string newValue, string objectDn, LogFile log)
{
// objectDn expects full ldap entery LDAP://cn=blah,ou=testing,dc=fabrikam,dc=com
try
{
DirectoryEntry ent = new DirectoryEntry(objectDn);
ent.Properties[attributeName].Value = newValue;
ent.CommitChanges();
log.addTrn("AD set value for field " + attributeName + " for user " + objectDn + " value " + newValue, "Transaction");
ent.Close();
ent.Dispose();
}
catch (Exception ex)
{
log.addTrn("failed AD set value for field " + attributeName + " for user " + objectDn + " value " + newValue + " exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
// possible not in use
public void MoveADObject(string objectLocation, string newLocation, LogFile log)
{
//For brevity, removed existence checks
// EXPECTS FULL Distinguished Name for both variables "LDAP://CN=xxx,DC=xxx,DC=xxx"
try
{
DirectoryEntry eLocation = new DirectoryEntry(objectLocation);
DirectoryEntry nLocation = new DirectoryEntry(newLocation);
string newName = eLocation.Name;
eLocation.MoveTo(nLocation, newName);
nLocation.Close();
eLocation.Close();
}
catch (Exception ex)
{
log.addTrn("failed to move ad object from " + objectLocation + " to " + newLocation + " exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public bool SetUserExpiration(int days, string userDN, LogFile log)
{
try
{
DirectoryEntry usr = new DirectoryEntry(userDN);
Type type = usr.NativeObject.GetType();
Object adsNative = usr.NativeObject;
string formattedDate;
// Calculating the new date
DateTime yesterday = DateTime.Today.AddDays(days);
formattedDate = yesterday.ToString("dd/MM/yyyy");
type.InvokeMember("AccountExpirationDate", BindingFlags.SetProperty, null, adsNative, new object[] { formattedDate });
usr.CommitChanges();
usr.Close();
usr.Dispose();
log.addTrn("User expiration set |" + userDN + "|" + days, "Transaction");
return true;
}
catch (System.DirectoryServices.DirectoryServicesCOMException E)
{
log.addTrn(E.Message.ToString() + " error setting user expiration " + userDN + " days " + days, "Error");
return false;
}
}
public void NicknameUpdate(AppsService service, SqlDataReader nicknames, string usernameFieldName, string userNicknameField, LogFile log)
{
// updates nicknames in gmail from sql data reader containing user ids and nicknames
// finds the user with the ID listed and returns all the nicknames for that user
// iterates trhrough the nicknames and delets all those which do not mathch the nickname from the usernickname field
// if the nickname from the usernickname field does not exist it will be created fro the user listed
//
// usernameFieldName userNicknameField
// ------------------------------------
// ID | Nickname
// ------------------------------------
// SB289312 | test.user
//
//
int i = 0;
int nicknamecount = 0;
bool foundnickname = false;
try
{
while (nicknames.Read())
{
// get all nicknames for user who has the wrong nickname
NicknameFeed userNicknames;
userNicknames = service.RetrieveNicknames(nicknames[usernameFieldName].ToString());
// get the count so we can iterate over them\
nicknamecount = userNicknames.Entries.Count;
// iterate and delete all nicknames that are not equal to the correct nickname
for (i = 0; i < nicknamecount; i++)
{
try
{
NicknameEntry nicknameEntry = userNicknames.Entries[nicknamecount] as NicknameEntry;
if (nicknameEntry.Nickname.Name.ToString() == nicknames[userNicknameField].ToString())
{
foundnickname = true;
}
else
{
service.DeleteNickname(nicknameEntry.Nickname.Name.ToString());
log.addTrn("Deleting user nickname " + nicknameEntry.Nickname.Name.ToString(), "Transaction");
}
}
catch
{
log.addTrn("Error deleting user nickname " + nicknames[userNicknameField].ToString(), "Error");
}
}
// if the nickname is not found create the new nickname
if (foundnickname == false)
{
try
{
service.CreateNickname(nicknames[usernameFieldName].ToString(), nicknames[userNicknameField].ToString());
log.addTrn("Creating user nickname " + nicknames[userNicknameField].ToString() + " for user " + nicknames[usernameFieldName].ToString(), "Transaction");
}
catch
{
log.addTrn("Error adding user nickname " + nicknames[userNicknameField].ToString() + " for user " + nicknames[usernameFieldName].ToString(), "Error");
}
}
// reset all variables
foundnickname = false;
i = 0;
nicknamecount = 0;
}
}
catch (Exception ex)
{
log.addTrn("Issue updating nicknames datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void UpdateGroup(string ouPath, Dictionary<string, string> properties, LogFile log)
{
// otherProperties is a mapping <the key is the active driectory field, and the value is the the value>
// the keys must contain valid AD fields
// the value will relate to the specific key
// needs parent OU present to work
try
{
if (DirectoryEntry.Exists("LDAP://CN=" + System.Web.HttpUtility.UrlEncode(properties["CN"].ToString()).Replace("+", " ").Replace("*", "%2A") + "," + ouPath))
{
DirectoryEntry entry = new DirectoryEntry("LDAP://" + ouPath);
DirectoryEntry group = entry.Children.Find("CN=" + System.Web.HttpUtility.UrlEncode(properties["CN"].ToString()).Replace("+", " ").Replace("*", "%2A"));
foreach (KeyValuePair<string, string> kvp in properties)
{
if (kvp.Key.ToString() == "CN" || kvp.Key.ToString() == "sAMAccountName")
{ }
else
{
group.Properties[kvp.Key.ToString()].Value = System.Web.HttpUtility.UrlEncode(kvp.Value.ToString()).Replace("+", " ").Replace("*", "%2A");
}
}
group.CommitChanges();
group.Close();
group.Dispose();
entry.Close();
entry.Dispose();
log.addTrn("updated group | LDAP://CN=" + System.Web.HttpUtility.UrlEncode(properties["CN"].ToString()).Replace("+", " ").Replace("*", "%2A") + "," + ouPath, "Transaction");
}
else
{
log.addTrn(ouPath + " group does not exist", "Warning");
}
}
catch (Exception ex)
{
log.addTrn(ex.Message.ToString() + "issue updating group LDAP://CN=" + System.Web.HttpUtility.UrlEncode(properties["CN"].ToString()).Replace("+", " ").Replace("*", "%2A") + "," + ouPath + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public SqlDataReader QueryInnerJoin(string table1, string table2, string pkey1, string pkey2, SqlConnection sqlConn, LogFile log)
{
// Returns data from table1 where the row is in both table 1 and table2
//*************************************************************************************************
//| Table1 | Table2 | Returned result
//*************************************************************************************************
//| ID Data | ID Data | | Table1.ID Table1.DATA
//| 1 a | 1 a | RETURNED | 1 a
//| 2 b | null null | NOT RETURNED |
//| 3 c | 3 null | RETURNED | 3 c
//| 4 d | 4 e | RETURNED | 4 d
SqlCommand sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".* FROM " + table1 + " INNER JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
SqlDataReader r;
try
{
sqlComm.CommandTimeout = 360;
r = sqlComm.ExecuteReader();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
return r;
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return null;
}
static void Main(string[] args)
{
Arguments CommandLine = new Arguments(args);
string operation = "";
string file = "";
if (CommandLine["F"] != null)
{
file = CommandLine["F"];
}
else Console.WriteLine("File not defined full path needed -F=c:\\xxx\\yyy\\zzz\\file.ldap warning there is no sanity check on the path");
operation = CommandLine["O"];
if (CommandLine["O"] != null)
{
if (CommandLine["O"] != "users" || CommandLine["O"] != "groups" || CommandLine["O"] != "OUmap" || CommandLine["O"] != "gmail")
{
Console.WriteLine("Operation not defined -O=users -O=groups -O=OUmap -O=gmail");
}
else
{
operation = CommandLine["O"];
}
}
// Check for tests first
// If tests are desired, skip normal opretaions
if( CommandLine["T"] != null ){
// Collection of Class objects
// Each class needs to have a runTests() method
GroupSynch groupconfig = new GroupSynch();
UserSynch userconfig = new UserSynch();
GmailUsers guserconfig = new GmailUsers();
executionOrder execution = new executionOrder();
UserStateChange usermapping = new UserStateChange();
ConfigSettings settingsconfig = new ConfigSettings();
utils.ToolSet tools = new ToolSet();
LogFile log = new LogFile();
ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
StopWatch timer = new StopWatch();
log.initiateTrn();
// Sift through are different tests
switch(CommandLine["T"])
{
// Run tests specific to users sync
case "users":
// userconfig.runTests();
break;
// Run tests specific to group sync
case "groups":
// groupconfig.runTests();
break;
// Run tests specific to OUmap sync
case "OUmap":
// Not sure how this operation is used
break;
// Run tests specifc to gmail sync
case "gmail":
// guserconfig.runTests();
break;
// Run all tests
default:
case "all":
// userconfig.runTests();
// groupconfig.runTests();
// guserconfig.runTests();
break;
}
}
// MessageBox.Show("operation is " + operation + " file is " + file);
else if (file != "" && operation != "")
{
// woot halleluijah we have input from the user time to execute
//duplicate the gui fucntionality in cmd line
// we won't check this input cause its from a really smart system administrator
// just in case file expects a full path
// c:\blah\blah\blah.ext
// valid oprations are
// users groups OUmap gmail
// create objects to hold save data
GroupSynch groupconfig = new GroupSynch();
UserSynch userconfig = new UserSynch();
GmailUsers guserconfig = new GmailUsers();
executionOrder execution = new executionOrder();
UserStateChange usermapping = new UserStateChange();
ConfigSettings settingsconfig = new ConfigSettings();
utils.ToolSet tools = new ToolSet();
LogFile log = new LogFile();
ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
StopWatch timer = new StopWatch();
log.initiateTrn();
// perform operations based on the data input from the user fro groups users, OU's and gmail
if (operation == "group")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
// Load values into text boxes
// reload properties each time as they are overwritten with the combo object trigger events
groupconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start Groups Syncs", "Info");
timer.Start();
groupSyncr.ExecuteGroupSync(groupconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Groups " + groupconfig.Group_Append + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
log.errors.Add("Failed to load save file");
}
//// save log to disk
//SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
//saveFileDialog1.FilterIndex = 2;
//saveFileDialog1.RestoreDirectory = true;
//if (saveFileDialog1.ShowDialog() == DialogResult.OK)
//{
// // create a file stream, where "c:\\testing.txt" is the file path
// System.IO.FileStream fs = new System.IO.FileStream(saveFileDialog1.FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite);
// // create a stream writer
// System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);
// // write to file (buffer), where textbox1 is your text box
// sw.WriteLine("{0}", result2);
// sw.WriteLine("{0}", result);
// // flush buffer (so the text really goes into the file)
// sw.Flush();
// // close stream writer and file
// sw.Close();
// fs.Close();
//}
}
if (operation == "users")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
DataTable customs = new DataTable();
BindingSource bs = new BindingSource();
//OpenFileDialog openFileDialog1 = new OpenFileDialog();
//openFileDialog1.InitialDirectory = "c:\\";
//openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
//openFileDialog1.FilterIndex = 2;
//openFileDialog1.RestoreDirectory = true;
//if (openFileDialog1.ShowDialog() == DialogResult.OK)
//{
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
userconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start User Synch", "Info");
timer.Start();
groupSyncr.ExecuteUserSync(userconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Users " + userconfig.BaseUserOU + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
Console.Write("Failed to load save file");
}
}
if (operation == "gmail")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
BindingSource bs = new BindingSource();
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
guserconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start Gmail Synch", "Info");
timer.Start();
gmailSyncr.EmailUsersSync(guserconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Gmail " + guserconfig.Admin_domain + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
Console.Write("Failed to load save file");
}
}
}
else
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
public void DropTable(string table, SqlConnection sqlConn, LogFile log)
{
SqlCommand sqlComm;
string sqlstring = "DROP TABLE " + table;
sqlComm = new SqlCommand(sqlstring.ToString(), sqlConn);
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
log.addTrn("table dropped " + table, "Transaction");
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}