Description

This tool is a PowerShell script that connects to M365 services and collects the usage data for various workloads. When the script runs completely, all collected information is exported to a CSV file.  

Download page for the scriptPre-RequisitesAuthentication ModesSteps to execute the scriptUseful Powershell commands

Authentication Modes:

This script uses the following two modes of authentication: 

Login with admin user credentials (UserLogin) 

•    This mode will require admin credentials to connect to Exchange Online and SharePoint Online. If authentication fails, the script will use interactive login. After interactive login, the script will not collect size information from the recycling bins of individual sites. 

•    For SharePoint Online, the admin user must be a member of the Owners Group of all SharePoint site collections, else the total size of the site will include the size of the recycle bin as well. 

•    For OneDrive for Business, the output will always include the size of the recycle bin. 

Azure AD app-only authentication (Application) 
An Azure AD application will be created with the following permissions. Admin consent should be granted manually to this app. 

•    Permission required for reading storage usage reports. 

•    Microsoft Graph > Reports.Read.All – Application 

•    Permission required for reading site storage usage and site recycle bin size. 

•    SharePoint > Sites.FullControl.All – Application 

•    SharePoint > User.Read.All – Application 

•    Permission required for reading mailbox usage. 

•    Office 365 Exchange Online > Exchange.ManageAsApp – Application 

•    Total size of SharePoint sites and OneDrive sites will exclude the size of the recycle bin. 

•    Detailed steps for login, using Azure AD is provided in the latter section below.

Pre-Requisites:

The system running this script should have the following PowerShell modules installed. If modules are not installed, then the script installs them during its execution.  

•    PowerShell 5.1
•    Microsoft.Graph (2.1.0 or later)  
•    ExchangeOnlineManagement (version 3.0.0 or later) 
•    SharePointPnPPowerShellOnline (3.29.2101.0 or later) 
•    Az.Accounts (2.12.1) 
•    Az.Resources (6.6.0)
•    Windows Server 2016 and above.

 
Note: The new PnP PowerShell module should not be installed on the system. If multiple versions of the same module are found on the system, the script will prompt users to refer to the documentation. Users must make sure that the installed modules align with the requirements. 
If the UserLogin authentication mode is selected, then to collect the precise output for SharePoint online, the user should have access to all the SharePoint/Teams sites. Users should be part of the owners' group of sites. If the Application authentication mode is selected, users logging into the script should have global admin access to create an Application Registration as well as admin credentials are required. 

Steps to execute the script:

1. Start PowerShell ( Run as administrator) and change directory to where the PS1 script is saved.
Execute the powershell script :  
 .\M365-Sizing-Tool-BE.ps1

2. Choose M365 services for which the usage report is needed. 



3. Select whether to get the total item count for OneDrive for Business and SharePoint or not.(If selecting yes, script execution may take several hours to run)

4. Select Authentication mode. If 2 - Application Authentication Mode is chosen, then review the section Azure AD app-only Login Steps. 

5. When prompted, enter the SharePoint Online admin URL. Normally, it is in the following format. https://-admin.sharepoint.com

Note: Replace tenant with the tenant name of the M365 domain.

6. If the UserLogin authentication mode is selected, the script will prompt you to enter the credentials for the user that has admin access to Exchange Online and SharePoint Online. If Application authentication mode is selected, the script will prompt you to enter the credentials for a user that has global admin access to create Azure AD Application.
 
7. If Application authentication is selected, then an app will be created. This app should be given admin consent. For Exchange Online statistics, this app will also need to be assigned an Exchange Administrator role in Azure AD. 

8. If UserLogin authentication mode is selected, the login window to connect to Microsoft Graph will open. Login with admin credentials. If Application authentication mode is selected, the script will use the application credentials created in step above for connecting to Microsoft Graph. 

9. The report will be exported in CSV format at the same location where the script is run from. The CSV file is generated in the following format: M365-Stats- yyyyMMdd-hhmmss.csv 

10. The script will disconnect all the sessions created while collecting usage data.  

11. An information message will appear on the screen mentioning that the usage data collection is completed and application registration is created while running the script which can be safely removed.  

Important Notes: 

For Exchange Online, the total number of mailboxes, item count, and total size includes: 

•    User mailboxes 
•    Shared mailboxes 
•    Group mailboxes 
•    Public folder mailboxes 
•    Other mailboxes. Such as room mailboxes and equipment mailboxes


For OneDrive, the total number of OneDrive sites and total size includes:
 
•    All personal sites for active users.  
•    Sites not associated with a licensed user account. (Stale sites)
 
The final output in CSV includes combined data size. For individual numbers of different items, refer to logs created at the location of the script. 

The same data can be viewed from Microsoft 365 Admin center. https://admin.microsoft.com/Adminportal/Home?#/reportsUsage 

Learn more about Microsoft 365 usage report from here https://docs.microsoft.com/en-US/microsoft-365/admin/activity-reports/activity-reports?WT.mc_id=365AdminCSH_inproduct 

Download:

To download the latest version of this script, click here. 

Azure AD app-only Login Steps :

1. After logging in with admin credentials to run the script, output similar to the following will be seen:
App Display Name: M365-20231003-034757, App Client Id: 34f39b27-0630-4f6c-a8a...

2. In the above step, App Display Name is the application ID.
 
3. Sign in to the Microsoft Entra ID admin center as administrator.

4. Go to App registrations and search for the application under All applications, as below.

5. Click on the Application ID and go to API Permissions.

6. Click on Grant admin consent for the app as highlighted below:

7. Once admin consent is granted to the app, the following will be seen:

8. In Microsoft Entra ID admin center, click on Roles and Administrator. Search for Exchange Administrator as shown below and click on the role.

9. Double-click on Exhange Administrator and click on Add Assignment.

10. Select the member as the Application ID, as shown below:

11. Fill in the information in the settings tab as shown below, then click Assign:


 

12. When done, a notification will be seen indicating role assignment, as per the example below.

13.    Click Enter to continue.

14. Type Yes or Y at the next prompt Is admin consent given to app ? Grant admin consent and then enter [Y] to continue.

Powershell Command :

1. Find the version of Powershell installed:
   $PSVersiontable

2. Find the version of Az.Accounts or Az.Resources installed:

   Get-Module -ListAvailable -FullyQualifiedName Az.Accounts
   OR
   Get-InstalledModule Az.Accounts -AllVersions

   Get-Module -ListAvailable -FullyQualifiedName Az.Resources
   OR
   Get-InstalledModule Az.Resources -AllVersions

3. To set the session to use TLS 1.2:

   [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12