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());
}
}
private void group_execute_now_Click(object sender, EventArgs e)
{
StopWatch timer = new StopWatch();
timer.Start();
log.addTrn("Group Synch Start ", "Info");
groupSyncr.ExecuteGroupSync(groupconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Group " + groupconfig.Group_Append + " Synch Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Info");
tools.savelog(log, settingsconfig);
}
private void users_Execute_Click(object sender, EventArgs e)
{
//int i;
StopWatch timer = new StopWatch();
log.addTrn("Start User Synch", "Info");
timer.Start();
groupSyncr.ExecuteUserSync(userconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Users " + userconfig.BaseUserOU + " Synch Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Info");
tools.savelog(log, settingsconfig);
}
private void mail_execute_Click(object sender, EventArgs e)
{
StopWatch timer = new StopWatch();
timer.Start();
log.addTrn("Start Gmail Synch", "Info");
gmailSyncr.EmailUsersSync(guserconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Gmail " + guserconfig.Admin_domain + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Info");
tools.savelog(log, settingsconfig);
}
public void ExecuteUserSync(UserSynch usersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
{
int i;
ArrayList debugList = new ArrayList();
StopWatch time = new StopWatch();
string baseOU = usersyn.BaseUserOU;
string DC = baseOU.Substring(baseOU.IndexOf("DC"));
string sqlForCustomFields = "";
// Table string place holders
string sqlUsersTable = "#FHC_USERS_SQLusersTable";
string adUsersTable = "#FHC_USERS_ADusersTable";
SqlDataReader add;
SqlDataReader delete;
SqlDataReader update;
SearchScope scope = SearchScope.OneLevel;
ArrayList completeSqlKeys = new ArrayList();
ArrayList completeADKeys = new ArrayList();
ArrayList adUpdateKeys = new ArrayList();
ArrayList sqlUpdateKeys = new ArrayList();
ArrayList extraFieldsToReturn = new ArrayList();
ArrayList fields = new ArrayList();
Dictionary<string, string> userObject = new Dictionary<string, string>();
SqlConnection sqlConn = new SqlConnection("Data Source=" + usersyn.DataServer + ";Initial Catalog=" + usersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360");
if (settingsConfig.TempTables == true)
{
sqlUsersTable = "#FHC_USERS_SQLusersTable";
adUsersTable = "#FHC_USERS_ADusersTable";
}
else
{
sqlUsersTable = "FHC_USERS_SQLusersTable";
adUsersTable = "FHC_USERS_ADusersTable";
}
//SqlDataReader sqlusers;
SqlCommand sqlComm;
SqlCommand sqlComm2;
string recordCount = "";
DataTable adUsers = new DataTable();
sqlConn.Open();
//housecleaning
log.addTrn("Cleaning out tables", "Info");
if (settingsConfig.TempTables == false)
{
tools.DropTable(sqlUsersTable, sqlConn, log);
tools.DropTable(adUsersTable, sqlConn, log);
}
//if were only updating it doesnt matter where we want ot put new users
if (usersyn.UpdateOnly == false)
{
log.addTrn("Initial setup of OUs and Groups", "Info");
// create initial ou's; will log a warning out if they already exist
tools.CreateOURecursive(usersyn.BaseUserOU, log);
tools.CreateOURecursive(usersyn.UserHoldingTank, log);
// setup extentions for the user accounts to go in to the right ou's
userObject.Add("sAMAccountName", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
userObject.Add("CN", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
userObject.Add("description", "Universal Group For Users");
// creates the group if it does not exist
tools.CreateGroup(usersyn.UniversalGroup.Remove(0, usersyn.UniversalGroup.IndexOf(",") + 1), userObject, log);
}
// need to add this field first to use as a primary key when checking for existance in AD
completeSqlKeys.Add("sAMAccountName");
completeSqlKeys.Add("CN");
completeSqlKeys.Add("sn");
completeSqlKeys.Add("givenName");
completeSqlKeys.Add("homePhone");
completeSqlKeys.Add("st");
completeSqlKeys.Add("streetAddress");
completeSqlKeys.Add("l");
completeSqlKeys.Add("postalCode");
// ?????? MIGHT NOT BE USED
// Lets make the SQL fields to check for update
sqlUpdateKeys.Add("sn");
sqlUpdateKeys.Add("givenName");
sqlUpdateKeys.Add("homePhone");
sqlUpdateKeys.Add("st");
sqlUpdateKeys.Add("streetAddress");
sqlUpdateKeys.Add("l");
sqlUpdateKeys.Add("postalCode");
// Lets make the Active Directory Keys as well
completeADKeys.Add("sAMAccountName");
completeADKeys.Add("CN");
completeADKeys.Add("sn");
completeADKeys.Add("givenName");
completeADKeys.Add("homePhone");
completeADKeys.Add("st");
completeADKeys.Add("streetAddress");
completeADKeys.Add("l");
completeADKeys.Add("postalCode");
completeADKeys.Add("distinguishedName");
// Lets make the Active Directory fields to check for update
adUpdateKeys.Add("sn");
adUpdateKeys.Add("givenName");
adUpdateKeys.Add("homePhone");
adUpdateKeys.Add("st");
adUpdateKeys.Add("streetAddress");
adUpdateKeys.Add("l");
adUpdateKeys.Add("postalCode");
//build custom keys
for (i = 0; i < usersyn.UserCustoms.Rows.Count; i++)
{
// build keys to pull back from SQL
// as well keys to check if these fields need updating
completeSqlKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
sqlUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
// build keys to pull back from AD
// as well keys to check if these fields need updating
completeADKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
adUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
// build fields to pull back from SQL
//create props from rows in usercustoms datatable our column names match the appropriate fields in AD and SQL
if (usersyn.UserCustoms.Rows[i][1].ToString() != "Static Value")
{
sqlForCustomFields += ", RTRIM(" + usersyn.UserCustoms.Rows[i][1].ToString() + ") AS " + usersyn.UserCustoms.Rows[i][0].ToString();
}
// static fields get static values for the table to get updated
else
{
sqlForCustomFields += ", '" + usersyn.UserCustoms.Rows[i][2].ToString() + "' AS " + usersyn.UserCustoms.Rows[i][0].ToString();
}
}
// grab users data from sql
log.addTrn("Get users from SQL tables", "Info");
if (usersyn.User_where == "")
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
", RTRIM(" + usersyn.User_CN + ") AS CN" +
", RTRIM(" + usersyn.User_Lname + ") AS sn" +
", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
", RTRIM(" + usersyn.User_State + ") AS st" +
", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
//", RTRIM(" + usersyn.User_mail + ") AS mail" +
", RTRIM(" + usersyn.User_city + ") AS l" +
", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
", RTRIM(" + usersyn.User_password + ") AS password" +
sqlForCustomFields +
" INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
", RTRIM(" + usersyn.User_CN + ") AS CN" +
", RTRIM(" + usersyn.User_Lname + ") AS sn" +
", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
", RTRIM(" + usersyn.User_State + ") AS st" +
", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
//", RTRIM(" + usersyn.User_mail + ") AS mail" +
", RTRIM(" + usersyn.User_city + ") AS l" +
", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
", RTRIM(" + usersyn.User_password + ") AS password" +
sqlForCustomFields +
" INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable +
" WHERE " + usersyn.User_where, 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");
throw;
}
if (usersyn.SearchScope == "Subtree")
{
scope = SearchScope.Subtree;
}
// go grab all the users from AD
log.addTrn("Get users from active directory", "Info");
adUsers = tools.EnumerateUsersInOUDataTable(usersyn.BaseUserOU, completeADKeys, adUsersTable, scope, log);
if (adUsers.Rows.Count > 0)
{
// make the temp table for ou comparisons
tools.Create_Table(adUsers, adUsersTable, sqlConn, log);
// Quick check to stop adding if the update only box is checked
if (usersyn.UpdateOnly == false)
{
// compare query for the add/remove
log.addTrn("Query to find users to add", "Info");
add = tools.QueryNotExistsByPkey(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);
// actual add stuff
log.addTrn("Adding users", "Info");
tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
add.Close();
sqlComm2 = new SqlCommand("select count(sAMAccountName) FROM " + sqlUsersTable, sqlConn);
sqlComm2.CommandTimeout = 360;
recordCount = sqlComm2.ExecuteScalar().ToString();
sqlComm2.Dispose();
if (recordCount != "0")
{
// compare query to find records which need deletion
log.addTrn("Query to find users to delete", "Info");
delete = tools.QueryNotExistsByPkey(adUsersTable, sqlUsersTable, sqlConn, usersyn.User_sAMAccount, completeADKeys[0].ToString(), log);
// delete users in AD
log.addTrn("Deleting users", "Info");
try
{
while (delete.Read())
{
tools.DeleteUserAccount((string)delete["distinguishedname"], log);
// log.addTrn("User removed ;" + (string)delete[adUpdateKeys[1].ToString()].ToString().Trim());
}
}
catch (Exception ex)
{
log.addTrn("Issue deleting AD users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
delete.Close();
}
}
// add the extra fields in form ".field ,"
extraFieldsToReturn.Add(adUsersTable + ".distinguishedname ,");
log.addTrn("Query to find users to update", "Info");
update = tools.CheckUpdate(sqlUsersTable, adUsersTable, "sAMAccountName", "sAMAccountName", sqlUpdateKeys, adUpdateKeys, extraFieldsToReturn, 1, sqlConn, log);
// update users in ad
// last record which matches the primary key is the one which gets inserted into the database
log.addTrn("Updating users", "Info");
tools.UpdateUsers(update, DC, usersyn, log);
update.Close();
}
// did not find any records in AD we are only adding users
else
{
// and we are not updating users
if (usersyn.UpdateOnly == false)
{
// add the users without doing additional checks
tools.Create_Table(adUsers, adUsersTable, sqlConn, log);
log.addTrn("Query to find users to add", "Info");
add = tools.QueryNotExistsAllFields(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);
log.addTrn("Add all users", "Info");
tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
add.Close();
}
}
sqlConn.Close();
}
public void ExecuteGroupSync(GroupSynch groupsyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
{
StopWatch time = new StopWatch();
string groupapp = groupsyn.Group_Append;
string groupOU = groupsyn.BaseGroupOU;
string sAMAccountName = "";
string description = "";
int count = 0;
string sqlgroupsTable = "#FHC_GROUPS_SQLgroupsTable";
string adGroupsTable = "#FHC_GROUPS_ADgroupsTable";
if (settingsConfig.TempTables == true)
{
sqlgroupsTable = "#FHC_GROUPS_SQLgroupsTable";
adGroupsTable = "#FHC_GROUPS_ADgroupsTable";
}
else
{
sqlgroupsTable = "FHC_GROUPS_SQLgroupsTable" + groupsyn.Group_Append;
adGroupsTable = "FHC_GROUPS_ADgroupsTable" + groupsyn.Group_Append;
}
string dc = groupOU.Substring(groupOU.IndexOf("DC"));
string groupDN;
string groupsTable;
SqlDataReader add;
SqlDataReader delete;
SqlDataReader update;
ArrayList adUpdateKeys = new ArrayList();
ArrayList sqlUpdateKeys = new ArrayList();
ArrayList fields = new ArrayList();
DataTable groupsDataTable = new DataTable();
Dictionary<string, string> groupObject = new Dictionary<string, string>();
SqlConnection sqlConn = new SqlConnection("Data Source=" + groupsyn.DataServer + ";Initial Catalog=" + groupsyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360");
sqlConn.Open();
// Setup the OU for the program
log.addTrn("Setup OU for the groups", "Info");
tools.CreateOURecursive("OU=" + groupapp + "," + groupOU, log);
// A little house cleaning to empty out tables
if (settingsConfig.TempTables == false)
{
log.addTrn("Clear out tables for use", "Info");
tools.DropTable(adGroupsTable, sqlConn, log);
tools.DropTable(sqlgroupsTable, sqlConn, log);
}
// grab list of groups from SQL insert into a temp table
log.addTrn("Get groups from SQL", "Info");
SqlCommand sqlComm = new SqlCommand();
if (groupsyn.Group_where == "")
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + groupsyn.Group_sAMAccount + ") AS " + groupsyn.Group_sAMAccount + ", RTRIM(" + groupsyn.Group_CN + ") + '" + groupapp + "' AS " + groupsyn.Group_CN + " INTO " + sqlgroupsTable + " FROM " + groupsyn.Group_dbTable + " WHERE " + groupsyn.Group_sAMAccount + " IS NOT NULL ORDER BY " + groupsyn.Group_CN, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + groupsyn.Group_sAMAccount + ") AS " + groupsyn.Group_sAMAccount + ", RTRIM(" + groupsyn.Group_CN + ") + '" + groupapp + "' AS " + groupsyn.Group_CN + " INTO " + sqlgroupsTable + " FROM " + groupsyn.Group_dbTable + " WHERE " + groupsyn.Group_sAMAccount + " IS NOT NULL AND " + groupsyn.Group_where + " ORDER BY " + groupsyn.Group_CN, 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");
throw;
}
// generate a list of fields to ask from AD
adUpdateKeys.Add("description");
adUpdateKeys.Add("CN");
// grab groups from AD
log.addTrn("Get groups from AD", "Info");
groupsDataTable = tools.EnumerateGroupsInOUDataTable("OU=" + groupapp + "," + groupOU, adUpdateKeys, adGroupsTable, log);
// insert groups from AD into a temp table
if (groupsDataTable.Rows.Count > 0)
{
groupsTable = tools.Create_Table(groupsDataTable, adGroupsTable, sqlConn, log);
//Find groups that we need to create
log.addTrn("Query to find groups that need to be created", "Info");
add = tools.QueryNotExistsAllFields(sqlgroupsTable, groupsTable, sqlConn, groupsyn.Group_CN, adUpdateKeys[1].ToString(), log);
// Create groups
log.addTrn("Creating groups", "Info");
while (add.Read())
{
//i++;
sAMAccountName = (string)add[1].ToString().Trim();
description = (string)add[0].ToString().Trim();
groupObject.Add("sAMAccountName", sAMAccountName);
groupObject.Add("CN", sAMAccountName);
groupObject.Add("description", description);
tools.CreateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
groupObject.Clear();
}
add.Close();
//time.Start();
log.addTrn("Query to find groups to delete", "Info");
delete = tools.QueryNotExistsAllFields(groupsTable, sqlgroupsTable, sqlConn, adUpdateKeys[1].ToString(), groupsyn.Group_CN, log);
// delete groups in AD
// i = 0;
log.addTrn("Deleting groups", "Info");
while (delete.Read())
{
tools.DeleteGroup("OU=" + groupapp + "," + groupOU, (string)delete[adUpdateKeys[1].ToString()].ToString().Trim(), log);
}
delete.Close();
// Get columns from sqlgroupsTable temp table in database get columns deprcated in favor of manual building due to cannot figure out how to get the columns of a temporary table
// SQLupdateKeys = tools.GetColumns(groupsyn.DataServer, groupsyn.DBCatalog, sqlgroupsTable);
// make the list of fields for the sql to check when updating note these fields must be in the same order as the AD update keys
sqlUpdateKeys.Add(groupsyn.Group_sAMAccount);
sqlUpdateKeys.Add(groupsyn.Group_CN);
// update assumes the both ADupdateKeys and SQLupdateKeys have the same fields, listed in the same order check call to EnumerateGroupsInOU if this is wrong should be sAMAccountName, CN matching the SQL order
log.addTrn("Query to find groups which need to be updated", "Info");
update = tools.CheckUpdate(sqlgroupsTable, groupsTable, groupsyn.Group_CN, adUpdateKeys[1].ToString(), sqlUpdateKeys, adUpdateKeys, sqlConn, log);
// update groups in ad
// last record which matches the primary key is the one which gets inserted into the database
log.addTrn("Updating groups", "Info");
while (update.Read())
{
// any duplicate records will attempt to be updated if slow runtimes are a problem this might be an issue
// i++;
sAMAccountName = (string)update[1].ToString().Trim();
description = (string)update[0].ToString().Trim();
groupObject.Add("sAMAccountName", sAMAccountName);
groupObject.Add("CN", sAMAccountName);
groupObject.Add("description", description);
if (tools.Exists("CN=" + groupObject["CN"] + ", OU=" + groupapp + "," + groupOU) == true)
{
// group exists in place just needs updating
tools.UpdateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
// log.addTrn("Group update ; " + sAMAccountName + ",OU=" + groupapp + "," + groupOU + ";" + description);
}
else
{
// find it its on the server somewhere we will log the exception
groupDN = tools.GetObjectDistinguishedName(objectClass.group, returnType.distinguishedName, groupObject["CN"], dc, log);
// what if user is disabled will user mapping handle it?
// groups needs to be moved and updated
// tools.MoveADObject(groupDN, "LDAP://OU=" + groupapp + ',' + groupOU);
// tools.UpdateGroup("OU=" + groupapp + "," + groupOU, groupObject);
log.addTrn("Group cannot be updated user probabally should be in ; " + "OU=" + groupapp + "," + groupOU + " ; but was found in ; " + groupDN, "Error");
}
groupObject.Clear();
}
update.Close();
}
// we didn't find any records in AD so there is no need for the Update or delete logic to run
else
{
log.addTrn("Query to get list of groups to add", "Info");
sqlComm = new SqlCommand("SELECT * FROM " + sqlgroupsTable, sqlConn);
try
{
sqlComm.CommandTimeout = 360;
add = sqlComm.ExecuteReader();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
log.addTrn("Adding groups", "Info");
while (add.Read())
{
//i++;
groupObject.Add("sAMAccountName", (string)add[1]);
groupObject.Add("CN", (string)add[1]);
groupObject.Add("description", (string)add[0]);
tools.CreateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
groupObject.Clear();
}
add.Close();
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
// users section
string sqlgroupMembersTable = "#FHC_GROUPS_sqlusersTable";
string ADgroupMembersTable = "#FHC_GROUPS_ADusersTable";
if (settingsConfig.TempTables == true)
{
sqlgroupMembersTable = "#FCH_GROUPS_sqlusersTable";
ADgroupMembersTable = "#FHC_GROUPS_ADusersTable";
}
else
{
sqlgroupMembersTable = "FHC_GROUPS_sqlusersTable";
ADgroupMembersTable = "FHC_GROUPS_ADusersTable";
}
SqlDataReader sqlgroups;
DataTable ADusers = new DataTable();
// A little house cleaning to empty out tables
log.addTrn("Clear out tables for usage", "Info");
if (settingsConfig.TempTables == false)
{
tools.DropTable(sqlgroupMembersTable, sqlConn, log);
tools.DropTable(ADgroupMembersTable, sqlConn, log);
}
// grab users data from sql
log.addTrn("Get users from SQL", "Info");
if (groupsyn.User_where == "")
{
sqlComm = new SqlCommand("SELECT DISTINCT 'CN=' + RTRIM(" + groupsyn.User_sAMAccount + ") + '," + groupsyn.BaseUserOU + "' AS " + groupsyn.User_sAMAccount + ", RTRIM(" + groupsyn.User_Group_Reference + ") + '" + groupapp + "' AS " + groupsyn.User_Group_Reference + " INTO " + sqlgroupMembersTable + " FROM " + groupsyn.User_dbTable, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT 'CN=' + RTRIM(" + groupsyn.User_sAMAccount + ") + '," + groupsyn.BaseUserOU + "' AS " + groupsyn.User_sAMAccount + ", RTRIM(" + groupsyn.User_Group_Reference + ") + '" + groupapp + "' AS " + groupsyn.User_Group_Reference + " INTO " + sqlgroupMembersTable + " FROM " + groupsyn.User_dbTable + " WHERE " + groupsyn.User_where, 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");
}
// populate datatable with users from AD groups by looping thru the list of groups from SQL and loading the cross referenced AD group members
log.addTrn("Get users from AD by looping through groups from SQL and appending results to a table", "Info");
sqlComm = new SqlCommand("SELECT " + groupsyn.Group_CN + " FROM " + sqlgroupsTable, sqlConn);
try
{
ArrayList sqlgroupsStr = new ArrayList();
sqlComm.CommandTimeout = 360;
sqlgroups = sqlComm.ExecuteReader();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
DataTable currentOu = new DataTable();
while (sqlgroups.Read())
{
sqlgroupsStr.Add((string)sqlgroups[0]);
}
sqlgroups.Close();
count = 0;
foreach (string key in sqlgroupsStr)
{
ADusers = tools.EnumerateUsersInGroupDataTable(key, ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log);
if (count == 0)
{
// make the temp table for ou comparisons the datatable must have somethign in it to make it
tools.Create_Table(ADusers, ADgroupMembersTable, sqlConn, log);
}
else
{
// table is already made now we need to only add to it
tools.Append_to_Table(ADusers, ADgroupMembersTable, sqlConn, log);
}
count++;
}
// hopefully merge acts as an append
// ADusers.Merge(tools.EnumerateUsersInGroupDataTable((string)sqlgroups[0], ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log));
// currentOu = tools.EnumerateUsersInGroupDataTable( , ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log);
// tools.Append_to_Table(currentOu, ADgroupMembersTable, sqlConn, log);
}
catch (Exception ex)
{
log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
// compare and add/remove
log.addTrn("Query to find the users to add ", "Info");
add = tools.QueryNotExistsAllFields(sqlgroupMembersTable, ADgroupMembersTable, sqlConn, groupsyn.User_sAMAccount, ADusers.Columns[0].ColumnName, log);
try
{
while (add.Read())
{
tools.AddUserToGroup((string)add[0], "CN=" + (string)add[1] + ",OU=" + groupapp + "," + groupOU, false, dc, log);
}
}
catch (Exception ex)
{
log.addTrn("Issue adding group datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
groupObject.Clear();
add.Close();
SqlCommand sqlComm2 = new SqlCommand();
string recordCount = "";
sqlComm2 = new SqlCommand("select count(" + groupsyn.Group_CN + ") FROM " + sqlgroupMembersTable, sqlConn);
sqlComm2.CommandTimeout = 360;
recordCount = sqlComm2.ExecuteScalar().ToString();
sqlComm2.Dispose();
if (recordCount != "0")
{
// setup update keys
adUpdateKeys.Clear();
sqlUpdateKeys.Clear();
adUpdateKeys.Add(ADusers.Columns[1].ColumnName);
adUpdateKeys.Add(ADusers.Columns[0].ColumnName);
sqlUpdateKeys.Add(ADusers.Columns[1].ColumnName);
sqlUpdateKeys.Add(ADusers.Columns[0].ColumnName);
// get list of keys which have differed. We will delete them and then next time they will be readded as the correct key\
// users which need to be updated just get deleted and recreadted later where they need to be
log.addTrn("Query to see which users need to be deleted", "Info");
delete = tools.CheckUpdate( ADgroupMembersTable, sqlgroupMembersTable, ADusers.Columns[0].ColumnName, groupsyn.User_sAMAccount, adUpdateKeys, sqlUpdateKeys, sqlConn, log);
// delete = tools.QueryNotExistsAllFields(ADgroupMembersTable, sqlgroupMembersTable, sqlConn, ADusers.Columns[1].ColumnName, groupsyn.User_Group_Reference, log);
// delete groups in AD
log.addTrn("Deleteing users", "Info");
while (delete.Read())
{
tools.RemoveUserFromGroup((string)delete[1], "CN=" + (string)delete[0] + ",OU=" + groupapp + "," + groupOU, log);
}
delete.Close();
}
sqlConn.Close();
}
