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.
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
-
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.
Clone or download the script from the repository. After downloading, extract the files to your preferred location.
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.
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 CertificatemsGraphAuthType: 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 CertificateexchangeAuthType: 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: 1If 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: 1Make 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 |
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.htmltrue 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. |
To change the report banner logo, replace the <resource>\logo.png file.
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>-
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
If transcript logging is enabled, the transcript is saved to the transcript folder.
If raw data saving is enabled, the raw data files and HTML report is saved to the reports\[ORGANIZATION]\[REPORT PERIOD] folder.
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.
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.
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.
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.cerAfter 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
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
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