Mild-mannered reporter, or Superman?
This week I had a bit of a respite from Office 365 migrations, but it was time to put together a necessary report for the upcoming migrations. Will people think you have super-powers if you can produce a report on your Office 365 environment? Well, maybe not, but at least you can impress someone with a nice report and CSV file on all your Office 365 mailboxes. Project managers love that stuff!
In addition to having the text of the report below, I've also attached it to this post to make downloading it easier. Try this in your lab, of course, but I tried to keep this fairly generic. It shouldn't need much modification since I'm reading your accepted domains right from the tenant.
The formatting of this doesn't come through as well on a blog as I'd like. Loading the file into the Powershell ISE should fix the indenting.
#mailboxcounts-cloud.ps1 = Gets current O365 mailbox counts. grb 2014-03-20
#We'll need the MSOnline commands, and connect to the service.
Import-Module MSOnline
#Get password value and make it into a securestring - don't ever use a clear-text password in a script! https://powertoe.wordpress.com/2011/06/05/storing-passwords-to-disk-in-powershell-with-machine-based-encryption/
$O365login = 'emailaddress'
$Livecred = New-Object System.Management.Automation.PsCredential $O365login,(Get-Content C:\Scripts\password.txt| ConvertTo-SecureString)
Connect-MsolService -Credential $LiveCred
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-PSSession $Session
#Some basics. Get & format the date. Define where we are going to write the log file.
$Date = Get-Date -Format 'yyyyMMdd'
$csvPath = 'C:\Scripts\logs\cloudmxbcounts-'+$Date+'.csv'
$Path = 'C:\Scripts\logs\mailbox-counts-'+$Date+'.log'
#This is going to be an array, so let's define it as such. We'll need this to output the CSV file.
$mailboxcounts = @()
# *Warning* This could get very large! As much as I really dislike loading the entire result into a variable, everything else I tried used about the same RAM. Figure about 1.5 GB of RAM per 10,000 mailboxes. YMMV.
$totalmbx = Get-Mailbox -ResultSize 'unlimited'
#Let's get some counts up-front for the header of the report. (Management summary!) Total mailboxes and licensed.
$totalmbxcount = ($totalmbx | Measure-Object).Count
$totallicensedmbxcount = ($totalmbx | ? {$_.SKUAssigned -eq 'True'} | Measure-Object).Count
#Make the title text
$reporttitle = "Office 365 Mailbox Counts - Total: "+$totalmbxcount+", Licensed: "+$totallicensedmbxcount
#Continue report - This is appended to an existing text file to make a larger report. This can be used separately.
echo "**************************************************" | Out-File -Append -FilePath $Path -NoClobber
echo $reporttitle | Out-File -Append -FilePath $Path -NoClobber
echo (get-Date) | Out-File -Append -FilePath $Path -NoClobber
$domains = Get-AcceptedDomain | Sort-Object -Property DomainName | Select DomainName
Foreach ($domain in $domains) {
$domainsearch = "*@"+$domain.DomainName
$mailboxes = $totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch}
#If a domain has no mailboxes, just skip it.
If ($mailboxes -ne $null) {
#Gather all the stats for the different types of mailboxes. This can take a while with lots of mailboxes.
$mbxuser = ($totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch} | ? {$_.RecipientTypeDetails -eq 'UserMailbox'} | Measure-Object).Count
$mbxshared = ($totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch} | ? {$_.RecipientTypeDetails -eq 'SharedMailbox'} | Measure-Object).Count
$mbxroom = ($totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch} | ? {$_.RecipientTypeDetails -eq 'RoomMailbox'} | Measure-Object).Count
$mbxequipment = ($totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch} | ? {$_.RecipientTypeDetails -eq 'EquipmentMailbox'} | Measure-Object).Count
$mbxtotal = $mbxuser+$mbxshared+$mbxroom+$mbxequipment
$mbxsku = ($totalmbx | ? {$_.WindowsEmailAddress -like $domainsearch} | ? {$_.SKUAssigned -eq 'True'} | Measure-Object).Count
#Let's add a separarator to the report and another line for the domain & stats.
echo "---------------" | Out-File -Append -FilePath $Path -NoClobber
echo ("Domain: "+$domain.DomainName+" - User Mbxs: "+$mbxuser+", Shared Mbxs: "+$mbxshared+", Rm Mbxs: "+$mbxroom+", Eq Mbxs: "+$mbxequipment+", Licensed Mbxs: "+$mbxsku+", Total Mbxs: "+$mbxtotal) | Out-File -Append -FilePath $Path -NoClobber
#Let's start gathering data for the CSV file.
$domaincount = New-Object System.Object
$domaincount | Add-Member -type NoteProperty -name DomainName -value $domain.DomainName
$domaincount | Add-Member -type NoteProperty -name UserMbx -value $mbxuser
$domaincount | Add-Member -type NoteProperty -name SharedMbx -value $mbxshared
$domaincount | Add-Member -type NoteProperty -name RoomMbx -value $mbxroom
$domaincount | Add-Member -type NoteProperty -name EquipMbx -value $mbxequipment
$domaincount | Add-Member -type NoteProperty -name LicensedMbx -value $mbxsku
$domaincount | Add-Member -type NoteProperty -name TotalMbx -value $mbxtotal
$mailboxcounts += $domaincount
}
}
echo "---------------" | Out-File -Append -FilePath $Path -NoClobber
echo " " | Out-File -Append -FilePath $Path -NoClobber
echo "End of report" | Out-File -Append -FilePath $Path -NoClobber
#Create the CSV file. Make your project manager happy.
$mailboxcounts | Export-Csv -Path $csvPath -NoTypeInformation
#Make a copy of the log and CSV that we will email using the batch file that's calling this script (or, put your own SMTP commands in).
copy $Path -Destination 'C:\Scripts\logs\mailbox-counts.log' -Confirm:$false -Force
copy $csvPath -Destination 'C:\Scripts\logs\cloudmxbcounts.csv' -Confirm:$false -Force