/// <summary> /// Gets a list of mailbox sizes Exchange /// </summary> /// <returns></returns> public List<MailboxUser> Get_MailboxSizes() { PowerShell powershell = null; try { // DEBUG logger.Debug("Retrieving a list of mailbox users from the SQL database..."); // Start clock Stopwatch stopwatch = Stopwatch.StartNew(); // Get a list of users from the database List<ADUser> allUsers = DbSql.Get_Users(); // Our return object of information List<MailboxUser> users = new List<MailboxUser>(); // Now loop through the databases and query the information foreach (ADUser user in allUsers) { if (user.MailboxPlanID > 0) { // DEBUG logger.Debug("Retrieving mailbox statistics for user " + user); // Our MailboxUser object to store this users information MailboxUser currentUser = new MailboxUser(); try { // Run commands powershell = PowerShell.Create(); powershell.Runspace = runspace; // Get Databases PSCommand cmd = new PSCommand(); cmd.AddCommand("Get-MailboxStatistics"); cmd.AddParameter("Identity", user.UserPrincipalName); cmd.AddParameter("DomainController", this.domainController); powershell.Commands = cmd; // Now read the returned values Collection<PSObject> foundStatistics = powershell.Invoke(); foreach (PSObject o in foundStatistics) { currentUser.UserPrincipalName = user.UserPrincipalName; currentUser.ItemCount = int.Parse(o.Members["ItemCount"].Value.ToString(), CultureInfo.InvariantCulture); currentUser.DeletedItemCount = int.Parse(o.Members["DeletedItemCount"].Value.ToString(), CultureInfo.InvariantCulture); currentUser.TotalItemSize = o.Members["TotalItemSize"].Value.ToString(); currentUser.TotalDeletedItemSize = o.Members["TotalDeletedItemSize"].Value.ToString(); currentUser.Database = o.Members["Database"].Value.ToString(); currentUser.MailboxDataRetrieved = DateTime.Now; } // Log the powershell commands LogPowershellCommands(ref powershell); // Find all the errors if (powershell.HadErrors) { // Log all errors detected foreach (ErrorRecord err in powershell.Streams.Error) { logger.Error("Error getting mailbox size for " + user, err.Exception); // Compile message StringBuilder sb = new StringBuilder(); sb.AppendLine("Failed to get mailbox size for: " + user); sb.AppendLine(""); sb.AppendLine("Recommended Action:"); sb.AppendLine("This could be because the user no longer exists in Active Directory but still exists in the database."); sb.AppendLine("If that is the case simply delete the user from CloudPanel. If the issues stays the same please contact support."); sb.AppendLine(""); sb.AppendLine("Error:"); sb.AppendLine(err.Exception.ToString()); // Send message Support.SendEmailMessage("Failed to get mailbox size for: " + user, sb.ToString()); } // Log all warnings detected foreach (WarningRecord err in powershell.Streams.Warning) { logger.Error("Warning getting mailbox size for " + user + ": " + err.Message); } } else { logger.Info("Successfully retrieved mailbox size information for " + currentUser.UserPrincipalName); users.Add(currentUser); } } catch (Exception ex) { logger.Fatal("Failed to retrieve mailbox size for " + user, ex); } } } // Stop the clock stopwatch.Stop(); // Log the success logger.Info("Successfully retrieved a complete list of mailbox sizes from Exchange."); // Return values return users; } catch (Exception ex) { throw; } finally { if (powershell != null) powershell.Dispose(); } }
/// <summary> /// Gets a list of users and information from SQL for a specific company /// (For the mailbox users display) /// </summary> /// <param name="companyCode"></param> /// <returns></returns> public static List<MailboxUser> GetMailboxUsers(string companyCode) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"SELECT u.ID, u.UserGuid, u.DistinguishedName, u.UserPrincipalName, u.Department, u.DisplayName, u.Email, u.AdditionalMB, u.sAMAccountName, p.MailboxPlanName, p.MailboxSizeMB, p.MaxMailboxSizeMB, u.ActiveSyncPlan, (SELECT TOP 1 TotalItemSizeInKB FROM SvcMailboxSizes WHERE UserPrincipalName=u.UserPrincipalName ORDER BY Retrieved DESC) AS TotalItemSizeInKB FROM Users u INNER JOIN Plans_ExchangeMailbox p ON p.MailboxPlanID = u.MailboxPlan WHERE u.CompanyCode=@CompanyCode AND u.MailboxPlan > 0 ORDER BY u.DisplayName", sql); // Initialize list List<MailboxUser> users = new List<MailboxUser>(); try { // Add parameters cmd.Parameters.AddWithValue("@CompanyCode", companyCode); // Open Connection sql.Open(); // Read all data SqlDataReader r = cmd.ExecuteReader(); if (r.HasRows) { while (r.Read()) { MailboxUser tmp = new MailboxUser(); tmp.UserID = int.Parse(r["ID"].ToString()); tmp.UserGuid = Guid.Parse(r["UserGuid"].ToString()); tmp.UserPrincipalName = r["UserPrincipalName"].ToString(); tmp.MailboxPlanName = r["MailboxPlanName"].ToString(); tmp.DisplayName = r["DisplayName"].ToString(); if (r["sAMAccountName"] != DBNull.Value) tmp.SamAccountName = r["sAMAccountName"].ToString(); else tmp.SamAccountName = ""; if (r["DistinguishedName"] != DBNull.Value) tmp.DistinguishedName = r["DistinguishedName"].ToString().ToUpper(); // Upper case to compare with other lists else tmp.DistinguishedName = ""; if (r["Department"] == DBNull.Value) tmp.Department = ""; else tmp.Department = r["Department"].ToString(); if (r["AdditionalMB"] != DBNull.Value) tmp.AdditionalMB = int.Parse(r["AdditionalMB"].ToString()); else tmp.AdditionalMB = 0; if (r["Email"] != DBNull.Value) tmp.PrimarySmtpAddress = r["Email"].ToString(); else tmp.PrimarySmtpAddress = ""; // Calcuate the current mailbox size int current = int.Parse(r["MailboxSizeMB"].ToString()); int total = current + tmp.AdditionalMB; decimal totalInGB = (decimal)total / 1024; //tmp.MailboxSizeFormatted = string.Format("{0:##.##}{1}", totalInGB, "GB"); // Get the Exchange mailbox size information if (r["TotalItemSizeInKB"] != DBNull.Value) tmp.TotalItemSizeInKB = FormatBytes(r["TotalItemSizeInKB"].ToString()); else tmp.TotalItemSizeInKB = "Unknown"; // Add to collections users.Add(tmp); } } // Close and dispose r.Close(); r.Dispose(); // Return list return users; } catch (Exception) { throw; } finally { cmd.Dispose(); sql.Dispose(); } }
/// <summary> /// /// </summary> /// <param name="user"></param> /// <param name="plan"></param> public static void UpdateUserMailbox(MailboxUser user, MailboxPlan plan) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"UPDATE Users SET MailboxPlan=@MailboxPlan, Email=@Email, AdditionalMB=@AdditionalMB WHERE UserPrincipalName=@UserPrincipalName", sql); try { // Check if the ActiveSync plan is not null if (!string.IsNullOrEmpty(user.ActiveSyncMailboxPolicy)) { cmd.CommandText = @"UPDATE Users SET MailboxPlan=@MailboxPlan, Email=@Email, AdditionalMB=@AdditionalMB, ActiveSyncPlan=(SELECT TOP 1 ASID FROM Plans_ExchangeActiveSync WHERE DisplayName=@ActiveSyncPlan) WHERE UserPrincipalName=@UserPrincipalName"; cmd.Parameters.AddWithValue("@ActiveSyncPlan", user.ActiveSyncMailboxPolicy); } else cmd.Parameters.AddWithValue("@ActiveSyncPlan", DBNull.Value); // Add our parameters cmd.Parameters.AddWithValue("@MailboxPlan", plan.PlanID); cmd.Parameters.AddWithValue("@Email", user.PrimarySmtpAddress); cmd.Parameters.AddWithValue("@AdditionalMB", plan.AdditionalMBAdded); cmd.Parameters.AddWithValue("@UserPrincipalName", user.UserPrincipalName); // Open connection sql.Open(); // Insert data cmd.ExecuteNonQuery(); // Close sql.Close(); } catch (Exception) { throw; } finally { cmd.Dispose(); sql.Dispose(); } }
/// <summary> /// Gets if any changes were made to the full access or send as permissions /// </summary> /// <param name="user"></param> private void GetChangedMailboxPermissions(ref MailboxUser user) { // // Figure out the full access permissions // List<string> fullAccessOriginal = hfFullAccessOriginal.Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(); user.FullAccessPermissions = new List<MailboxPermissions>(); foreach (ListItem li in lstFullAccessPermissions.Items) { // Check if the item is selected first, then we need to see if they were added if (li.Selected && !fullAccessOriginal.Contains(li.Value)) { // Permission was added user.FullAccessPermissions.Add(new MailboxPermissions() { Add = true, SamAccountName = li.Value }); } } // Now see if any were removed foreach (string s in fullAccessOriginal) { // Check if the item exist in the original but isn't selected in the listbox. That means they were removed if (!lstFullAccessPermissions.Items.FindByValue(s).Selected) { // Permission was removed user.FullAccessPermissions.Add(new MailboxPermissions() { Add = false, SamAccountName = s }); } } // // Figure out the send as permissions // List<string> sendAsOriginal = hfSendAsOriginal.Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(); user.SendAsPermissions = new List<MailboxPermissions>(); foreach (ListItem li in lstSendAsPermissions.Items) { // Check if the item is selected first, then we need to see if they were added if (li.Selected && !sendAsOriginal.Contains(li.Value)) { // Permission was added user.SendAsPermissions.Add(new MailboxPermissions() { Add = true, SamAccountName = li.Value }); } } // Now see if any were removed foreach (string s in sendAsOriginal) { // Check if the item exists in the original but isn't selected in the listbox. That means they were removed if (!lstSendAsPermissions.Items.FindByValue(s).Selected) { // Permission was removed user.SendAsPermissions.Add(new MailboxPermissions() { Add = false, SamAccountName = s }); } } }
/// <summary> /// Saves a mailbox after it has been edited /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnEditMailboxSave_Click(object sender, EventArgs e) { ExchCmds powershell = null; try { // Initialize powershell powershell = new ExchCmds(Config.ExchangeURI, Config.Username, Config.Password, Config.ExchangeConnectionType, Config.PrimaryDC); // Get mailbox plan MailboxPlan selectedPlan = SQLPlans.GetMailboxPlan(int.Parse(ddlEditMailboxPlan.SelectedValue)); // Initialize our collection MailboxUser user = new MailboxUser(); user.UserPrincipalName = hfUserPrincipalName.Value; user.DistinguishedName = hfDistinguishedName.Value; user.PrimarySmtpAddress = string.Format("{0}@{1}", txtEditPrimaryEmail.Text.Replace(" ", string.Empty), ddlEditPrimaryEmailDomain.SelectedValue); user.DeliverToMailboxAndForward = cbDeliverToMailboxAndFoward.Checked; user.ActiveSyncMailboxPolicy = ddlActiveSyncPlanEditMailbox.SelectedIndex == 0 ? null : ddlActiveSyncPlanEditMailbox.SelectedItem.Text; // Get our forwrading address if (ddlForwardTo.SelectedIndex > 0) user.ForwardingAddress = ddlForwardTo.SelectedValue; // Get our list of email aliases user.EmailAliases = new List<string>(); if (emailAliases != null && emailAliases.Count > 0) { foreach (BaseEmailAliases email in emailAliases) { user.EmailAliases.Add(email.emailAddress); } } // Get the selected mailbox size based on the slider int selectedSize = int.Parse(hfEditMailboxSize.Value); int totalAdded = selectedSize - selectedPlan.SizeInMB; selectedPlan.SetSizeInMB = selectedSize; // Get the permissions and see if anything has changed or not GetChangedMailboxPermissions(ref user); // // Check plan features or if the plan was overridden // user.ActiveSyncEnabled = cbOverrideOptions.Checked ? cbEnableActiveSync.Checked : selectedPlan.ActiveSyncEnabled; user.ECPEnabled = cbOverrideOptions.Checked ? cbEnableECP.Checked : selectedPlan.ECPEnabled; user.IMAPEnabled = cbOverrideOptions.Checked ? cbEnableIMAP.Checked : selectedPlan.IMAPEnabled; user.MAPIEnabled = cbOverrideOptions.Checked ? cbEnableMAPI.Checked : selectedPlan.MAPIEnabled; user.OWAEnabled = cbOverrideOptions.Checked ? cbEnableOWA.Checked : selectedPlan.OWAEnabled; user.POP3Enabled = cbOverrideOptions.Checked ? cbEnablePOP3.Checked : selectedPlan.POP3Enabled; // Update the mailbox powershell.Set_Mailbox(user, selectedPlan); // Update mailbox in SQL SQLUsers.UpdateUserMailbox(user, selectedPlan); // Set notification notification1.SetMessage(controls.notification.MessageType.Success, Resources.LocalizedText.NotificationSuccessMailboxUpdate + user.UserPrincipalName); } catch (Exception ex) { notification1.SetMessage(controls.notification.MessageType.Error, ex.Message); } finally { if (powershell != null) powershell.Dispose(); // Revert to main screen GetMailboxUsers(); } }
/// <summary> /// Formarts a single user email address /// </summary> /// <param name="user"></param> /// <param name="domainName"></param> /// <returns></returns> private string FormatUserEmail(MailboxUser user, string domainName) { // Remove spaces string firstName = "", lastName = ""; if (!string.IsNullOrEmpty(user.FirstName)) firstName = user.FirstName.Replace(" ", string.Empty); if (!string.IsNullOrEmpty(user.LastName)) lastName = user.LastName.Replace(" ", string.Empty); // Validate the values are correct if ((string.IsNullOrEmpty(firstName) && string.IsNullOrEmpty(lastName)) && !rbFormatOther.Checked) throw new Exception(Resources.LocalizedText.NotificationErrorMailboxCannotEnable1 + user.UserPrincipalName); if (string.IsNullOrEmpty(firstName) && (!rbFormatOther.Checked && !rbFormatLastName.Checked)) throw new Exception(Resources.LocalizedText.NotificationErrorMailboxCannotEnable1 + user.UserPrincipalName); if (string.IsNullOrEmpty(lastName) && (!rbFormatOther.Checked && !rbFormatFirstName.Checked)) throw new Exception(Resources.LocalizedText.NotificationErrorMailboxCannotEnable1 + user.UserPrincipalName); if (rbFormatFirstDotLast.Checked) return string.Format("{0}.{1}@{2}", firstName, lastName, domainName); else if (rbFormatFirstLast.Checked) return string.Format("{0}{1}@{2}", firstName, lastName, domainName); else if (rbFormatLastDotFirst.Checked) return string.Format("{0}.{1}@{2}", lastName, firstName, domainName); else if (rbFormatLastFirst.Checked) return string.Format("{0}{1}@{2}", lastName, firstName, domainName); else if (rbFormatFirstInitialLast.Checked) return string.Format("{0}{1}@{2}", firstName.Substring(0, 1), lastName, domainName); else if (rbFormatLastFirstInitial.Checked) return string.Format("{0}{1}@{2}", lastName, firstName.Substring(0, 1), domainName); else if (rbFormatFirstName.Checked) return string.Format("{0}@{1}", firstName, domainName); else if (rbFormatLastName.Checked) return string.Format("{0}@{1}", lastName, domainName); else if (rbFormatUPN.Checked) return string.Format("{0}@{1}", user.UserPrincipalName.Split('@')[0], domainName); else { // User has selected other so now we need to parse the input string entered = txtFormatOther.Text.Replace(" ", string.Empty).ToLower(); // Replace the basic variables entered = entered.Replace("%g", firstName); entered = entered.Replace("%s", lastName); // Replace the advanced variables if (entered.Contains("%1s")) entered = entered.Replace("%1s", lastName.Substring(0, 1)); else if (entered.Contains("%2s")) entered = entered.Replace("%2s", lastName.Substring(0, 2)); else if (entered.Contains("%3s")) entered = entered.Replace("%3s", lastName.Substring(0, 3)); else if (entered.Contains("%4s")) entered = entered.Replace("%4s", lastName.Substring(0, 4)); else if (entered.Contains("%5s")) entered = entered.Replace("%5s", lastName.Substring(0, 5)); else if (entered.Contains("%6s")) entered = entered.Replace("%6s", lastName.Substring(0, 6)); else if (entered.Contains("%7s")) entered = entered.Replace("%7s", lastName.Substring(0, 7)); else if (entered.Contains("%8s")) entered = entered.Replace("%8s", lastName.Substring(0, 8)); else if (entered.Contains("%9s")) entered = entered.Replace("%9s", lastName.Substring(0, 9)); if (entered.Contains("%1g")) entered = entered.Replace("%1g", firstName.Substring(0, 1)); else if (entered.Contains("%2g")) entered = entered.Replace("%2g", firstName.Substring(0, 2)); else if (entered.Contains("%3g")) entered = entered.Replace("%3g", firstName.Substring(0, 3)); else if (entered.Contains("%4g")) entered = entered.Replace("%4g", firstName.Substring(0, 4)); else if (entered.Contains("%5g")) entered = entered.Replace("%5g", firstName.Substring(0, 5)); else if (entered.Contains("%6g")) entered = entered.Replace("%6g", firstName.Substring(0, 6)); else if (entered.Contains("%7g")) entered = entered.Replace("%7g", firstName.Substring(0, 7)); else if (entered.Contains("%8g")) entered = entered.Replace("%8g", firstName.Substring(0, 8)); else if (entered.Contains("%9g")) entered = entered.Replace("%9g", firstName.Substring(0, 9)); // Return the finished product return string.Format("{0}@{1}", entered.Replace(" ", string.Empty), domainName); } }