private void GetTotalAllocatedMailboxSize(string entityConnectionString, ref OverallStats stats)
{
SqlConnection sql = null;
SqlCommand cmd = null;
try
{
string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString;
sql = new SqlConnection(providerConnectionString);
cmd = new SqlCommand(@"SELECT UserPrincipalName, MailboxPlan, AdditionalMB,
(SELECT MailboxSizeMB FROM Plans_ExchangeMailbox WHERE MailboxPlanID=Users.MailboxPlan) AS MailboxPlanSize,
((SELECT MailboxSizeMB FROM Plans_ExchangeMailbox WHERE MailboxPlanID=Users.MailboxPlan) + AdditionalMB) AS TotalSize
FROM Users WHERE MailboxPlan > 0", sql);
sql.Open();
// Keep track of total
decimal total = 0;
// Read data
SqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
if (r["TotalSize"] == DBNull.Value)
total = total + decimal.Parse(r["MailboxPlanSize"].ToString(), CultureInfo.InvariantCulture);
else
total = total + decimal.Parse(r["TotalSize"].ToString(), CultureInfo.InvariantCulture);
}
// Convert to GB, TB, etc
if (total > 0)
{
if (total >= 1048576)
{
stats.TotalAllocatedEmailSpace = (total / 1024) / 1024;
stats.TotalAllocatedEmailSpaceSizeType = "TB";
}
else if (total >= 1024)
{
stats.TotalAllocatedEmailSpace = total / 1024;
stats.TotalAllocatedEmailSpaceSizeType = "GB";
}
// Convert the MB to KB for the progress bars
stats.TotalAllocatedEmailSpaceInKB = total * 1024;
}
}
r.Close();
r.Dispose();
if (total < 0)
{
stats.TotalAllocatedEmailSpaceInKB = 0;
stats.TotalAllocatedEmailSpace = 0;
stats.TotalAllocatedEmailSpaceSizeType = "?";
}
}
catch (Exception ex)
{
logger.Error("Error getting Exchange allocated mailbox sizes", ex);
}
finally
{
if (cmd != null)
cmd.Dispose();
if (sql != null)
sql.Dispose();
}
}
private void GetTotalUsedMailboxSize(string entityConnectionString, ref OverallStats stats)
{
SqlConnection sql = null;
SqlCommand cmd = null;
try
{
string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString;
sql = new SqlConnection(providerConnectionString);
cmd = new SqlCommand(@"SELECT UserPrincipalName, Retrieved, TotalItemSizeInKB FROM SvcMailboxSizes
WHERE CONVERT(date, Retrieved) = (SELECT TOP 1 CONVERT(date, Retrieved) FROM SvcMailboxSizes ORDER BY Retrieved DESC)
ORDER BY Retrieved DESC", sql);
sql.Open();
// Keep track of total
decimal total = 0;
// Read data
SqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
decimal parsed = 0;
decimal.TryParse(r["TotalItemSizeInKB"].ToString(), NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out parsed);
total = total + parsed;
}
// Convert to MB, GB, TB, etc
if (total > 0)
{
if (total >= 1073741824)
{
stats.TotalUsedEmailSpace = ((total / 1024) / 1024) / 1024;
stats.TotalUsedEmailSpaceSizeType = "TB";
}
else if (total >= 1048576)
{
stats.TotalUsedEmailSpace = (total / 1024) / 1024;
stats.TotalUsedEmailSpaceSizeType = "GB";
}
else if (total >= 1024)
{
stats.TotalUsedEmailSpace = total / 1024;
stats.TotalUsedEmailSpaceSizeType = "MB";
}
stats.TotalUsedEmailSpaceInKB = total;
}
}
r.Close();
r.Dispose();
if (total < 0)
{
stats.TotalUsedEmailSpaceInKB = 0;
stats.TotalUsedEmailSpace = 0;
stats.TotalUsedEmailSpaceSizeType = "?";
}
}
catch (Exception ex)
{
logger.Error("Error getting Exchange mailbox sizes", ex);
}
finally
{
if (cmd != null)
cmd.Dispose();
if (sql != null)
sql.Dispose();
}
}
public OverallStats GetOtherStatistics(string entityConnectionString)
{
CPDatabase database = null;
try
{
database = new CPDatabase();
OverallStats stats = null;
// Get all users
var users = from u in database.Users
select u;
// Don't continue we don't find any users
if (users != null)
{
stats = new OverallStats();
stats.TotalUsers = users.Count();
stats.TodayUsers = (from u in users where DbFunctions.TruncateTime(u.Created) == DbFunctions.TruncateTime(DateTime.Now) select u.UserPrincipalName).Count();
stats.TotalMailboxes = (from u in users where u.MailboxPlan > 0 select u.UserPrincipalName).Count();
stats.TotalLyncUsers = (from u in users where u.LyncPlan > 0 select u.UserPrincipalName).Count();
// Get all companies
var companies = from c in database.Companies
select c;
if (companies != null)
{
stats.TotalResellers = (from r in companies where r.IsReseller select r.CompanyCode).Count();
stats.TotalCompanies = (from c in companies where !c.IsReseller select c.CompanyCode).Count();
stats.TodayCompanies = (from c in companies where DbFunctions.TruncateTime(c.Created) == DbFunctions.TruncateTime(DateTime.Now) select c.CompanyCode).Count();
}
// Get all domains
var domains = from d in database.Domains
select d;
if (domains != null)
{
stats.TotalDomains = (from d in domains select d.Domain1).Count();
stats.TotalAcceptedDomains = (from a in domains where a.IsAcceptedDomain select a.Domain1).Count();
}
// Get other Exchange stats
stats.TotalDistributionGroups = (from d in database.DistributionGroups select d.ID).Count();
stats.TotalMailContacts = (from c in database.Contacts select c.DistinguishedName).Count();
// Get Citrix users
var citrix = (from c in database.UserPlansCitrices
select c.UserID).Distinct().Count();
stats.TotalCitrixUsers = citrix;
// Get Citrix data
var citrixApps = from c in database.Plans_Citrix
select c;
if (citrixApps != null)
{
stats.TotalCitrixApps = (from c in citrixApps where !c.IsServer select c).Count();
stats.TotalCitrixServers = (from c in citrixApps where c.IsServer select c).Count();
}
// Get total allocated space
GetTotalAllocatedMailboxSize(entityConnectionString, ref stats);
// Get total used spaces
GetTotalUsedMailboxSize(entityConnectionString, ref stats);
}
// Return data
return stats;
}
catch (Exception ex)
{
this.logger.Error("Error getting other statistics.", ex);
return null;
}
finally
{
if (database != null)
database.Dispose();
}
}
