Home

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

Highlighted
Fabrice Romelard
MVP

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:

Source used: