Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-356287%22%20slang%3D%22en-US%22%3EPowerShell%20script%20to%20export%20Exchange%20Usage%20in%20CSV%20format%20used%20to%20Audit%20an%20Office%20365%20Tenant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356287%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20case%20of%20Office%20365%20usage%20audit%2C%20Exchange%20Online%20is%20a%20big%20part%20of%20this%20assessment.%3C%2FP%3E%0A%3CP%3EThe%20following%20script%20will%20export%20useful%20data%20in%20simple%20CSV%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%5Bstring%5D%24username%20%3D%20%22Admin%40yourtenant.onmicrosoft.com%22%0A%5Bstring%5D%24PwdTXTPath%20%3D%20%22C%3A%5CSECUREDPWD%5CExportedPWD-%24(%24username).txt%22%0A%0A%24secureStringPwd%20%3D%20ConvertTo-SecureString%20-string%20(Get-Content%20%24PwdTXTPath)%0A%24adminCreds%20%3D%20New-Object%20System.Management.Automation.PSCredential%20%24username%2C%20%24secureStringPwd%0A%0A%23%24adminCreds%20%3D%20get-credential%0A%0A%24ReportPath%20%3D%20%22C%3A%5CEXCHANGE%5CReports%5C%22%0A%24data%20%3D%20%40()%0A%0A%24Session%20%3D%20New-PSSession%20-ConfigurationName%20Microsoft.Exchange%20-ConnectionUri%20https%3A%2F%2Foutlook.office365.com%2Fpowershell-LiveID%2F%20-Credential%20%24adminCreds%20-Authentication%20Basic%20-AllowRedirection%0AImport-PSSession%20%24Session%0A%0A%24MbxUsers%20%3D%20get-mailbox%20-resultsize%20unlimited%0A%23%24MbxUsers%20%3D%20get-mailbox%20%20%23%20%26lt%3B%20for%20testing%20only%20first%201000%20mailbox%0A%23%24MbxUsers%20%3D%20get-mailbox%20-RecipientTypeDetails%20SharedMailbox%20-resultsize%2050%20%23%20%26lt%3B%20for%20testing%20only%20first%2050%20shared%20MB%0A%0Aforeach(%24user%20in%20%24mbxusers)%0A%7B%0A%20%24UPN%20%3D%20%24user.userprincipalname%0A%20%24Mbx%20%3D%20Get-MailboxStatistics%20%24UPN%0A%20%24TotalMBSize%20%3D%20%5Bmath%5D%3A%3ARound(((%24Mbx.TotalItemSize.Value.ToString()).Split(%22(%22)%5B1%5D.Split(%22%20%22)%5B0%5D.Replace(%22%2C%22%2C%22%22)%2F1MB)%2C2)%20%23%2269.48%20MB%20(72%2C854%2C427%20bytes)%22%0A%0A%20Write-host%20%22%20%26gt%3B%26gt%3B%20MailBox%20UPN%3A%22%2C%20%24user.userprincipalname%2C%20%22-%20MailBoxType%3A%22%2C%20%24user.RecipientTypeDetails%2C%20%22-%20Mailbox%20ItemNumber%3A%22%2C%20%24Mbx.ItemCount%20-ForegroundColor%20Magenta%0A%20Write-host%20%22%20%20%20%20%26gt%3B%26gt%3B%20MailBox%20Size%20Text%3A%22%2C%20%24Mbx.TotalItemSize%20%2C%22%20-%20MailBox%20SizeMB%3A%22%2C%20%24TotalMBSize%0A%20Write-host%20%22%20%20%20%20%26gt%3B%26gt%3B%20ProhibitSendQuota%3A%22%2C%20%24user.ProhibitSendQuota%2C%20%22-%20ProhibitSendReceiveQuota%3A%22%2C%20%24user.ProhibitSendReceiveQuota%0A%20%0A%20%24Properties%20%3D%20%40%7B%0A%20%20Logoff%20%3D%20%24Mbx.lastlogofftime%0A%20%20Logon%20%3D%20%24Mbx.lastlogontime%0A%20%20IsEncrypted%20%3D%20%24Mbx.IsEncrypted%0A%20%20ProhibitSendReceiveQuotaMB%20%3D%20%24user.ProhibitSendReceiveQuota%0A%20%20ProhibitSendQuotaMB%20%3D%20%24user.ProhibitSendQuota%0A%20%20TotalSizeMB%20%3D%20%24TotalMBSize.ToString()%0A%20%20ItemCount%20%3D%20%24Mbx.ItemCount%0A%20%20IsArchiveMailbox%20%3D%20%24Mbx.IsArchiveMailbox%0A%20%20RecipientTypeDetails%20%3D%20%24user.RecipientTypeDetails%0A%20%20Alias%20%3D%20%24user.alias%0A%20%20UPN%20%3D%20%24user.userprincipalname%0A%20%20Displayname%20%3D%20%24Mbx.Displayname%0A%20%20Name%20%3D%20%24user.name%0A%20%20%7D%0A%20%24data%20%2B%3D%20New-Object%20psobject%20-Property%20%24properties%0A%7D%0A%20%0A%24datestring%20%3D%20(get-date).ToString(%22yyyyMMdd-hhmm%22)%0A%24fileName%20%3D%20Join-Path%20-Path%20%24ReportPath%20%20-ChildPath%20%24(%22ExchangeMailbox_%22%2B%20%24datestring%20%2B%20%22.csv%22)%0A%20%0AWrite-host%20%22%20-----------------------------------------%22%20-ForegroundColor%20Green%0AWrite-Host%20(%22%20%26gt%3B%26gt%3B%26gt%3B%20writing%20to%20file%20%7B0%7D%22%20-f%20%24fileName)%20-ForegroundColor%20Green%0A%24data%20%7C%20Select-Object%20Name%2CDisplayname%2CUPN%2CAlias%2CRecipientTypeDetails%2CIsArchiveMailbox%2CIsEncrypted%2CItemCount%2CTotalSizeMB%2CProhibitSendQuotaMB%2CProhibitSendReceiveQuotaMB%2CLogon%2CLogoff%20%7C%20Export-csv%20%24fileName%20-NoTypeInformation%20-enc%20utf8%20%0AWrite-host%20%22%20-----------------------------------------%22%20-ForegroundColor%20Green%0A%0ARemove-PSSession%20%24Session%3C%2FPRE%3E%0A%3CP%3EYou%20can%20adapt%20that%20script%20as%20you%20need%2C%20based%20on%20your%20own%20requirements%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFabrice%20Romelard%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CEM%3EFrench%20version%3A%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.developpeur.org%2Ffabrice69%2Farchive%2F2019%2F02%2F25%2Foffice-365-script-powershell-pour-auditer-l-usage-d-exchange-online-de-votre-tenant.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblogs.developpeur.org%2Ffabrice69%2Farchive%2F2019%2F02%2F25%2Foffice-365-script-powershell-pour-auditer-l-usage-d-exchange-online-de-votre-tenant.aspx%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CEM%3E%3CSTRONG%3ESource%20used%3A%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22http%3A%2F%2Fwww.cloudpartner.fi%2F%3Fp%3D350%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.cloudpartner.fi%2F%3Fp%3D350%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fexchange%2Fmailboxes%2Fget-mailboxstatistics%3Fview%3Dexchange-ps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fexchange%2Fmailboxes%2Fget-mailboxstatistics%3Fview%3Dexchange-ps%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fexchange%2Fmailboxes%2Fget-mailbox%3Fview%3Dexchange-ps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fexchange%2Fmailboxes%2Fget-mailbox%3Fview%3Dexchange-ps%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.citrix.com%2Farticle%2FCTX229565%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.citrix.com%2Farticle%2FCTX229565%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vdberge.com%2Fkennisbank%2Foffice-365-error-the-term-get-mailbox-is-not-recognized%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.vdberge.com%2Fkennisbank%2Foffice-365-error-the-term-get-mailbox-is-not-recognized%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2F4sysops.com%2Farchives%2Fsort-exchange-and-office-365-mailboxes-by-size-with-powershell%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F4sysops.com%2Farchives%2Fsort-exchange-and-office-365-mailboxes-by-size-with-powershell%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-356287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAudit%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExchange%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Frequent Contributor

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:

 

Related Conversations