I have been updating my scripts from using Exchange Online PowerShell to using the Microsoft Graph SDK as the Microsoft Graph is WAY more powerful of a tool to use. In the old script, I used the Get-MailboxStatistics cmdlet to get all the mailbox sizes for users and maybe some other data. That has since moved to the Reports tab in the M365 Admin Center where you can get different reports on usage and adoption.
This was a great opportunity to be able to run this report in a PowerShell script that you could automate if needed. I am sure there are other ways maybe through PowerApps or Power Automate that would do the same thing. You could essentially put this script in a Power Automate function and send the report where you need it once completed. Of course, modification would need to be done for the Input/and Output locations for the CSV, AND you could also make the script send an email through the Microsoft Graph SDK in another set of commands. That is for more down the road. I am on the catch up when it comes to using the SDK.
This script will Connect to MS Graph with Powershell using the auth token method for authentication so you don’t have to logon. Make sure you have the Azure Enterprise Application setup, the ‘Reports.Read.All’ and Users.Read.All scope permissions granted to the API in Azure. You will need your AppID, TenantID, and Secret Key form the Azure App Registrations Overview Page to proceed. The script explains that though.
SCRIPT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | <# .SYNOPSIS Get an M365 Mailbox Usage Report of all members of a Commercial Tenant with M365 Exchange Online Mailboxes. It will tell you what their mailbox usage details for all mailboxes in the cloud that are active. The report will be in CSV Format and will output to a predetermined directory based on the Parameters set in the script. .NOTES Name: Get-M365GraphMBReport Author: Lance Lingerfelt Version: 2.0 Modify Date: 2024, May 30 Parameter Values: $ReportsPath sets the path to a default directory and will create the directory if needed later in the script. Setting a value is NOT mandatory but script will setup a directory of C:\MatlockScripts\Reports $AppID is the Application (client) ID of an App Registration in Azure AD - Required $Secret is the client secret of the App Registration in Azure AD - Required $TenantID is the Tenant ID of the Azure AD Tenant - Required .EXAMPLE Connect to a Comerical Tenant in the Script .\Get-M365GraphMBReport.ps1 -ClientID "00000000-0000-0000-0000-000000000000" -ClientSecret xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -TenantID "00000000-0000-0000-0000-000000000000" #> [CmdletBinding(SupportsShouldProcess = $true)] Param( [Parameter(Mandatory = $false)] [string] $ReportsPath = "C:\LDLNETScripts\Reports", [Parameter(Mandatory = $true)] [string] $AppID, [Parameter(Mandatory = $true)] [string] $Secret, [Parameter(Mandatory = $true)] [string] $TenantID ) # ================================================ # DO NOT MODIFY BEGIN # ================================================ $ErrorActionPreference = 'Continue' $Date = Get-Date -Format "MM/dd/yyyy" # Set Logging Configuration $Log = [PSCustomObject]@{ Path = "C:\LDLNETScripts\Logs\Get-M365GraphMBReport" Name = "$($Date).log" } # ================================================ # DO NOT MODIFY END # ================================================ # # ================================================ # SCRIPT BEGIN # ================================================ # Log Start of Script Write-Host "Start of Script" -ForegroundColor Red Write-VerboseLog "Start of Script" # Create Report Path if not there Write-VerboseLog "Creating Report Path if not there" Write-Host "Creating Report Path if not there"-ForegroundColor Blue if (!(Test-Path $ReportsPath)) { New-Item -Type Directory -Path $ReportsPath } # Create New Logger Instance if Enabled if ($PSCmdlet.ShouldProcess("Create New Logger Instance", $Log.Path)) { # Import Logger Module try { if ( -not (Get-Module -Name PoShLog -ListAvailable) ) { Install-Module -Name PoShLog -Scope CurrentUser -Force } else { Import-Module -Name PoShLog -Force } } catch { Write-Host -Object "Unable to import logger module. Error: $($_.Exception.Message)" exit 1 } # Import Microsoft Graph Modules for User and Mailbox settings try { if ( -not (Get-Module -Name Microsoft.Graph.Mail -ListAvailable) ) { Install-Module -Name Microsoft.Graph.Mail -Scope CurrentUser -Force } else { Import-Module -Name Microsoft.Graph.Mail -Force } if ( -not (Get-Module -Name Microsoft.Graph.Users -ListAvailable) ) { Install-Module -Name Microsoft.Graph.Users -Scope CurrentUser -Force } else { Import-Module -Name Microsoft.Graph.Users -Force } } catch { Write-Host -Object "Unable to import Microsoft Graph module(s). Error: $($_.Exception.Message)" exit 1 } } # Authenticate and connect to Microsoft Graph using the Token Method and the Microsoft Graph SDK variables set in the cmdlet. # You can replace with your authentication method if you wish to use a different method. $tenantId = $TenantID $clientId = $AppID $clientSecret = $Secret $body = @{ grant_type = "client_credentials" scope = "https://graph.microsoft.com/.default" client_id = $clientId client_secret = $clientSecret } $tokenResponse = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -ContentType "application/x-www-form-urlencoded" -Body $body # Convert the client secret to a SecureString $securetoken = ConvertTo-SecureString $tokenResponse.access_token -AsPlainText -Force $accessToken = $securetoken # Use the token to connect to Microsoft Graph Write-VerboseLog "Connecting to Microsoft Graph SDK Endpoint" Write-Host "Connecting to Microsoft Graph SDK Endpoint"-ForegroundColor Red Connect-MgGraph -AccessToken $accessToken # Get the mailbox usage details for the last 180 days Write-VerboseLog "Getting Mailbox Usage Details and Information for Each User Past 180 Days" Write-Host "Getting Mailbox Usage Details and Information for Each User Past 180 Days" -ForegroundColor Green #Run the report and Convert the values to GB or MB respectively - It creates a input file called M365_Mailbox_Detials_In.csv Get-MgReportMailboxUsageDetail -Period D180 -OutFile $ReportsPath\M365_Mailbox_Detials_In.csv $report = import-csv $ReportsPath\M365_Mailbox_Detials_In.csv $report.Foreach({ $_."storage used (Byte)" = '{0:N2} MB' -f ($_."storage used (Byte)" / 1MB) }) $report.Foreach({ $_."Issue Warning Quota (Byte)" = '{0:N2} GB' -f ($_."Issue Warning Quota (Byte)" / 1GB) }) $report.Foreach({ $_."Prohibit Send Quota (Byte)" = '{0:N2} GB' -f ($_."Prohibit Send Quota (Byte)" / 1GB) }) $report.Foreach({ $_."Prohibit Send/Receive Quota (Byte)" = '{0:N2} GB' -f ($_."Prohibit Send/Receive Quota (Byte)" / 1GB) }) $report.Foreach({ $_."Deleted Item Size (Byte)" = '{0:N2} MB' -f ($_."Deleted Item Size (Byte)" / 1MB) }) $report.Foreach({ $_."Deleted Item Quota (Byte)" = '{0:N2} GB' -f ($_."Deleted Item Quota (Byte)" / 1GB) }) # Export the Results Write-Host "Exporting Results to Path [$ReportsPath]" -ForegroundColor Yellow Write-VerboseLog "Exporting Results to Path [$ReportsPath]" $report | Export-Csv $ReportsPath\M365UserMBReport_$(Get-Date -Format yyyyMMddThhmmss).csv -notypeinformation Write-Host "Mailbox Usage report has been exported to [$ReportsPath]" -ForegroundColor Green Write-VerboseLog "Mailbox Usage report has been exported to [$ReportsPath]" #Delete the input file Remove-Item $ReportsPath\M365_Mailbox_Detials_In.csv if (Test-Path $ReportsPath\M365_Mailbox_Detials_In.csv) { Write-Host "Unable to delete the input file. Please delete it manually. Filepath: [$ReportsPath]\M365_Mailbox_Detials_In.csv" -ForegroundColor Red Write-VerboseLog "Unable to delete the input file. Please delete it manually. Filepath: [$ReportsPath]\M365_Mailbox_Detials_In.csv" } else { Write-Host "Input file has been deleted." -ForegroundColor White Write-VerboseLog "Input file has been deleted." } # Disconnect from the Microsoft Graph SDK endpoint Write-Host "Disconnecting from Microsoft Graph SDK Endpoint" -ForegroundColor Red Write-VerboseLog "Disconnecting from Microsoft Graph SDK Endpoint" Disconnect-MgGraph # Log End of Script Write-Host "End of Script" -ForegroundColor Red Write-VerboseLog "End of Script" # ================================================ # SCRIPT END #================================================= |
OUTPUT
You will receive a CSV file that will look like this:
As you will see in the report, I converted the values to MB and GB respectively, you can make adjustments to the script at you see fit. I am always happy to get feedback and additions to the script as well. Thanks again to Github Copilot for helping with coding some of this script. Feel free to expand it from there, but this script can give you a great report of all your mailbox users in M365 when preparing for migrations or archiving.
References
Generate Usage Reports with Microsoft Graph PowerShell
Report Mailbox and OneDrive Size in PowerShell via MS Graph API (baswijdenes.com)
About Lance Lingerfelt
Lance Lingerfelt is an M365 Specialist and Evangelist with over 20 years of experience in the Information Technology field. Having worked in enterprise environments to small businesses, he is able to adapt and provide the best IT Training and Consultation possible. With a focus on AI, the M365 Stack, and Healthcare, he continues to give back to the community with training, public speaking events, and this blog.
