Microsoft 365 Usage Reports PowerShell Script

Important!!! - If you're coming from versions below 1.2.5 of this script, you'll need to update your config file from JSON to YAML format. The YAML template is included in this version.

Overview
Requirements
How to Get the Script
Configuration
How to Use the Script
ANNEX
- Register a New Azure AD App
- Creating an Encrypted Exchange Online Credentials File

Overview

This PowerShell script exports the Microsoft 365 usage reports using the Microsoft Graph API v1.0 and ExchangeOnlineManagement PowerShell Module. The results are saved locally and can also be sent by email.

The reports that can be exported using this script are:

Assigned Licenses Report Count
- Exchange
- SharePoint
- OneDrive
- Skype for Business
- Teams
- Yammer
Microsoft 365
- Users
- Activations
Exchange Reports
- Mailbox Status (Active/Inactive)
- Mailbox Provisioning (Created/Deleted)
- Mailbox Quota
- Email App Usage
- Office 365 Groups Provisioning
- Mail Traffic (Inbound/Outbound)
- Spam and Malware Detection (Count)
- ATP Mail Detection (ATP Safe Links, ATP Safe Attachments)
- Top 10 Mail Senders
- Top 10 Mail Recipients
- Top 10 Spam Recipients
- Top 10 Malware Recipients
- Top 10 Malware Detected
SharePoint Reports
- Total, Active, Inactive Sites
- Storage Used (Tenant)
OneDrive Reports
- Total, Active, Inactive Sites
- Storage Used (Tenant)
Skype for Business Reports
- Organizer Minutes
- Participant Minutes
- Peer-to-Peer Minutes
- Total Audio & Video Minutes
- Number of Users
- Total number of conferences
- Total number of peer-to-peer sessions
- Device Usage Distribution
Microsoft Teams
- Total Users
- Active Users
- Inactive Users
- Guest Users
- Total 1:1 Calls
- Total Team Chat Messages
- Total Private Chat Messages
- Device Usage Distribution

Requirements

A registered Azure AD (OAuth) App with the following settings:

Annex: Register a New Azure AD App
- API: Microsoft Graph
- Permission Type: Application
- Permission(s):
  - Reports.Read.All - For reading the usage reports.
  - Directory.Read.All - For getting the deleted Microsoft 365 Groups and users.
  - Mail.Send - For sending the report by email.
Windows PowerShell 5.1.
The ExchangeOnlineManagement PowerShell Module- must be installed on the computer where you will be running this script. The minimum version required is 2.0.3.
The MSAL.PS PowerShell Module must be installed on the computer where you will be running this script. The minimum version required is 4.16.0.4.
The powershell-yaml module must be installed. The script will use this module to process the configuration file. The minimum version required is 0.4.2.
A valid mailbox that will be used for sending the report. A shared mailbox (no license) is recommended.

How to Get the Script

Clone or download the script from the repository. After downloading, extract the files to your preferred location.

Configuration

Make a New Configuration File

The script uses configuration details from a YAML document. You can have many configuration files with different settings if you want.

To create a new configuration, make a copy of the config_template.yml. I recommend for you to use your Microsoft 365 tenant domain as the name of the configuration file for easier identification.

You can use any name for the new configuration file. What's important is the contents, not the file name.

YAML Settings Explained

Open your YAML file using any text editor. It would be best to use an editor that has syntax/language support like Notepad++, Atom, or Visual Studio Code.

The code below shows the default content of the configuration YAML file. The meaning of each setting is explained in the next section.

auth:
  tenantName: <tenant>.onmicrosoft.com
  msGraphAuthType: 2 # 1 = Certificate, 2 = Secret
  msGraphAppID:
  msGraphAppKey:
  msGraphAppCertificateThumbprint:
  exchangeAuthType: 2 # 1 = Certificate, 2 = Secret
  exchangeAppID:
  exchangeAppCertificateThumbprint:
  exchangeCredentialFile:
parameters:
  transLog: false
  saveRawData: false
  period: 30
  showLogo: true
mail:
  sendEmail: false
  fromAddress: sender@domain.com
  toAddress: recipient1@domain.com,recipient2@domain.com
  ccAddress:
  bccAddress:
reports:
  license: true
  ms365ActiveUsers: true
  ms365ActivationUsers: true
  exchangeMailbox: true
  exchangeApp: true
  exchangeTopMailTraffic: true
  exchangeMailTraffic: true
  exchangeATPDetections: true
  Office365Groups: true
  sharepoint: true
  onedrive: true
  SkypeForBusiness: true
  teams: true
developer:
  graphApiVersion: beta
  scriptCompatibleVersion: 1.2.5

AUTH
`tenantName`	This is your Microsoft 365 tenant's organization name. Example: `contoso.onmicrosoft.com`
`msGraphAuthType`	Determines the Graph API credential type. `msGraphAuthType: 1` = Use Certificate `msGraphAuthType: 2` = Use Client Secret
`msGraphAppID`	This is the registered app's Application ID. (refer to Register a New Azure AD App).
`msGraphAppKey`	This is the registered app's Secret Key. (refer to Adding a Client Secret).
`msGraphAppCertificateThumbprint`	This is the registered app's Certificate Thumbprint. (refer to Creating and Uploading a Self-Signed Certificate*).
`exchangeAuthType`	Determines the Exchange Credential authentication type. `exchangeAuthType: 1` = Use Certificate `exchangeAuthType: 2` = Use encrypted username and password
`exchangeAppID`	This is the registered app's Application ID. Refer to Setting Up App-Only Authentication using PowerShell. This is only required if you're using If you're using `exchangeAuthType: 1` If you're using `exchangeAuthType: 2`, you do not need to add a value to this.
`exchangeAppCertificateThumbprint`	This is the registered app's Certificate Thumbprint. Refer to Setting Up App-Only Authentication using PowerShell. This is only required if you're using If you're using `exchangeAuthType: 1` Make sure that the certificate is in the personal certificate store and uploaded to the registered Exchange app in Azure AD. If you're using `exchangeAuthType: 2`, you do not need to add a value to this.
`exchangeCredentialFile`	The file path to the encrypted credential file. Refer to: Creating an Encrypted Exchange Online Credentials File Example: `exchangeCredentialFile: C:\temp\cred.xml`

PARAMETERS
`transLog`	Turn ON or OFF the transcript logging. When turned on, the transcript will be saved to the `<script_root>\transcript` folder. `true` or `false`
`saveRawData`	Turn ON or OFF the saving of raw data. Raw data files are saved to the `<script_root>\reports\<organization name>`. `true` or `false`
`period`	The period covered by the report in days. Valid values are: `7`,`30`,`90`,`180`.
`showLogo`	Show or hide the banner logo on top of the report. `true` or `false`

MAIL
`sendEmail`	Turn ON or OFF the sending of the HTML report by email. The HTML report file is saved to <script_root>\reports<organization name>\report.html `true` or `false`
`fromAddress`	This is the email address used for sending the HTML report. This must be a valid mailbox and email address. Using a shared mailbox as the sender is recommend because it does not required an Exchange Online license. Example: `fromAddress: sender@domain.com`
`toAddress`	The recipient(s) email address that will appear in the TO address. Multiple entries are accepted, but must be delimited by a comma ",". Example: `toAddress: recipient1@domain.com,recipient2@domain.com`
`ccAddress`	The recipient(s) email address that will appear in the CC address. Multiple entries are accepted, but must be delimited by a comma ",". Example: ccAddress: recipient1@domain.com,recipient2@domain.com`
`bccAddress`	The recipient(s) email address that will appear in the BCC address. Multiple entries are accepted, but must be delimited by a comma ",". Example: `bccAddress: recipient1@domain.com,recipient2@domain.com`

REPORTS
`license`	Turn ON or OFF the license assignment count report. `true` or `false`
`sharepoint`	Turn ON or OFF the SharePoint Online reports. `true` or `false`
`onedrive`	Turn ON or OFF the OneDrive for Business reports. `true` or `false`
`SkypeForBusiness`	Turn ON or OFF the Skype for Business reports. `true` or `false`
`teams`	Turn ON or OFF the Microsoft Teams report. `true` or `false`
`Office365Groups`	Turn ON or OFF the Microsoft 365 Groups report. `true` or `false`
`exchangeMailbox`	Turn ON or OFF the Exchange Online Mailbox reports. `true` or `false`
`exchangeApp`	Turn ON or OFF the Exchange Online Email App report. `true` or `false`
`exchangeMailTraffic`	Turn ON or OFF the Exchange Online Mail Traffic reports. `true` or `false`
`exchangeTopMailTraffic`	Turn ON or OFF the Exchange Online Mail Top Traffic reports. `true` or `false`
`exchangeATPDetections`	Turn ON or OFF the Exchange Online Mail ATP detection reports. `true` or `false`
`ms365ActiveUsers`	Turn ON or OFF the Office 365 Active Users report. `true` or `false`
`ms365ActivationUsers`	Turn ON or OFF the Office 365 Activations by Users report. `true` or `false`

DEVELOPER
`graphApiVersion`	DO NOT CHANGE!!! FOR DEVELOPMENT USE ONLY. This defines the Microsoft Graph API version used by the script.
`scriptCompatibleVersion`	DO NOT CHANGE!!! FOR DEVELOPMENT USE ONLY.

Change the Header Logo

To change the report banner logo, replace the <resource>\logo.png file.

How to Use the Script

Syntax

The Get-Ms365UsageReport.ps1 script accepts one (1) mandatory parameter.

-Config - This parameter accepts the path of the YAML configuration file.

.\Get-Ms365UsageReport.ps1 -Config <PATH TO YAML FILE>

Running the Script

Open PowerShell and change the working directory to where you saved the script.
Run the script. In the example below, the configuration file used is poshlab.ml.yml - which is in the same folder as the script.
```
.\Get-Ms365UsageReport.ps1 -config .\poshlab.ml.yml
```

You should see a screen output similar to the one below.

Running the script

Script Output

Transcript File

If transcript logging is enabled, the transcript is saved to the transcript folder.

Raw Data and HTML Report Files

If raw data saving is enabled, the raw data files and HTML report is saved to the reports\[ORGANIZATION]\[REPORT PERIOD] folder.

HTML Report

Email Report

ANNEX

Register a New Azure AD App

Go to the Azure Active Directory admin center and navigate to Azure Active Directory > App registrations. Then, click on New Registration.

In the Name, type in Microsoft 365 Usage Reports.
In the Supported account types, select Accounts in this organizational directory only.
In the Redirect URI (optional), select Web and type in http://localhost.
Click on Register.

After the App has been registered, copy the Application (client) ID and Directory (tenant) ID.

Adding the Required API Permissions

Go to API Permissions and click on the Add a Permission button.

In the Request API Permission, select Microsoft Graph API.

In What type of permissions does your application require? click on Application.

From the list of permissions, search for and enable the following permissions.

Reports.Read.All
Directory.Read.All
Mail.Send

Once you're done selecting the permissions, click on the Add permissions button.

Next, you have two options as to how your application can get authorization. Using a Client Secret or a Self-Signed Certificate.

OPTION 1: Adding a Client Secret

Note: You can use either a Certificate or a Client Secret for API authentication. This example shows you only how to create a client secret.

Go to Certificates & secrets and click on the New client secret button.

In the Add a client secret page:

Type in the Description* box the description you want to use. In this example, the description used is secret key 1.
Select the validity period for this secret. In this example, the secret key Expires In 2 years. Choose which ever one is permitted for your organization.
Click on Add.

Make sure to copy and save the key.

OPTION 2: Creating and Uploading a Self-Signed Certificate

Note: You can use either a Certificate or a Client Secret for API authentication. This example shows you only how to create a certificate.

Copy the code below and run it in PowerShell. The script will create a new self-signed certificate in the personal certificate store with a validity of five (5) years. Then, exports the DER-encoded ms365UsageReport.cer file to the current working directory.

# Create a self-sign certificate in the personal certificate store with 5-year validity.
$certSplat = @{
  Subject = 'CN=ms365UsageReport'
  NotBefore = ((Get-Date).AddDays(-1))
  NotAfter = ((Get-Date).AddYears(5))
  CertStoreLocation = "Cert:\CurrentUser\My"
}
$selfSignedCertificate = New-SelfSignedCertificate @certSplat

# Export the certificate (.CER)
Export-Certificate -Cert $selfSignedCertificate -FilePath .\ms365UsageReport.cer

After running the code, you can see the new certificate is in the personal certificate store.

The new self-signed certificate in the personal certificate store

The new self-signed certificate exported to a file

Now that you have the certificate file (.cer), the next step is to upload the certificate to the AzureAd App. Follow these next steps.

Go to Certificates & secrets and click on the Upload certificate button.
Click the browse button.
Locate the certificate file and click Open.
Click Add.

Uploading a certificate

You should now see the certificate was uploaded. Copy the Thumbprint value.

The certificate is uploaded

Granting Admin Consent

Note: Only a Global Admin can grant consent on behalf of the Microsoft 365 Tenant. If you do not have the proper rights, ask your Global Admin to grant the consent.

You should see that the new API permissions are added, but the status if Not granted for [tenant]. To finish granting the permissions, click on the Grant admin consent for [tenant] button.

When asked to confirm, click on Yes.

The API permissions status should change to Granted for [tenant]

Now you should have the following details available:

Client ID
Secret Key
Tenant ID

Creating an Encrypted Exchange Online Credentials File

If you're using a username + password to authenticate to Exchange Online, then you'll need to save your encrypted credentials to a file.

Note: The Exchange Online credential you'll be using must at least have View-Only Organization Management permission

In PowerShell, enter this command - Get-Credential | Export-CliXml <PATH\TO\FILE.xml>.
When prompted, enter the username and password of the Exchange Online credential to use.

Creating an Encrypted Exchange Online Credentials File

junecastillote/Ms365UsageReport

Microsoft 365 Usage Reports PowerShell Script

Overview

Requirements

How to Get the Script

Configuration

Make a New Configuration File

YAML Settings Explained

Change the Header Logo

How to Use the Script

Syntax

Running the Script

Script Output

Transcript File

Raw Data and HTML Report Files

HTML Report

Email Report

ANNEX

Register a New Azure AD App

Adding the Required API Permissions

OPTION 1: Adding a Client Secret

OPTION 2: Creating and Uploading a Self-Signed Certificate

Granting Admin Consent

Creating an Encrypted Exchange Online Credentials File