Surprised! I will show you some PowerShell this month!
A client of mine finally switched to Office 365 last month. They started to have recurring issues with their old on premise Exchange server and going to Office 365 was a no-brainer decision.
On the previous server, because space was tight, I had a script running weekly showing the usage of all users’ mailboxes to discover who was using all the precious space. That script was built for Exchange Server 2010 and was not compatible with Office 365.
Now with the standard 50 gb of total space for each user, they have plenty of space but managers are still interested in getting statistics about the space used in Office 365 and the licensing.
When I started to look around for samples, I found snippets here and there but none were giving me all the client wanted. I found that I needed to mix many of them in order to get various pieces of information.
For example, Get-Mailbox returns a list of the mailboxes in your tenant but we need to call Get-MailboxStatistics to get the total size of an account. but I later discovered that this size does include much more than just emails (so it was a bit different from I expected). If we want a break that value down, we need to call Get-MailboxFolderStatistics to get more granular information like the number and size of emails, calendar items, and contacts. Lastly, if we are interested in licensing information, we need to call Get-MsolUser.
This current article is clearly not a full tutorial on how to use PowerShell as I am not an expert in that domain. It is the result of my experiments trying to produce some statistics putting together snippets that were working separately but not when combined all together.
Downloadable code
Obviously, there won’t be any .Net code to download this month. Instead, the PowerShell scripts are made available so you can start from them and modify to satisfy your own requirements.
This script was tested using the version 5 of PowerShell.
The editor I am using
I am using the “Windows PowerShell Integrated Scripting Environment (ISE)” as my editor for PowerShell. It is convenient enough for the needs I have. For more information about this tool, visit https://msdn.microsoft.com/en-us/powershell/scripting/core-powershell/ise/introducing-the-windows-powershell-ise.
Figure 1: Windows PowerShell editor
Credentials
Every time you will want to run that script, you will need to provide credentials to your Office 365 tenant. You usually call the Get-Credential method which shows a dialog that let you type in a user name and a password (see figure 2). This is not a good thing if you want to automate a process and run a script from a scheduler like I do!
You could write the password directly into your script but it is really not recommended. Anybody getting access to your script, have access to your password in clear text, can then connect to your tenant and do a lot of damage. Really not a good idea!
I found an easy way to encrypt your password into a file and reuse that file from your script. This is all using pure PowerShell, no external libraries involved. The first thing you need to do is to type this command in your editor:
(Get-Credential).Password | ConvertFrom-SecureString | Out-File "C:\Tasks\O365Pwd.txt"
When you execute it, you will be asked to enter your credentials as shown in figure 2. This command is really 4 operations:
- Get-Credential shows the dialog
- .Password just extract the password from it (which is a secure string)
- ConvertFrom-SecureString converts the password into an encrypted standard string
- Out-File send the encrypted string into a text file
Figure 2: Dialog asking for credentials
After you have executed this command and successfully enter your credentials, the file specified in the out-file argument of the command will be created (notice that the folder needs to exist). You can open that file in your favorite text editor and you should find a long string (roughly 500 characters). This file will need to be deployed with your script later as it will be required every time your script will be executed.
Now that you have your password file, you can create a function that will take care. And because we might need to connect to our Office 365 tenant from another script, it is a good idea to create a file containing just that function so you can very easily reuse it. This is what I do. I have created a script file that I have named Functions_ConnectToOffice365.ps1 (that file name will be used later) which contains only this:
#Variables to configure to fit your own environment
$Office365Username = "youradminemailaddress@yourdomain.com"
$Office365PasswordFile = $PSScriptRoot + "\O365Pwd.txt"
function ConnectToOffice365
{
#Build credentials object
$cred = New-Object `
-TypeName System.Management.Automation.PSCredential `
-ArgumentList $Office365Username, (Get-Content $Office365PasswordFile | ConvertTo-SecureString)
#Create remote Powershell session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
-ConnectionUri https://ps.outlook.com/powershell `
-Credential $cred `
-Authentication Basic `
–AllowRedirection
#Import the session
Import-PSSession $Session -AllowClobber | Out-Null
}
Send emails
Another function we will need is one to send the resulting email. For that purpose, I have created another script file (named Functions_SendEmail.ps1) which contains one important function:
#Variables to configure to fit your own environment
$Office365Username = "youradminemailaddress@yourdomain.com"
$MailServer = "yourdomain-com.mail.protection.outlook.com"
function SendEmail
{
param([string] $pSendTo, [string]$pSubject, [string]$htmlBody)
$smtp = New-Object System.Net.Mail.SmtpClient $MailServer
$msg = New-Object System.Net.Mail.MailMessage $Office365Username, $pSendTo, $pSubject, $htmlBody
$msg.isBodyhtml = $true
$smtp.send($msg)
}
Very simple but it will do the job!
Importing these 2 scripts
In my main script, I will want to use these previous functions.
Just like I need to import the MSOnline module, I can import my own modules as well (I use $PSScriptRoot because my function scripts are in the same folder as the main one).
Import-Module MSOnline
Import-Module $PSScriptRoot\Functions_ConnectToOffice365.ps1
Import-Module $PSScriptRoot\Functions_SendEmail.ps1
Looping through the folders of a mailbox account
Another function (but in the main script) that we will be needing down the road is the GetMailboxCounters. This is the code for that function:
function GetMailboxCounters
{
#parse folders to split calendar items from contacts from emails and so on
param([string]$pIdentity)
#get all the properties of the mailbox folders
$folders = Get-MailboxFolderStatistics -Identity $pIdentity |
Select FolderType, ItemsInFolder, FolderSize
#initialize some counters
$EmailsLiveNb = 0
$EmailsLiveSize = 0
$EmailsDeletedNb = 0
$EmailsDeletedSize = 0
$CalendarNb = 0
$CalendarSize = 0
$ContactsNb = 0
$ContactsSize = 0
foreach ( $folder in $folders)
{
$size = 0
#get only the size in bytes
$size = $folder.FolderSize.Split("(")[1].Split(" ")[0].Replace(",","")
if (($folder.FolderType -eq "user created") -or
($folder.FolderType -eq "inbox") -or
($folder.FolderType -eq "sentitems"))
{
$EmailsLiveNb += $folder.ItemsInFolder
$EmailsLiveSize += $size
}
elseif ($folder.FolderType -eq "deleteditems")
{
$EmailsDeletedNb += $folder.ItemsInFolder
$EmailsDeletedSize += $size
}
elseif ($folder.FolderType -eq "Calendar")
{
$CalendarNb += $folder.ItemsInFolder
$CalendarSize += $size
}
elseif ($folder.FolderType -contains "contacts")
{
$ContactsNb += $folder.ItemsInFolder
$ContactsSize += $size
}
}
#return values
[hashtable]$Return = @{}
$Return.EmailsLiveNb = $EmailsLiveNb
$Return.EmailsLiveSize = $EmailsLiveSize
$Return.EmailsDeletedNb = $EmailsDeletedNb
$Return.EmailsDeletedSize = $EmailsDeletedSize
$Return.CalendarNb = $CalendarNb
$Return.CalendarSize = $CalendarSize
$Return.ContactsNb = $ContactsNb
$Return.ContactsSize = $ContactsSize
Return $Return
}
This function loops through all the folders of a specific mailbox (returned by the Get-MailboxFolderStatistics) and calculates the break down that my client wanted (emails/contacts/calendar). Some folders are just deliberately ignored because they were not required (you could add an another else to catch the remaining folders).
Getting information for one mailbox
This other custom function does another big part of the heavy lifting. It calls the GetMailboxCounters custom function to get the counters defined above and also call the Get-MailboxStatistics and the Get-MsolUser built-in functions for the current mailbox to retrieve some known values.
function CreateCollectionItem
{
param($pMailbox)
#Get other details from the account
$mailboxCounters = GetMailboxCounters $pMailbox.Identity.ToString()
$mailboxStat = Get-MailboxStatistics $pMailbox.UserPrincipalName |
Select ItemCount, TotalItemSize, LastLogonTime
$userInfo = Get-MsolUser -UserPrincipalName $pMailbox.UserPrincipalName |
Select IsLicensed, @{Name="Licenses"; Expression ={$_.Licenses.AccountSkuId}}
#manipulate some values
$size = $mailboxStat.TotalItemSize.value.ToString().Split("(")[1].Split(" ")[0].Replace(",","")
$lastLogon = ""
if($mailboxStat.LastLogonTime)
{
$lastLogon = $mailboxStat.LastLogonTime.ToString("yyyy/MM/dd HH:mm:ss")
}
$newItem = New-Object System.Object
$newItem | Add-Member -MemberType NoteProperty -Name "UserPrincipalName" -Value $pMailbox.UserPrincipalName
$newItem | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $pMailbox.DisplayName
$newItem | Add-Member -MemberType NoteProperty -Name "EmailsLiveSize" -Value (SizeToString($mailboxCounters.EmailsLiveSize))
$newItem | Add-Member -MemberType NoteProperty -Name "EmailsLiveNb" -Value $mailboxCounters.EmailsLiveNb.ToString("#,##0")
$newItem | Add-Member -MemberType NoteProperty -Name "EmailsDeletedSize" -Value (SizeToString($mailboxCounters.EmailsDeletedSize))
$newItem | Add-Member -MemberType NoteProperty -Name "EmailsDeletedNb" -Value $mailboxCounters.EmailsDeletedNb.ToString("#,##0")
$newItem | Add-Member -MemberType NoteProperty -Name "CalendarNb" -Value $mailboxCounters.CalendarNb.ToString("#,##0")
$newItem | Add-Member -MemberType NoteProperty -Name "ContactsNb" -Value $mailboxCounters.ContactsNb.ToString("#,##0")
$newItem | Add-Member -MemberType NoteProperty -Name "TotalItemSize" -Value (SizeToString($size))
$newItem | Add-Member -MemberType NoteProperty -Name "TotalItemCount" -Value $mailboxStat.ItemCount.ToString("#,##0")
$newItem | Add-Member -MemberType NoteProperty -Name "LastLogonTime" -Value $lastLogon
$newItem | Add-Member -MemberType NoteProperty -Name "RecipientTypeDetails" -Value $pMailbox.RecipientTypeDetails
$newItem | Add-Member -MemberType NoteProperty -Name "IsLicensed" -Value $userInfo.IsLicensed
$newItem | Add-Member -MemberType NoteProperty -Name "Licenses" -Value ($userInfo.Licenses -replace 'reseller-account:')
$newItem | Add-Member -MemberType NoteProperty -Name "TotalItemSizeRaw" -Value ([long]$size)
return $newItem
}
All the values calculated here are stored into a new object ($newItem) similar to a key-value pair to be easily returned to the caller.
The core of the script
So, what’s left to do in the main script?
The first thing is to retrieve the list of all mailboxes of the tenant using this code:
$mailboxes = Get-Mailbox |
Select Identity, DisplayName, UserPrincipalName, RecipientTypeDetails |
Sort DisplayName
Write-Output ("Number of accounts to process = " + $mailboxes.Count.ToString())
Because we will collect information from various functions, we needed a way to store the data for the time the process is running. An ArrayList is perfect for what we need to do. This is the code required to declare one:
$collectionWithItems = New-Object System.Collections.ArrayList
And we can loop through the list of mailboxes. On each iteration, we call the custom CreateCollectionItem function defined here above like this:
$mailboxCount = 0
foreach ($mailbox in $mailboxes)
{
$mailboxCount ++
Write-Output ("Processing $mailboxCount - $mailbox")
$collectionWithItems.Add((CreateCollectionItem $mailbox)) | Out-Null
}
The objects returned by this function are stored in the ArrayList.
Once the ArrayList has been filled, we can sort it on the criteria we need. In my case, I wanted the list to be sorted by the total size of the accounts descending (I want to see the biggest mailboxes first). This is how it is done:
$collectionWithItems = $collectionWithItems |
sort-object @{Expression={$_.TotalItemSizeRaw}; Ascending=$false}
By the way, TotalItemSizeRaw was filled in the CreateCollectionItem function.
We are now ready to loop through our ArrayList and format an HTML body that will later be sent by email:
$mailboxCount = 0
$htmlOutput = ""
$htmlOutput += CreateBodyHeader
foreach ($item in $collectionWithItems)
{
$mailboxCount ++
$htmlOutput += "<tr>"
$htmlOutput += "<td align=center>$mailboxCount</td>"
$htmlOutput += "<td>" + $item.DisplayName + "</td>"
$htmlOutput += "<td align=center>" + $item.EmailsLiveSize + "</td>"
$htmlOutput += "<td align=center>" + $item.EmailsLiveNb + "</td>"
$htmlOutput += "<td align=center>" + $item.EmailsDeletedSize + "</td>"
$htmlOutput += "<td align=center>" + $item.EmailsDeletedNb + "</td>"
$htmlOutput += "<td align=center>" + $item.CalendarNb + "</td>"
$htmlOutput += "<td align=center>" + $item.ContactsNb + "</td>"
$htmlOutput += "<td align=center>" + $item.TotalItemSize + "</td>"
$htmlOutput += "<td align=center>" + $item.TotalItemCount + "</td>"
$htmlOutput += "<td align=center>" + $item.LastLogonTime + "</td>"
$htmlOutput += "<td align=center>" + $item.RecipientTypeDetails + "</td>"
$htmlOutput += "<td align=center>" + $item.IsLicensed + "</td>"
$htmlOutput += "<td>" + $item.Licenses + "</td>"
$htmlOutput += "</tr>"
}
$htmlOutput += CreateBodyFooter
CreateBodyHeader and CreateBodyFooter are 2 functions available in the attached downloadable scripts.
Finally, the email can be sent by using the function we have defined earlier:
SendEmail $SendTo $MailSubject $htmlOutput
The result
Once I have everything set correctly, I can schedule my script to run every Sunday morning while people are still sleeping.
The output is an HTML email sent to the persons listed in the $SendTo variable at the top of the script.
Figure 3: A sample of the content of the email
Conclusion
Real PowerShell gurus could have surely written better code. Before that exercise, my experiments using PowerShell were very limited (read here copy & paste & run). I have learned a lot by doing this exercise and I hope you will find something in here for you as well!