PowerShell script to export Exchange Usage in CSV format used to Audit an Office 365 Tenant

In case of Office 365 usage audit, Exchange Online is a big part of this assessment.

The following script will export useful data in simple CSV format.

[string]$username = "Admin@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"

$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$adminCreds = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd

#$adminCreds = get-credential

$ReportPath = "C:\EXCHANGE\Reports\"
$data = @()

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $adminCreds -Authentication Basic -AllowRedirection
Import-PSSession $Session

$MbxUsers = get-mailbox -resultsize unlimited
#$MbxUsers = get-mailbox  # < for testing only first 1000 mailbox
#$MbxUsers = get-mailbox -RecipientTypeDetails SharedMailbox -resultsize 50 # < for testing only first 50 shared MB

foreach($user in $mbxusers)
{
	$UPN = $user.userprincipalname
	$Mbx = Get-MailboxStatistics $UPN
	$TotalMBSize = [math]::Round((($Mbx.TotalItemSize.Value.ToString()).Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2) #"69.48 MB (72,854,427 bytes)"

	Write-host " >> MailBox UPN:", $user.userprincipalname, "- MailBoxType:", $user.RecipientTypeDetails, "- Mailbox ItemNumber:", $Mbx.ItemCount -ForegroundColor Magenta
	Write-host "    >> MailBox Size Text:", $Mbx.TotalItemSize ," - MailBox SizeMB:", $TotalMBSize
	Write-host "    >> ProhibitSendQuota:", $user.ProhibitSendQuota, "- ProhibitSendReceiveQuota:", $user.ProhibitSendReceiveQuota
	
	$Properties = @{
		Logoff = $Mbx.lastlogofftime
		Logon = $Mbx.lastlogontime
		IsEncrypted = $Mbx.IsEncrypted
		ProhibitSendReceiveQuotaMB = $user.ProhibitSendReceiveQuota
		ProhibitSendQuotaMB = $user.ProhibitSendQuota
		TotalSizeMB = $TotalMBSize.ToString()
		ItemCount = $Mbx.ItemCount
		IsArchiveMailbox = $Mbx.IsArchiveMailbox
		RecipientTypeDetails = $user.RecipientTypeDetails
		Alias = $user.alias
		UPN = $user.userprincipalname
		Displayname = $Mbx.Displayname
		Name = $user.name
		}
	$data += New-Object psobject -Property $properties
}
 
$datestring = (get-date).ToString("yyyyMMdd-hhmm")
$fileName = Join-Path -Path $ReportPath  -ChildPath $("ExchangeMailbox_"+ $datestring + ".csv")
	
Write-host " -----------------------------------------" -ForegroundColor Green
Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green
$data | Select-Object Name,Displayname,UPN,Alias,RecipientTypeDetails,IsArchiveMailbox,IsEncrypted,ItemCount,TotalSizeMB,ProhibitSendQuotaMB,ProhibitSendReceiveQuotaMB,Logon,Logoff | Export-csv $fileName -NoTypeInformation -enc utf8 
Write-host " -----------------------------------------" -ForegroundColor Green

Remove-PSSession $Session

You can adapt that script as you need, based on your own requirements

Fabrice Romelard

French version: