How Do I Programmatically Extract Numeric Data From Get-MailboxStatistics?
I recently was working with a customer to build a reporting solution for their Exchange 2010 environment. The report was going to be used for billing and compliance administration. Basically, the organization needed to know how many mailboxes per customer, what some key settings on those mailboxes were. We had a couple of challenges. The first was tying a user to a specific customer. The company attribute in Active Directory was not reliably used. Some customers could be tied to a domain or oganizational unit while others spanned multiple. To resolve that, we created an input file with three values, the agency code (customer billing code), the agency name (customer name) and the agency's recipient view root. If an agency had more than a single view root, mutiple lines in the text file could be processed as long as the code and agency name was consistent. The first line of the CSV file must contain these headers for the code to work: CID,CustomerName,ViewRoot.
The second issue extracting numerical information from the Get-MailboxStatistics cmdlet for attributes like TotalItemSize. By default, it's reported as "250.1 MB (262,144,128 bytes)." While that looks pretty and is even fairly easy to read, it doesn't aggregate well in an Excel pivot table. It needed to be converted to a numeric value and functions exist to do so. By calling out $object.TotalItemSize.Value.ToBytes() we can get a numer to display in the console, but I found it particularly challenging to assign the value to returned to a variable. After several iterations, including some painfully challenging string breaking and re-concatenation, I came upon the idea to use Select-Object. While it certainly seems like an unnecessary complication, I am pleased to see it works. I am also happy to share it, because I know from my own research that many others are experiencing this same request.
## Create a new variable, specified as an array
$MailboxData = @()
## Create a top-level loop for each customer's scope. Even though we grab the OU below, we need a controlled input file for aligning
## discontiguous scopes with specific billing codes. The CID code is considered the billing code for report purposes.
ForEach ($Customer IN Import-CSV [Insert Name of Input File]) {
## Scope the output using a known scoped parameter from the input file
Set-ADServerSettings -RecipientViewRoot $Customer.ViewRoot
## Now, loop through all non-legacy mailbox types in the defined scope (2010 mailboxes only in this case)
ForEach ($mailbox in Get-Mailbox -ResultSize Unlimited | where{$_.RecipientTypeDetails -notlike "Legacy*"}) {
## Create a new database object to populate with several columns, listed below
$DBObject = new-object PSObject
## Columns are grouped by source.
## The first group is all from the Agency Variable created by the top-level loop
$DBObject | add-member NoteProperty -Name CID -Value $Customer.CID
$DBObject | add-member NoteProperty -Name CustomerName -Value $Customer.CustomerName
## This group is extracted from the Get-Mailbox object data
$DBObject | add-member NoteProperty -Name Alias -Value $Mailbox.alias
$DBObject | add-member NoteProperty -Name EmailAddress -Value $Mailbox.primarysmtpaddress
$DBObject | add-member NoteProperty -Name OU -Value $Mailbox.organizationalunit
$DBObject | add-member NoteProperty -Name DisplayName -Value $Mailbox.DisplayName
$DBObject | add-member NoteProperty -Name Database -Value $Mailbox.Database
$DBObject | add-member NoteProperty -Name EmailAddressPolicyEnabled -Value $Mailbox.EmailAddressPolicyEnabled
$DBObject | add-member NoteProperty -Name SingleItemRecoveryEnabled -Value $Mailbox.SingleItemRecoveryEnabled
$DBObject | add-member NoteProperty -Name LitigationHoldEnabled -Value $Mailbox.LitigationHoldEnabled
$DBObject | add-member NoteProperty -Name RetentionComment -Value $Mailbox.RetentionComment
$DBObject | add-member NoteProperty -Name StartDateForRetentionHold -Value $Mailbox.StartDateForRetentionHold
$DBObject | add-member NoteProperty -Name EndDateForRetentionHold -Value $Mailbox.EndDateForRetentionHold
$DBObject | add-member NoteProperty -Name LitigationHoldDate -Value $Mailbox.LitigationHoldDate
$DBObject | add-member NoteProperty -Name LitigationHoldOwner -Value $Mailbox.LitigationHoldOwner
$DBObject | add-member NoteProperty -Name IsMailboxEnabled -Value $Mailbox.IsMailboxEnabled
$DBObject | add-member NoteProperty -Name ModerationEnabled -Value $Mailbox.ModerationEnabled
$DBObject | add-member NoteProperty -Name ThrottlingPolicy -Value $Mailbox.ThrottlingPolicy
$DBObject | add-member NoteProperty -Name UseDatabaseQuotaDefaults -Value $Mailbox.UseDatabaseQuotaDefaults
$DBObject | add-member NoteProperty -Name ProhibitSendReceiveQuota -Value $Mailbox.ProhibitSendReceiveQuota
## Create an object variable to store the get-mailboxstatistics object with select attributes that we can customize (as expressions) to make them
## friendly to an Excel or other computational environment. If you will be adding attributes to this section, you need to add them both to the Select-Object cmdlet and the Add-Member.
$MbxS = Get-mailbox $mailbox.alias | Get-MailboxStatistics | Select-Object LastLoggedOnUserAccount,LastLogonTime,totalDeleteditemsize,itemcount,storagelimitstatus,@{Expression={$_.totalitemsize.value.ToBytes()};Label="TotalMBSize"},@{Expression={$_.totalDeleteditemsize.value.ToBytes()};Label="TotalDeletedItemSize"}
$DBObject | add-member NoteProperty -Name StorageLimitStatus -Value $MBxS.StorageLimitStatus
$DBObject | add-member NoteProperty -Name Items -Value $MBxS.ItemCount
$DBObject | add-member NoteProperty -Name TotalItemSize -Value $MBxS.TotalMBSize
$DBObject | add-member NoteProperty -Name DeletedItemSize -Value $MBxS.TotalDeletedItemSize
$DBObject | add-member NoteProperty -Name LastLogonTime -Value $MBxS.LastLogonTime
$DBObject | add-member NoteProperty -Name LastLoggonOnUser -Value $MBxS.LastLoggedOnUserAccount
## This group is from the Get-User object
$U = Get-User $mailbox.alias
$DBObject | add-member NoteProperty -Name Company -Value $U.Company
$DBObject | add-member NoteProperty -Name FirstName -Value $U.FirstName
$DBObject | add-member NoteProperty -Name LastName -Value $U.LastName
## Append the row to the top-level variable used to store the array
$MailboxData += $DBObject
}
## Now, let's loop around and get the next agency in the CSV input file
}
## Export the array to CSV file using the user supplied value (or default if none supplied)
$MailboxData | Export-Csv $o -NoTypeInformation