Report Mailbox and OneDrive Size in PowerShell via Microsoft Graph API

Report Mailbox sizes, OneDrive sizes, or all in one, with PowerShell and the MS Graph API

In the tutorial below I explain how you can:

• Report Mailbox sizes
• Report OneDrive Sizes
• Report both (in one report)

In the blog post I use PowerShell and the Microsoft Graph API. I built my own modules around the API. I don’t use the official Microsoft PowerShell module.

In the blog post below, I’m using a submodule called Optimized.Mga.Report.
This is a submodule of Optimized.Mga.

Got feedback? Please leave a comment!

Before we can start…

We need the following:

• Optimized.Mga.Report module
• AzureAD registered application
  With permission:
  1. Reports.Read.All

PowerShell module Optimized.Mga.Report

• If you have feedback for me, you can leave a comment on this post, or on Github.
• If you want to know more about how to use and read the module, check out this blog.

AzureAD registered application

Not sure how to get started with an AzureAD registered application for the Microsoft Graph API?

I wrote a page for this which you can find here:
How to start with Microsoft Graph in PowerShell by Bas Wijdenes

Microsoft also created a blog post about how to get started with an AzureAD registered application for the Microsoft Graph API.
You can find that here:
Manage app registration and API permission for Microsoft Graph notifications – Microsoft Graph | Microsoft Docs

Install-Module Optimized.Mga.Report -Scope CurrentUser

$null = Connect-Mga -ClientSecret 'XXXX' -ApplicationID 'b5954443-ad10-4d1c-8cbc-dc05268a1858' -Tenant 'bwit.onmicrosoft.com'

Let’s start with the reports

Report Mailbox sizes

The Report module contains several cmdlets for mailboxes. You can see which cmdlets exist by running the cmdlet below.

Get-Command -name "*Mailbox*" -Module 'Optimized.Mga.Report'

In theory, you could run them all to see what information they return. These commands are purely about reports and you can not change anything in your tenant with these cmdlets.

Play with it all you want, but the cmdlet we need now is Get-MgaReportMailboxUsageDetail.

Get-MgaReportMailboxUsageDetail

LastActivityDate             : 2022-04-01
CreatedDate                  : 2022-03-31
ItemCount                    : 63
DeletedItemSizeInGb          : 0
DisplayName                  : Bas Wijdenes
UserPrincipalName            : [email protected]
HasArchive                   : True
IsDeleted                    : False
ProhibitSendQuotaInGB        : 99
ProhibitSendReceiveQuotaInGb : 100
SizeInGb                     : 0
IssueWarningQuotaInGB        : 98
DeletedItemCount             : 46
DeletedItemQuotaInGb         : 30

I have shortened the response above to 1 user. You will probably see more users.

The Gb is calculated automatically from the bytes in the backend. You can recognize this from the ‘InGb’ in the property name. At 0 Gb it means there are so few items in it that it is automatically calculated at 0 Gb.

I’d love to hear your feedback on whether you’d still like to include the bytes.
I’m not going to convert it from Bytes to Kb, Mb, AND Gb, since a mailbox may contain 100Gb, bytes, Kbs, and Mbs are negligible, but it might be better to include the original bytes as well.

When we receive the response in a variable, we can make a report of this, or combine it with, for example… the OneDrive size.

$MailboxSizes = Get-MgaReportMailboxUsageDetail

Report OneDrive Sizes

The Report module contains several cmdlets for OneDrive as well. You can see which cmdlets exist by running the cmdlet below.

Get-Command -name "*OneDrive*" -Module 'Optimized.Mga.Report'

You can test them again if you want, but the cmdlet we need now is Get-MgaReportMailboxUsageDetail.

Get-MgaReportOneDriveUsageAccountDetail

SiteUrl           : XXXXXXXXXX
AllocatedSizeInGb : 1024
SizeInGb          : 0,03
UserPrincipalName : [email protected]
FreeSizeInGb      : 1023,97
FileCount         : 15
ActiveFileCount   : 0
LastActivityDate  : 2022-04-01
IsDeleted         : False
DisplayName       : Bas Wijdenes

And with this cmdlet the same story as with Get-MgaReportMailboxUsageDetail. The bytes have been converted to Gb. I’m curious whether you prefer to see the original value or whether it is fine in Gb.

And let’s put it in a variable again.

$OneDriveSizes = Get-MgaReportOneDriveUsageAccountDetail

Mailbox size & OneDrive size in one report

We have our data in the following variables $MailboxSizes & $OneDriveSizes.
Now we just need to put them together.

What important is to me in my code, is speed, optimization, and understandable scripting.

We can and will approach this in different ways and I want to show the differences here.

If you are only looking for the best solution, go to header: The complete script for the most optimized way.

Quite difficult to understand and a slow solution

What we can do is that we run through the $MailboxSizes with foreach and then we find the OneDrive user with a Where-Object.

$Users = @()
foreach ($MailboxSize in $MailboxSizes) {
    $CurrentOneDriveUser = $null
    $User = New-Object PSObject
    $CurrentOneDriveUser = $OneDriveSizes | Where-Object { $_.UserPrincipalName -eq $MailboxSize.UserPrincipalName } 
    $User | Add-Member -NotePropertyName 'UPN' -NotePropertyValue $MailboxSize.UserPrincipalName
    $User | Add-Member -NotePropertyName 'MailboxSizeInGb' -NotePropertyValue $MailboxSize.SizeInGb
    $User | Add-Member -NotePropertyName 'OneDriveSizeInGb' -NotePropertyValue $CurrentOneDriveUser.SizeInGb
    $Users += $User
}

With Add-Member you can make custom objects with the property you find useful.

This script is slow and difficult to read because there are several commands in the foreach loop.
I prefer to minimize the commands in a foreach loop because that just makes the script slower.
It is best to first collect the large numbers in one go and then match them together.

A Measure-Command shows (10240 mailboxes): TotalSeconds : 29,7377941

The easiest to understand, but a slow solution

We will run the same foreach loop with the Where-Object again, but remove the Add-Member this time.

This would be the complete script:

$Users = @()
foreach ($MailboxSize in $MailboxSizes) {
    $CurrentOneDriveUser = $null
    $CurrentOneDriveUser = $OneDriveSizes | Where-Object { $_.UserPrincipalName -eq $MailboxSize.UserPrincipalName } 
    $Object = [PSCustomObject]@{
        UPN          = $MailboxSize.UserPrincipalName
        MailboxSize  = $MailboxSize.SizeInGb
        OneDriveSize = $CurrentOneDriveUser.SizeInGb
    }
    $Users += $Object
}

I find this an easy to read script.

The only drawback is that this is not optimized. What now happens in the script is that we run through the Where-Object every time to find the right OneDrive. I currently only have 20 mailboxes in my tenant, so it doesn’t matter much based on time, but suppose you have 10000+ Mailboxes, this will save a lot more time.

Let’s test this by increasing the $MailboxSizes array to 10240 mailboxes and then run a Measure-Command to test how long this script takes: TotalSeconds : 8,1102409.

This is not too bad for me, but suppose you want to add even more reports, etc. then this is not the right approach in terms of optimisation.

You want to minimize the Where-Object‘s as well.

Most optimized way to include both reports in one

A Where-Object is slow. You prefer to match via hashtables. The script will be a bit longer so readability will be more difficult, but the speed is a big difference, especially on larger tenants.

First we will convert the $OneDriveSizes to a hashtable. You can do that with the following script:

$OneDriveSizesHashTable = @{}
foreach ($OneDriveSize in $OneDriveSizes) {
    $OneDriveSizesHashTable.Add($OneDriveSize.UserPrincipalName, $OneDriveSize)
}

A foreach loop through variables or objects is very fast even with a large number of objects. This is because no other commands are called.

By using the above script, your script will be larger and therefore less readable. For me it is important that I understand it myself, but also my fellow colleagues.

And then we go through the $MailboxSizes again and match on the hashtable as in the script below.

$ReportSizesHashTable = [System.Collections.Generic.List[Object]]::new()
foreach ($MailboxSize in $MailboxSizes) {
    $CurrentOneDriveUser = $null
    $CurrentOneDriveUser = $OneDriveSizesHashTable[$MailboxSize.UserPrincipalName]
    if ($null -ne $CurrentOneDriveUser) {
        $Object = [PSCustomObject]@{
            UPN          = $MailboxSize.UserPrincipalName
            MailboxSize  = $MailboxSize.SizeInGb
            OneDriveSize = $CurrentOneDriveUser.SizeInGb
        }
        $ReportSizesHashTable.Add($Object)
    }
}

A Measure-Command on 10240 mailboxes shows the difference in speed between these 3 ways: TotalSeconds : 0,2675163

The complete script for the most optimized way

$OneDriveSizesHashTable = @{}
foreach ($OneDriveSize in $OneDriveSizes){
$OneDriveSizesHashTable.Add($OneDriveSize.UserPrincipalName, $OneDriveSize)
}

$ReportSizesHashTable = [System.Collections.Generic.List[Object]]::new()
foreach ($MailboxSize in $MailboxSizes) {
    $CurrentOneDriveUser = $null
    $CurrentOneDriveUser = $OneDriveSizesHashTable[$MailboxSize.UserPrincipalName]
    if ($null -ne $CurrentOneDriveUser) {
        $Object = [PSCustomObject]@{
            UPN          = $MailboxSize.UserPrincipalName
            MailboxSize  = $MailboxSize.SizeInGb
            OneDriveSize = $CurrentOneDriveUser.SizeInGb
        }
        $ReportSizesHashTable.Add($Object)
    }
}