/// <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);
}
}
