Loading...

Tech Talk Live Blog

PowerShell, Exchange 2013, and StorageLimitSize

Shawn Mellinger


Recently, I have been working towards using PowerShell to accomplish tasks. Previously, I’d use a command and research it heavily, but that was the extent of my knowledge. I started working on scripting, and created a script that takes the CSV we receive from Human Resources and creates the AD user account, adds the user to AD groups, and Exchange enables and Lync enables the user. I felt pretty good after finishing and was looking for another situation to learn more about PowerShell. I chatted with one of our Private Cloud customers, and he was lamenting that Microsoft deprecated the StorageLimitStatus field in Exchange. I decided to take a look and see what I could do to help him.

This client was periodically running a command to export to CSV all the users’ quota status and other useful information. However, when the data was migrated to Exchange 2013, the command still ran, but StorageLimitStatus was blank. According to Microsoft (http://support.microsoft.com/kb/2819389/en-us):

“This behavior is by design.

Unlike versions of Exchange Server Information Store earlier than Exchange Server 2013 Information Store, the Exchange Server 2013 Information Store does not cache the values of mailbox quotas. Therefore, the Information Store makes frequent calls to Active Directory to retrieve the values of mailbox quotas for each mailbox that is specified in the Get-MailboxStatistics cmdlet. Because of the frequent calls to Active Directory, you may experience poor performance in Exchange Server. To avoid poor performance in Exchange Server, the default Get-MailboxStatistics cmdlet does not retrieve the mailbox quotas and does not display a value in the StorageLimitStatus field.”

I offered to help him get the status of each user in relation to their quota. After all, the best way to learn PowerShell is to practice and get real world experience from a problem. Currently, he was running the same command against each of his databases by just modifying the name. Then, he would combine all the CSV files into a single file and peruse. My goals for this script were the following:

  • Get all desired statistics from user accounts
  • Calculate StorageLimitStatus
  • Export all mailbox statistics from each database to a single file
  • Be able to run this script from the desktop, rather than remote desktop, to Exchange server

Note, I am still a novice at PowerShell. I cannot guarantee that the following examples are best practice. I am always open to suggestions on how to better accomplish a task simpler or cleaner. However, the following script worked and solved the issue that we were facing.

First of all, I had to open a connection to the Exchange server. This saves the user the time needed to remote desktop to the server.

$ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUrihttp://ExchangeServerName/Powershell/ -Authentication Kerberos

Import-PSSession $ExchangeSession

Then, I wanted to get all desired information on a user’s mailbox and dump it to a temporary CSV file. To accomplish this I created a variable with each of the databases listed and ran Get-MailboxStatistics against it.

$DatabaseList = “database1″,”database2”,”AndSoOn”

$folderpath = “C:\test\”

$tempfile = $folderpath + “Userinfo.csv”

ForEach ($database in $DatabaseList){

 

Get-MailboxStatistics -Database $Database   | Sort -Property TotalItemSize -Descending | selectDisplayname,database,TotalItemSize,ItemCount,LastLogonTime,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota | Export-CSV $tempfile -Append

 

Process-UserInfo

}

Now I have a CSV file that contains the mailbox statistics for all of the database users. Next, I need to process that information, calculate the StorageLimitSize, and export it out to another CSV. Sounds simple, right? Not quite.

Where I run into difficulty is how TotalItemSize is exported. If I open my CSV file, TotalItemSize looks like this “214.5 MB (224,969,640 bytes).” It will be rather difficult to compare a string like that against the DatabaseProhibitSendQuota which is also exported in the same format. If you look around the internet, many examples will show to use TotalItemSize.ToMB(). However, since I am using a remote session to Exchange (as per my objectives), I am dealing with a restricted command that does not behave as it does when run in the native Exchange Shell. So, what are my options?

What I chose to do was to modify the string to eliminate everything but the actual value in MB. I could then set a variable to be the decimal version of that string. This accomplished the task, and I now have to do the same for both the warning quota and the prohibit send quota.

$User.TotalItemSize = $_.TotalItemSize

$strTotalItemSize = “{0:N2}” -f ($User.TotalItemSize.SubString(($User.TotalItemSize.IndexOf(“(“) + 1),($User.TotalItemSize.IndexOf(” bytes”) – ($User.TotalItemSize.IndexOf(“(“) + 1))).Replace(“,”,””)/1024/1024)

$decTotalItemSize = [decimal]$strTotalItemSize

$WarningQuota = $_.DatabaseIssueWarningQuota

$strWarningQuota = “{0:N2}” -f ($WarningQuota.SubString(($WarningQuota.IndexOf(“(“) + 1),($WarningQuota.IndexOf(” bytes”) – ($WarningQuota.IndexOf(“(“) + 1))).Replace(“,”,””)/1024/1024)

$decWarningQuota = [decimal]$strWarningQuota

$SendQuota = $_.DatabaseProhibitSendQuota

$strSendQuota = “{0:N2}” -f ($SendQuota.SubString(($SendQuota.IndexOf(“(“) + 1),($SendQuota.IndexOf(” bytes”) – ($SendQuota.IndexOf(“(“) + 1))).Replace(“,”,””)/1024/1024)

$decSendQuota = [decimal]$strSendQuota

Ok, now I’m in a much better place. We have the users TotalItemSize, DatabaseIssueWarningQuota, and DatabaseProhibitSendQuota in decimal values. I can now compare and set a variable to the status of their storage limit.

If (($decTotalItemSize -gt $decWarningQuota) -and ($decTotalItemSize -lt$decSendQuota)) {

$User.StorageLimitStatus = “IssueWarning”

}

 

ElseIf ($decTotalItemSize -gt $decSendQuota) {

$User.StorageLimitStatus = “OverQuota”

}

 

Else {

$User.StorageLimitStatus = “BelowLimit”

}

Armed with all the information I need, I can now export to a final CSV file. The script continues on, deleting the temporary CSV file that I created earlier, leaving only the final CSV which includes the date in the name.

New-Object -TypeName psobject -Property @{

DisplayName = $User.DisplayName

Database = $User.Database

TotalItemSize = $decTotalItemSize

ItemCount = $User.ItemCount

LastLogonTime = $User.LastLogonTime

StorageLimitStatus = $User.StorageLimitStatus

TotalDeletedItemSize = $user.TotalDeletedItemSize

} | Select-Object DisplayName,Database,@{label=”Total Size (MB)”;expression={$decTotalItemSize}},ItemCount,LastLogonTime,StorageLimitStatus,TotalDeletedItemSize | Export-Csv$outputfile -Append

Success! I have accomplished all my objectives and have a single CSV file for the end user. I have attached the entire script below so that you can see, in its entirety, how everything comes together. As I stated earlier, I am open to feedback on how to better accomplish my goals, but this appears to be very effective.

$ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUrihttp://ExchangeServerName/PowerShell/ -Authentication Kerberos

Import-PSSession $ExchangeSession

 

$DatabaseList = “database1″,”database2″,”AndSoOn”

[hashtable]$User = @{}

 

$date = get-date -Format MM-dd-yyy

$folderpath = “C:\test\”

$tempfile = $folderpath + “Userinfo.csv”

$outputfile = $folderpath + “UserQuotas_” + $Date + “.csv”

 

Function RunCommands {

 

ProcessMailboxes

Remove-PSSession $ExchangeSession

}

 

 

 

 

Function ProcessMailboxes{

 

ForEach ($database in $DatabaseList){

 

Get-MailboxStatistics -Database $Database   | Sort -Property TotalItemSize -Descending |selectDisplayname,database,TotalItemSize,ItemCount,LastLogonTime,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota | Export-CSV $tempfile -Append

 

Process-UserInfo

}

 

 

}

 

Function Process-UserInfo {

 

 

Import-Csv $tempfile | ForEach-Object {

 

 

 

$User.DisplayName = $_.DisplayName

$User.Database = $_.Database

$User.TotalItemSize = $_.TotalItemSize

$User.ItemCount = $_.ItemCount

$User.LastLogonTime = $_.LastLogonTime

$User.TotalDeletedItemSize = $_.TotalDeletedItemSize

$strTotalItemSize = “{0:N2}” -f($User.TotalItemSize.SubString(($User.TotalItemSize.IndexOf(“(“) + 1),($User.TotalItemSize.IndexOf(” bytes”) – ($User.TotalItemSize.IndexOf(“(“) +1))).Replace(“,”,””)/1024/1024)

$decTotalItemSize = [decimal]$strTotalItemSize

$WarningQuota = $_.DatabaseIssueWarningQuota

$strWarningQuota = “{0:N2}” -f ($WarningQuota.SubString(($WarningQuota.IndexOf(“(“) + 1),($WarningQuota.IndexOf(” bytes”) – ($WarningQuota.IndexOf(“(“) + 1))).Replace(“,”,””)/1024/1024)

$decWarningQuota = [decimal]$strWarningQuota

$SendQuota = $_.DatabaseProhibitSendQuota

$strSendQuota = “{0:N2}” -f ($SendQuota.SubString(($SendQuota.IndexOf(“(“) + 1),($SendQuota.IndexOf(” bytes”) – ($SendQuota.IndexOf(“(“) + 1))).Replace(“,”,””)/1024/1024)

$decSendQuota = [decimal]$strSendQuota

 

 

 

 

If (($decTotalItemSize -gt $decWarningQuota) -and ($decTotalItemSize -lt $decSendQuota)) {

$User.StorageLimitStatus = “IssueWarning”

}

ElseIf ($decTotalItemSize -gt $decSendQuota) {

$User.StorageLimitStatus = “OverQuota”

}

Else {

$User.StorageLimitStatus = “BelowLimit”

}

New-Object -TypeName psobject -Property @{

DisplayName = $User.DisplayName

Database = $User.Database

TotalItemSize = $decTotalItemSize

ItemCount = $User.ItemCount

LastLogonTime = $User.LastLogonTime

StorageLimitStatus = $User.StorageLimitStatus

TotalDeletedItemSize = $user.TotalDeletedItemSize

} | Select-Object DisplayName,Database,@{label=”Total Size (MB)”;expression={$decTotalItemSize}},ItemCount,LastLogonTime,StorageLimitStatus,TotalDeletedItemSize | Export-Csv$outputfile -Append

 

 

 

}

 

Remove-Item $tempfile

 

}

 

 

RunCommands

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770