Sync Mailbox Usage Reports with a SharePoint list

The Microsoft Graph includes all sorts of useful reports on how users are taking advantage of the various Office 365 services. One particularly useful report, especially for IT Admins and MSPs, is the mailbox usage report.

This built in report provides a summary of each users mailbox size and how close they are to filling it up. This guide will demonstrate how to sync this report to a SharePoint list and provide alerts to your support desk when a mailbox’s usage has reached a certain threshold.

This guide is designed for Microsoft Partners or IT Admins with access to customer tenants via delegated administration. It will also work for a single tenant with a small modification.

Prerequisites

• To run the first script, you’ll need to install the Azure AD PowerShell Module. You can do this by opening PowerShell as an administrator and running:

  Install-Module AzureAD

• To authorise the application to access your own and your customers’ tenants, you’ll need to be a Global Administrator.

Solution outline

This solution consists of the following:

Script 1 – Authorise an Azure AD Application to access customers’ reports

Creates an application with access to Mailbox usage reports for your own and customers’ tenants. This one needs to be run as a Global Admin.

Script 2 – Syncing Mailbox Usage Reports with a SharePoint List

Retrieves the usage reports, creates a SharePoint list and syncs the mailbox usage data. Can be run as a scheduled task or Azure Function

Optional steps:

Create a HTTP Triggered Microsoft Flow

Sends an email to yourself or your support desk with a list of mailboxes that are running out of space

Create HTTP and Timer Triggered Azure Functions

The Timer Triggered function will run the syncing script on a schedule. To prevent an overload of repetitive alerts, the HTTP Triggered Azure function will allow your support desk to acknowledge or ignore alerts for certain mailboxes.

Authorise an Azure AD Application to access customers’ reports

1. Double click the below script to select it.
2. Copy and paste the script into a new file in Visual Studio Code and save it with a .ps1 extension
3. Install the recommended PowerShell module if you haven’t already
4. Modify the $homePage and $logoutURI values to any valid URI that you like. They don’t need to be actual addresses, so feel free to make something up. Set the $appIDUri variable to a use a valid domain in your tenant. eg. https://yourdomain.com/$((New-Guid).ToString())
5. If you are running the script for a single tenant, comment out the following two lines:
6. Press F5 to run the script
7. Sign in to Azure AD using your global admin credentials. Note that the login window may appear behind Visual Studio Code.
8. Wait for the script to complete.
9. Retrieve the client ID, client secret and tenant ID from the exported CSV at C:\temp\azureadapp.csv. (below image is just an example.)

PowerShell Script to create and authorise Azure AD Application

# This script needs to be run by an admin account in your Office 365 tenant
# This script will create an Azure AD app in your organisation with permission
# to access resources in yours and your customers' tenants.
# It will export information about the application to a CSV located at C:\temp\.
# The CSV will include the Client ID and Secret of the application, so keep it safe.
   
# Confirm C:\temp exists
$temp = Test-Path -Path C:\temp
if ($temp) {
    #Write-Host "Path exists"
}
else {
    Write-Host "Creating Temp folder"
    New-Item -Path C:\temp -ItemType directory
}
   
$applicationName = "GCITS Report Reader"
   
# Change this to true if you would like to overwrite any existing applications with matching names. 
$removeExistingAppWithSameName = $false
# Modify the homePage, appIdURI and logoutURI values to whatever valid URI you like. 
# They don't need to be actual addresses, so feel free to make something up.
$homePage = "https://secure.gcits.com"
$appIdURI = "https://secure.gcits.com/$((New-Guid).ToString())"
$logoutURI = "https://portal.office.com"
   
$URIForApplicationPermissionCall = "https://graph.microsoft.com/beta/reports/getMailboxUsageDetail(period='D7')?`$format=application/json"
$ApplicationPermissions = "Reports.Read.All Directory.Read.All Sites.Manage.All"
   
Function Add-ResourcePermission($requiredAccess, $exposedPermissions, $requiredAccesses, $permissionType) {
    foreach ($permission in $requiredAccesses.Trim().Split(" ")) {
        $reqPermission = $null
        $reqPermission = $exposedPermissions | Where-Object {$_.Value -contains $permission}
        Write-Host "Collected information for $($reqPermission.Value) of type $permissionType" -ForegroundColor Green
        $resourceAccess = New-Object Microsoft.Open.AzureAD.Model.ResourceAccess
        $resourceAccess.Type = $permissionType
        $resourceAccess.Id = $reqPermission.Id    
        $requiredAccess.ResourceAccess.Add($resourceAccess)
    }
}
   
Function Get-RequiredPermissions($requiredDelegatedPermissions, $requiredApplicationPermissions, $reqsp) {
    $sp = $reqsp
    $appid = $sp.AppId
    $requiredAccess = New-Object Microsoft.Open.AzureAD.Model.RequiredResourceAccess
    $requiredAccess.ResourceAppId = $appid
    $requiredAccess.ResourceAccess = New-Object System.Collections.Generic.List[Microsoft.Open.AzureAD.Model.ResourceAccess]
    if ($requiredDelegatedPermissions) {
        Add-ResourcePermission $requiredAccess -exposedPermissions $sp.Oauth2Permissions -requiredAccesses $requiredDelegatedPermissions -permissionType "Scope"
    } 
    if ($requiredApplicationPermissions) {
        Add-ResourcePermission $requiredAccess -exposedPermissions $sp.AppRoles -requiredAccesses $requiredApplicationPermissions -permissionType "Role"
    }
    return $requiredAccess
}
Function New-AppKey ($fromDate, $durationInYears, $pw) {
    $endDate = $fromDate.AddYears($durationInYears) 
    $keyId = (New-Guid).ToString()
    $key = New-Object Microsoft.Open.AzureAD.Model.PasswordCredential($null, $endDate, $keyId, $fromDate, $pw)
    return $key
}
   
Function Test-AppKey($fromDate, $durationInYears, $pw) {
   
    $testKey = New-AppKey -fromDate $fromDate -durationInYears $durationInYears -pw $pw
    while ($testKey.Value -match "\+" -or $testKey.Value -match "/") {
        Write-Host "Secret contains + or / and may not authenticate correctly. Regenerating..." -ForegroundColor Yellow
        $pw = Initialize-AppKey
        $testKey = New-AppKey -fromDate $fromDate -durationInYears $durationInYears -pw $pw
    }
    Write-Host "Secret doesn't contain + or /. Continuing..." -ForegroundColor Green
    $key = $testKey
   
    return $key
}
   
Function Initialize-AppKey {
    $aesManaged = New-Object "System.Security.Cryptography.AesManaged"
    $aesManaged.Mode = [System.Security.Cryptography.CipherMode]::CBC
    $aesManaged.Padding = [System.Security.Cryptography.PaddingMode]::Zeros
    $aesManaged.BlockSize = 128
    $aesManaged.KeySize = 256
    $aesManaged.GenerateKey()
    return [System.Convert]::ToBase64String($aesManaged.Key)
}
function Confirm-MicrosoftGraphServicePrincipal {
    $graphsp = Get-AzureADServicePrincipal -All $true | where-object {$_.displayname -eq "Microsoft Graph"}
    if (!$graphsp) {
        $graphsp = Get-AzureADServicePrincipal -SearchString "Microsoft.Azure.AgregatorService"
    }
    if (!$graphsp) {
        Login-AzureRmAccount -Credential $credentials
        New-AzureRmADServicePrincipal -ApplicationId "00000003-0000-0000-c000-000000000000"
        $graphsp = Get-AzureADServicePrincipal -SearchString "Microsoft Graph"
    }
    return $graphsp
}
Write-Host "Connecting to Azure AD. The login window may appear behind Visual Studio Code."
Connect-AzureAD
   
Write-Host "Creating application in tenant: $((Get-AzureADTenantDetail).displayName)"
   
# Check for the Microsoft Graph Service Principal. If it doesn't exist already, create it.
$graphsp = Confirm-MicrosoftGraphServicePrincipal
   
$existingapp = $null
$existingapp = get-azureadapplication -SearchString $applicationName
if ($existingapp -and $removeExistingAppWithSameName) {
    Remove-Azureadapplication -ObjectId $existingApp.objectId
}
   
# RSPS 
$rsps = @()
if ($graphsp) {
    $rsps += $graphsp
    $tenant_id = (Get-AzureADTenantDetail).ObjectId
    $tenantName = (Get-AzureADTenantDetail).DisplayName
   
    # Add Required Resources Access (Microsoft Graph)
    $requiredResourcesAccess = New-Object System.Collections.Generic.List[Microsoft.Open.AzureAD.Model.RequiredResourceAccess]
    $microsoftGraphRequiredPermissions = Get-RequiredPermissions -reqsp $graphsp -requiredApplicationPermissions $ApplicationPermissions -requiredDelegatedPermissions $DelegatedPermissions
    $requiredResourcesAccess.Add($microsoftGraphRequiredPermissions)
   
    # Get an application key
    $pw = Initialize-AppKey
    $fromDate = [System.DateTime]::Now
    $appKey = Test-AppKey -fromDate $fromDate -durationInYears 99 -pw $pw
   
    Write-Host "Creating the AAD application $applicationName" -ForegroundColor Blue
    $aadApplication = New-AzureADApplication -DisplayName $applicationName `
        -HomePage $homePage `
        -ReplyUrls $homePage `
        -IdentifierUris $appIdURI `
        -LogoutUrl $logoutURI `
        -RequiredResourceAccess $requiredResourcesAccess `
        -PasswordCredentials $appKey `
        -AvailableToOtherTenants $true
       
    # Creating the Service Principal for the application
    $servicePrincipal = New-AzureADServicePrincipal -AppId $aadApplication.AppId
   
    Write-Host "Assigning Permissions" -ForegroundColor Yellow
     
    # Assign application permissions to the application
    foreach ($app in $requiredResourcesAccess) {
        $reqAppSP = $rsps | Where-Object {$_.appid -contains $app.ResourceAppId}
        Write-Host "Assigning Application permissions for $($reqAppSP.displayName)" -ForegroundColor DarkYellow
        foreach ($resource in $app.ResourceAccess) {
            if ($resource.Type -match "Role") {
                New-AzureADServiceAppRoleAssignment -ObjectId $serviceprincipal.ObjectId `
                    -PrincipalId $serviceprincipal.ObjectId -ResourceId $reqAppSP.ObjectId -Id $resource.Id
            }
        }
    }
     
    # This provides the application with access to your customer tenants.
    $group = Get-AzureADGroup -Filter "displayName eq 'Adminagents'"
    Add-AzureADGroupMember -ObjectId $group.ObjectId -RefObjectId $servicePrincipal.ObjectId
 
    Write-Host "App Created" -ForegroundColor Green
     
    # Define parameters for Microsoft Graph access token retrieval
    $client_id = $aadApplication.AppId;
    $client_secret = $appkey.Value
    $tenant_id = (Get-AzureADTenantDetail).ObjectId
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
   
    # Get the access token using grant type password for Delegated Permissions or grant type client_credentials for Application Permissions
   
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
   
    # Try to execute the API call 6 times
   
    $Stoploop = $false
    [int]$Retrycount = "0"
    do {
        try {
            $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
            Write-Host "Retrieved Access Token" -ForegroundColor Green
            # Assign access token
            $access_token = $response.access_token
            $body = $null
   
            $body = Invoke-RestMethod `
                -Uri $UriForApplicationPermissionCall `
                -Headers @{"Authorization" = "Bearer $access_token"} `
                -ContentType "application/json" `
                -Method GET  `
   
            Write-Host "Retrieved Graph content" -ForegroundColor Green
            $Stoploop = $true
        }
        catch {
            if ($Retrycount -gt 5) {
                Write-Host "Could not get Graph content after 6 retries." -ForegroundColor Red
                $Stoploop = $true
            }
            else {
                Write-Host "Could not get Graph content. Retrying in 5 seconds..." -ForegroundColor DarkYellow
                Start-Sleep -Seconds 5
                $Retrycount ++
            }
        }
    }
    While ($Stoploop -eq $false)
   
    $appInfo = [pscustomobject][ordered]@{
        ApplicationName        = $ApplicationName
        TenantName             = $tenantName
        TenantId               = $tenant_id
        clientId               = $client_id
        clientSecret           = $client_secret
        ApplicationPermissions = $ApplicationPermissions
    }
       
    $AppInfo | Export-Csv C:\temp\AzureADApp.csv -Append -NoTypeInformation
}
else {
    Write-Host "Microsoft Graph Service Principal could not be found or created" -ForegroundColor Red
}

Script 2 – Syncing Mailbox Usage Reports with a SharePoint List

From here on, I’ll refer to this script as the syncing script.

This script will run through your customers and retrieve the mailbox usage reports. For each mailbox in the report, it will create or update an item in SharePoint. If you decide to complete this guide in it’s entirety, the script will then retrieve all the items, check for any over 90% capacity and send you an email via Microsoft Flow with a list of large mailboxes.

1. Double click the below script to select it.
2. Copy and paste the script into a new file in Visual Studio Code and save it with a .ps1 extension
3. Replace $appId, $secret, and $ourTenantId with your client ID, client secret and Tenant Id values respectively.
4. Press F5 to run the script and wait for it to complete.
5. Log onto your root SharePoint site at https://yourtenant.sharepoint.com
6. Click the settings cog on the top right, and select Site Contents
7. Locate the Mailbox Usage Register script
8. Click the percentageUsed column, then select Column Settings – Format this column
9. Paste in the following JSON and click Save

   {
       "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
       "elmType": "div",
       "txtContent": "@currentField",
       "attributes": {
           "class": "sp-field-dataBars"
       },
       "style": {
           "width": "=toString(@currentField) + '%'",
           "background-color": "=if(@currentField <= 70,'#F1F7CF', if(@currentField <= 85, '#FFF1CC', if(@currentField <= 95, '#FBD9CD','#FACFD3')))",
           "border-top-color": "=if(@currentField <= 70,'#DAF732', if(@currentField <= 85, '#FAC332', if(@currentField <= 95, '#F66933','#F52F41')))"
       }
   }

   

10. You should now have a coloured bar behind your percentage used columns.
11. You can choose to sort the list by percentage used, then save the view as default by clicking All Items, Save view as.
12. If you just want to run this script every now and then, you can stop here. If you want to set it up to run on a schedule, or send your support team notifications of larger mailboxes, keep scrolling past the syncing script.

PowerShell script to sync Mailbox Usage Reports with a SharePoint list

$appId = "ENTER_CLIENTID_HERE"
$secret = "ENTER_CLIENTSECRET_HERE"
$ourTenantId = "ENTER_TENANTID_HERE"
$ListName = "Mailbox Usage Register"
$YourCompanyName = "YourCompanyName"
$siteid = "root"
$graphBaseUri = "https://graph.microsoft.com/beta"

$sizeThreshold = 90
$flowUri = $null
$supportEmail = $null
$functionUri = $null

function Get-GCITSAccessToken($appCredential, $tenantId) {
    $client_id = $appCredential.appID
    $client_secret = $appCredential.secret
    $tenant_id = $tenantid
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
    $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
    $access_token = $response.access_token
    return $access_token
}

function Get-GCITSMSGraphResource($Resource) {
    $graphBaseUri = "https://graph.microsoft.com/beta"
    $values = @()
    $result = Invoke-RestMethod -Uri "$graphBaseUri/$resource" -Headers $headers
    if ($result.value) {
        $values += $result.value
        if ($result."@odata.nextLink") {
            do {
                $result = Invoke-RestMethod -Uri $result."@odata.nextLink" -Headers $headers
                $values += $result.value
            } while ($result."@odata.nextLink")
        }
    }
    else {
        $values = $result
    }
    return $values
}
function New-GCITSSharePointColumn($Name, $Type, $Indexed, $lookupListName, $lookupColumnPrimaryName, $lookupColumnName, $longText) {
    if ($longText) {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{
                maxLength          = 0
                allowMultipleLines = $True
            }
            
        }  
    }
    else {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{}
        }
     
        if ($lookupListName -and $type -contains "lookup") {
            $list = Get-GCITSSharePointList -ListName $lookupListName
            if ($list) {
                $column.lookup.listId = $list.id
                $column.lookup.columnName = $lookupColumnName
            }
        }
    }

    return $column
}
function New-GCITSSharePointList ($Name, $ColumnCollection) {
    $list = @{
        displayName = $Name
        columns     = $columnCollection
    } | Convertto-json -Depth 10
      
    $newList = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method POST -Body $list
    return $newList
}
 
function Remove-GCITSSharePointListItem ($ListId, $ItemId) {
    $removeItem = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/$ListId/items/$ItemId" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method DELETE
    return $removeItem
}
 
function New-GCITSSharePointListItem($ItemObject, $ListId) {
 
    $itemBody = @{
        fields = $ItemObject
    } | ConvertTo-Json -Depth 10
 
    $listItem = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method Post `
        -Body $itemBody
}
 
function Get-GCITSSharePointListItem($ListId, $ItemId, $Query) {
 
    if ($ItemId) {
        $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId `
            -Method Get -headers $SPHeaders `
            -ContentType application/json
        $value = $listItem
    }
    elseif ($Query) {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items/?expand=fields&`$filter=$Query" `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    else {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items?expand=fields `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    return $value
}
 
function Set-GCITSSharePointListItem($ListId, $ItemId, $ItemObject) {
    $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId/fields `
        -Method Patch -headers $SPHeaders `
        -ContentType application/json `
        -Body ($itemObject | ConvertTo-Json)
    $return = $listItem
}

function Get-GCITSSharePointList($ListName) {
    $list = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists?expand=columns&`$filter=displayName eq '$ListName'" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method GET
    $list = $list.value
    return $list
}

$appCredential = @{
    AppId  = $appid
    Secret = $secret
}


$accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $ourTenantId
$SPHeaders = @{Authorization = "Bearer $accesstoken"}

$headers = @{
    Authorization = "Bearer $accesstoken"
}

$customers = @()
$customers += @{
    customerid        = $ourtenantid
    displayName       = $YourCompanyName
}
$customers += Get-GCITSMSGraphResource -Resource contracts
$existingItems = $null
foreach ($customer in $customers) {
    Write-Output $customer.displayName
    $tenant_id = $customer.customerid
    $accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $tenant_id
    $headers = @{
        Authorization = "Bearer $accessToken"
    }
    $accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $ourTenantId
    $SPHeaders = @{Authorization = "Bearer $accesstoken"}

    try {
        $MailboxUsage = $null
        $users = $null
        $MailboxUsage = Get-GCITSMSGraphResource -Resource "reports/getMailboxUsageDetail(period='D90')?`$format=application/json"
        $users = Get-GCITSMSGraphResource -Resource users
    }
    catch {
        Write-Output "Couldn't retrieve report or users for $($customer.displayName)"
    }
    

    $mailboxReportArray = @()
    if ($MailboxUsage -and $users) {
        foreach ($mailbox in $MailboxUsage) {
            $user = $users | Where-Object {$_.userPrincipalName -eq $mailbox.userPrincipalName}
            if ($user) {
                $object = [pscustomobject][ordered]@{
                    Title              = $mailbox.userPrincipalName
                    Customer           = $customer.displayName
                    TenantId           = $customer.customerId
                    MailboxDisplayName = $mailbox.displayName
                }
                $percentageUsed = [math]::round(($mailbox.storageUsedInBytes / $mailbox.prohibitSendReceiveQuotaInBytes * 100), 0)
                $object | Add-Member percentageUsed $percentageUsed
                $object | Add-Member storageUsedInGB "$([math]::round([double]$mailbox.storageusedinbytes / 1GB, 2)) GB" -Force
                $object | Add-Member issueWarningQuotaInGB "$([math]::round([double]$mailbox.issueWarningQuotaInBytes / 1GB, 2)) GB" -Force
                $object | Add-Member prohibitSendQuotaInGB "$([math]::round([double]$mailbox.prohibitSendQuotaInBytes / 1GB, 2)) GB" -Force
                $object | Add-Member prohibitSendReceiveQuotaInGB "$([math]::round([double]$mailbox.prohibitSendReceiveQuotaInBytes / 1GB, 2)) GB" -Force
                $object | Add-Member Acknowledged $null
                $object | Add-Member AlwaysIgnore $null
                $members = $mailbox.psobject.Properties | Where-Object { $_ -notmatch "name" -and $_ -notmatch "odata.type" -and $_ -notmatch "reportPeriod"}
                foreach ($member in $members) {
                    $object | Add-Member $member.name $mailbox.$($member.name)
                }
                $object | Add-Member objectId $user.id
                $mailboxReportArray += $object
            }
        }

        if (!$list) {
            $list = Get-GCITSSharePointList -ListName $ListName
            if (!$list -and $mailboxReportArray) {
                $columnCollection = @()
                $columnNames = $mailboxReportArray[0].psobject.Properties.name | Where-Object {$_ -ne "Title"}
                foreach ($column in $columnNames) {
                    if ($column -eq "percentageUsed" -or $column -like "*Bytes" -or $column -eq "itemCount" -or $column -eq "deleteditemcount") {
                        $columnCollection += New-GCITSSharePointColumn -Name $column -Type number -Indexed $true
                    }
                    elseif ($column -eq "isDeleted" -or $column -eq "Acknowledged" -or $column -eq "AlwaysIgnore") {
                        $columnCollection += New-GCITSSharePointColumn -Name $column -Type boolean -Indexed $false
                    }
                    else {
                        $columnCollection += New-GCITSSharePointColumn -Name $column -Type text -Indexed $true
                    }
                }
                $list = New-GCITSSharePointList -Name $ListName -ColumnCollection $columnCollection
            }   
        }
        $existingItems = Get-GCITSSharePointListItem -ListId $list.id
        foreach ($mailbox in $mailboxReportArray) {
            if ($existingItems.fields.objectid -contains $mailbox.objectId) {
                $match = $existingItems | Where-Object {$_.fields.objectid -eq $mailbox.objectid}
                if ($match.fields.Acknowledged -and $match.fields.reportRefreshDate -eq $mailbox.reportRefreshDate) {
                    $mailbox.Acknowledged = $match.fields.Acknowledged
                }
                $mailbox.AlwaysIgnore = $match.fields.AlwaysIgnore
                try {
                    Set-GCITSSharePointListItem -ListId $list.id -ItemId $match.id -ItemObject $mailbox
                }
                catch {
                    Write-Output "Couldn't update SharePoint list item for $($mailbox.title), retrying"
                    try {
                        Set-GCITSSharePointListItem -ListId $list.id -ItemId $match.id -ItemObject $mailbox
                    }
                    catch {
                        Write-Output "Couldn't update SharePoint list item for $($mailbox.title) again."
                    }
                }
               
            }
            else {
                try {
                    New-GCITSSharePointListItem -ListId $list.id -ItemObject $mailbox
                }
                catch {
                    Write-Output "Couldn't create SharePoint list item for $($mailbox.title), retrying"
                    try {
                        New-GCITSSharePointListItem -ListId $list.id -ItemObject $mailbox
                    }
                    catch {
                        Write-Output "Couldn't create SharePoint list item for $($mailbox.title) again."
                    }
                }

            }
        }
    }
}
$existingItems = Get-GCITSSharePointListItem $list.id

# Cleaning up old items
$oldItems = $existingItems | Where-Object {[datetime]$_.fields.modified -lt (Get-Date).AddDays(-7)}

foreach ($oldItem in $oldItems) {
    Remove-GCITSSharePointListItem -ListId $list.id -ItemId $oldItem.id
}
if ($flowUri) {
    $largeMailboxes = ($existingItems | Where-Object {$_.fields.percentageUsed -ge $sizeThreshold -and !$_.fields.Acknowledged -and !$_.fields.AlwaysIgnore}).fields
    
    if ($largeMailboxes) {
        $largeMailboxArray = @()
        $largeMailboxes = $largeMailboxes | Sort-Object percentageUsed -Descending
        foreach ($largeMailbox in $largeMailboxes) {
            $Acknowledge = "$($functionUri)&id=$($largeMailbox.id)&action=acknowledge"
            $Ignore = "$($functionUri)&id=$($largeMailbox.id)&action=ignore"
            $object = [pscustomobject][ordered]@{
                Customer              = $largeMailbox.customer
                'User Principal Name' = $largeMailbox.Title
                'Mailbox Usage'       = "$($largeMailbox.percentageUsed)%"
                'Storage Used'        = $largeMailbox.storageUsedInGB
                'Mailbox Size'        = $largeMailbox.prohibitSendReceiveQuotaInGB
            }
            if ($functionUri) {
                $actionsString = "<a href=`"$Acknowledge`">Acknowledge</a> | <a href=`"$Ignore`">Ignore</a>"
                $object | Add-Member Actions $actionsString
            }
            $largeMailboxArray += $object
        }
        $tableHTML = ($largeMailboxArray | ConvertTo-Html -Fragment) -join ""
        $tableHtml = $tableHTML -replace "&lt;", "<"
        $tableHtml = $tableHTML -replace "&gt;", ">"
        $tableHtml = $tableHTML -replace "&quot;", "`""
        
        $flowBody = @{
            email   = $supportEmail
            subject = "Office 365 Mailboxes with at least $sizeThreshold% Usage"
            body    = $tableHTML
        } | ConvertTo-Json -Depth 10
    
        Invoke-RestMethod -Method POST -Uri $flowUri -Body $flowBody -ContentType application/json
    }
}

Setting up automatic notifications for large mailboxes

To get notified of mailboxes that are approaching their limit, we first need to set up a Microsoft Flow to send an email on our behalf.

Create a HTTP Triggered Microsoft Flow

I like to create a general purpose HTTP triggered flow which can take a JSON object containing an email address, subject and message body.

1. You can set this up by logging into Microsoft Flow at flow.microsoft.com
2. Create a new Flow by clicking My flows, + New, Create from blank.
3. Click Search hundred of connectors and triggers, then search for a HTTP Request Trigger.
4. Copy and paste the following JSON for the Request Body JSON Schema

   {
       "type": "object",
       "properties": {
           "email": {
               "type": "string"
           },
           "subject": {
               "type": "string"
           },
           "body": {
               "type": "string"
           }
       }
   }

5. Add a Send An Email (V2) Action and drag in the email, subject and body outputs from the HTTP Request step..
6. Save the Flow
7. Copy the HTTP POST URL from the flow trigger
8. Add it into the $flowUri value in the syncing script and update the $supportEmail variable in the syncing script with your own email while testing.
9. If you run the syncing script now by pressing F5, you should receive an email with a list of mailboxes that are using over 90% capacity. (In this example I set the threshold to 80% to return more results)

Preventing alert fatigue

You might already be thinking that this is the kind of report that can cause alert fatigue – especially if it’s always the same mailboxes that are at capacity.

Fortunately, we can use a HTTP triggered Azure Function to make the alert email a little more interactive. In this example we are creating two buttons – Acknowledge and Ignore.

Acknowledge will suppress alerts for a mailbox for a particular report date, and start sending alerts again if the mailbox is near capacity on the next report refresh. Microsoft gathers its mailbox usage reports every few days, so if your support team resolves a mailbox size alert on the first day, you don’t want them to get notified about the same mailbox every day until the report refreshes.

Ignore will suppress alerts for a particular mailbox forever – or until you edit the SharePoint list item for the mailbox to clear the AlwaysIgnore field.

Create HTTP and Timer Triggered Azure Functions

1. Log in to portal.azure.com and create an Azure Function app if you don’t already have one.

Note: when setting up an Azure Function app, you’ll be prompted to choose whether you want it on a Consumption Plan or App Service Plan.

Consumption plans are much cheaper than App Service Plans, however the scripts can only run for up to 5 minutes. App Service Plans allow you to run long scripts, but you’re paying for a reserved underlying VM. If you have spare Azure credits I recommend using the App Service plan for your new function app.

If you don’t use Azure Functions on an app service plan already, you may decide to run the main syncing script on a secure server with a scheduled task, and run the alert handler HTTP function in an Azure Function app on a consumption plan.

2. Once you have set up your function app, we’ll create a new Azure Function inside it.
3. Choose to Create a HTTP Triggered PowerShell Azure Function. You may need to ensure that Experimental Language Support is enabled on the top right.
4. Open the function and paste in the below script (HTTP Triggered Azure Function Script), remember to update the $appId, $secret and $ourtenantid values with the same values from the syncing script.
5. Click Save
6. Click Get function URL and copy url of the function.
7. Paste it into the $functionUri value of the syncing script
8. Press F5 to run the syncing script and confirm that you receive an email with a list of mailboxes using over 90% capacity.
9. You should be able to click the Acknowledge or Ignore links and have the items in SharePoint update to reflect this.
10. Once you are happy with the syncing script, create a Timer Triggered PowerShell Azure Function. I called this one TT-SyncMailboxUsageToSharePoint.
11. You can have it run each day using the following cron trigger. This one starts each day at 8AM Australian Eastern Time.

    0 0 22 * * *

12. Make sure that you’ve updated the $supportEmail value if required, then copy and paste the syncing script into the new Azure Function and click save.

HTTP Triggered Azure Function Script

$appId = "ENTER_CLIENTID_HERE"
$secret = "ENTER_CLIENTSECRET_HERE"
$ourTenantId = "ENTER_TENANTID_HERE"
$ListName = "Mailbox Usage Register"
$siteid = "root"
$graphBaseUri = "https://graph.microsoft.com/v1.0"


function Get-GCITSAccessToken($appCredential, $tenantId) {
    $client_id = $appCredential.appID
    $client_secret = $appCredential.secret
    $tenant_id = $tenantid
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
    $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
    $access_token = $response.access_token
    return $access_token
}
 
function Get-GCITSSharePointListItem($ListId, $ItemId, $Query) {
 
    if ($ItemId) {
        $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId `
            -Method Get -headers $SPHeaders `
            -ContentType application/json
        $value = $listItem
    }
    elseif ($Query) {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items/?expand=fields&`$filter=$Query" `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    else {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items?expand=fields `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    return $value
}
 
function Set-GCITSSharePointListItem($ListId, $ItemId, $ItemObject) {
    $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId/fields `
        -Method Patch -headers $SPHeaders `
        -ContentType application/json `
        -Body ($itemObject | ConvertTo-Json)
    $return = $listItem
}
function Get-GCITSSharePointList($ListName) {
    $list = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists?expand=columns&`$filter=displayName eq '$ListName'" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method GET
    $list = $list.value
    return $list
}

$appCredential = @{
    AppId  = $appid
    Secret = $secret
}


$accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $ourTenantId
$SPHeaders = @{Authorization = "Bearer $accesstoken"}


if ($req_query_id -and $req_query_action) {
    $id = $req_query_id
    $action = $req_query_action
    $list = Get-GCITSSharePointList -ListName $ListName

    if ($list) {
        $item = Get-GCITSSharePointListItem -ListId $list.id -ItemId $id
        if ($item) {
            if ($action -eq "ignore") {
                $object = @{
                    AlwaysIgnore = $true
                }
            }
            elseif ($action -eq "acknowledge") {
                $object = @{
                    Acknowledged = $true
                }
            }
            Set-GCITSSharePointListItem -ListId $list.id -ItemId $id -ItemObject $object
        }
    }
}

Out-File -Encoding Ascii -FilePath $res -inputObject "Processed '$action' for item with id '$id'"