This page will show you a way to use the Office 365 management API, Microsoft Graph, Powershell and PowerBI to create a graph displaying Office 365 Health history and your License trends and usage.

Follow these steps to Create an Enterprise App to be used by powershell to access the Office 365 management API:

Go to and log on to the Azure or Office365 portal:

• Azure Active Directory → App Registrations → All Applications
• New Application Registration
  • Name: Office365AutomaticMonitoring
  • Application type: Web app / API
  • Sign-on URL: http://localhost
• Click create and note the Application ID
• Click Settings → Keys. Create a new key by filling in the following fields below Passwords:
  • Description: monkey
  • Expires: Never expires
• Click save and note the Value (the value will never be showed again)
• Go to Required Permissions
  • Delete the default assigned permission for Windows Azure Active Directory
  • Click Add, and select an API: Office 365 Management API
  • Select Permissions: “Read Service Health information from your organization”
  • Click Done
• Repeat for API Microsoft Graph with Application Permission “Read directory data”
• Click Grant permissions to actually assign the permissions and confirm by clicking yes.

Note that if you forgot to click the Grant permissions button you can expect an error like this if you run the PowerShell script:

Invoke-RestMethod : System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Microsoft.Office365ServiceComms.Common.OAuthAuthorizeAttribute.OnAuthorization(HttpActionContext actionContext)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.OnAuthorizationAsync(HttpActionContext actionContext,
CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
... <cut --- and more errors like the above ---> ...
At line:1 char:1
+ Invoke-RestMethod -Uri "https://manage.office.com/api/v1.0/$($tenantd ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc
   eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

The following script needs to be modified for your own purposes and details but will give you the state of all Office 365 services you are entitled to:

# Author: Sjoerd Hooft / https://www.linkedin.com/in/sjoerdhooft/
 
### Versioning and functionality ##################################################################
### 2019 03 20 - Sjoerd Hooft ###
# BeginOfDay / EndofDay - Office 365
### Fetches current status of Office 365 services (you are eligible for depending on subscription)  
### Fetches current subscriptions and licenses for these subscriptions 
### Create transposed csv files 
### Combine csv files to preserve history 
### Sends out warning per email if a service or subscription is added or removed (checks for count)
###
###################################################################################################
 
### Bugs ##########################################################################################
### If the script is run twice in the same minute PowerBI will add up the statusses in the visuals. 
### Do not save the csv output files in excel, this might mess up the timestamps format.
###################################################################################################
 
### How-To ########################################################################################
# Run the script on an hourly bases to collect and store the status. Keep services info for shorter periods and license information longer for trend analysis. 
###################################################################################################
 
### Script Overview ###############################################################################
### Fase 1
# Set script variables
# Start Logging
# Check for required modules
# All other Variables 
# Import default functions 
# Custom Functions 
### Fase 2
# Collect all raw data from Office 365 and Graph 
### Fase 3
# Services: Transpose, combine and cleanup the information
### Fase 4
# Licenses: Transpose, combine and cleanup the information 
### Fase 5
# Store the data on Sharepoint Online. This is important as these files are connected to PowerBI for visualizing the information.
# Send out warnings per email 
# Stop logging 
###################################################################################################
 
########################################## Start Fase 1 ###########################################
 
# Script Variables
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
 
# Start transcript for full logging capabilities
start-transcript -path "$scriptlocation\logtranscript.txt"
 
# Check Required Modules
# if (Get-Module -ListAvailable -Name ActiveDirectory) {
    # Write-Host "Module ActiveDirectory exists"}
# else {Write-Host "Module ActiveDirectory does not exist"}
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
    Write-Host "Module Microsoft.Online.SharePoint.PowerShell exists"
	# Module needs to be imported and Installed: #Install-Module -Name Microsoft.Online.SharePoint.PowerShell
	Import-Module Microsoft.Online.SharePoint.PowerShell}
else {Write-Host "Module Microsoft.Online.SharePoint.PowerShell does not exist"}
 
#Report Variables 
#$date = (Get-Date).ToString('yyyy/MM/dd HH:mm')
#$date = get-date -Format s
$date = get-date
$script:warning = "none"
$csvfileservicesnow = "$scriptlocation\$scriptname-01.csv"
$csvfileservicesnowtransposed = "$scriptlocation\$scriptname-03.csv"
$csvfileservicestransposedtotal = "$scriptlocation\$scriptname-services.csv"
$csvfileservicestransposedtotaltemp = "$scriptlocation\$scriptname-temp.csv"
$csvfilelicensesnow = "$scriptlocation\$scriptname-02.csv"
$csvfilelicensesnowtransposed = "$scriptlocation\$scriptname-04.csv"
$csvfilelicensestransposedtotal = "$scriptlocation\$scriptname-licenses.csv"
### Email Variables 
$mailserver = "smtp"
$toemail = "ict_getshifting.com"
$ccmail = "sjoerd_getshifting.com"
$fromemail = "$scriptname_getshifting.com"
 
### SharePoint Variables
$spusername = "automationaccount_getshifting.com"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "$scriptlocation\spcreds.txt"
$sppassword = get-content "$scriptlocation\..\ICTControlFramework\spcreds.txt" | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $spusername,$sppassword
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
# SharePoint Online Site
$WebUrl = "https://getshifting.sharepoint.com/teams/ICT"
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
$Context.Credentials = $Credentials
# SharePoint Library
$LibraryName = "Documents"
#Foldername can only be one level deep
$FolderName = "Office365Checks"
$Library =  $Context.Web.Lists.GetByTitle($LibraryName)
 
### Tenant settings ###
# see the documentation on Teams on how to obtain them. 
## clientID – this is the Application ID you copied from the App Registration in Azure AD
## clientSecret – this is the Key you copied from the App Registration in Azure AD
## tenantDomain – this is your Office 365 Tenant Domain -> AAD -> Custom domain names -> Name of the primary domain
$clientId="cxxx1c8d-xxx7-41xx-xx21-2fxxdcxxx89"
$clientSecret="Oyyy0rzzzz2DSFRxJyyyy4uSmzzzt1ziJyHwAmxyyyy="
$tenantDomain="getshifting.onmicrosoft.com"
# Primary License 
# $primarysku = "O365_BUSINESS_PREMIUM"
$primarysku = "ENTERPRISEPREMIUM"
 
### Start Functions ###
# Import functions from global function file
#. "$scriptlocation\Functions.ps1"
. "$scriptlocation\Functions.ps1"
 
### Custom Functions ###
# Possible status definitions: https://docs.microsoft.com/en-us/office365/enterprise/view-service-health
# We need to convert these statusses to a number for PowerBI to show them on a nice graph 
Function SetStatus ($StatusDisplayName){
	# Set status based on severity and order of incident handling
	if ($StatusDisplayName -eq "Normal Service"){
		Return "0"
		}
	elseif ($StatusDisplayName -eq "Investigating"){
		Return "7"}
	elseif ($StatusDisplayName -eq "Service degradation"){
		Return "8"}
	elseif ($StatusDisplayName -eq "Service interruption"){
		Return "9"}
	elseif ($StatusDisplayName -eq "Restoring service"){
		Return "6"}
	elseif ($StatusDisplayName -eq "Extended recovery"){
		Return "5"}
	elseif ($StatusDisplayName -eq "Investigation suspended"){
		Return "4"}
	elseif ($StatusDisplayName -eq "Service restored"){
		Return "3"}
	elseif ($StatusDisplayName -eq "Post-incident report published"){
		Return "2"}
	elseif ($StatusDisplayName -eq "False Positive"){
		Return "1"}
	# Return the same value as was given and send out a warning
	else {
		$script:warning = "status"
		Return $StatusDisplayName
	}
}
 
########################################## Start Fase 2 ###########################################
 
# Possible current services: (Invoke-RestMethod -Uri "https://manage.office.com/api/v1.0/$($tenantdomain)/ServiceComms/Services" -Headers $headerParams -Method Get).Value
# You can work these with different approaches, you can filter them in the output of the API commands, or filter them in the PowerBI graph. As the services are subscription dependent (i.e. Business license does not get you enterprise services) and microsoft could add services my approach is to simply add all services here and filter them later in powerbi
 
# Authenticate to Office 365 Management API
$body = @{grant_type="client_credentials";resource="https://manage.office.com";client_id=$ClientID;client_secret=$ClientSecret}
$oauth = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$($tenantdomain)/oauth2/token?api-version=1.0" -Body $body
$headerParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"}
 
# Get Current Status of Office 365 services
$currentStatus = (Invoke-RestMethod -Uri "https://manage.office.com/api/v1.0/$($tenantdomain)/ServiceComms/CurrentStatus" -Headers $headerParams -Method Get).Value | Select WorkloadDisplayName,StatusDisplayName
 
# Authenticate to Graph API
$bodyGraph = @{grant_type="client_credentials";scope="https://graph.microsoft.com/.default";client_id=$ClientID;client_secret=$ClientSecret}
$oauthGraph = Invoke-RestMethod -Method Post -Uri https://login.microsoftonline.com/$tenantdomain/oauth2/v2.0/token -Body $bodygraph
$headerParamsGraph = @{'Authorization'="$($oauthgraph.token_type) $($oauthgraph.access_token)"}
 
# Get licensetype, consumed and available
#$licenseInfo = (Invoke-RestMethod -Method Get -uri "https://graph.microsoft.com/v1.0/subscribedSkus" -Headers $headerParamsGraph).Value | Select skuPartNumber,consumedUnits -ExpandProperty prepaidUnits 
$licenseInfo = (Invoke-RestMethod -Method Get -uri "https://graph.microsoft.com/v1.0/subscribedSkus" -Headers $headerParamsGraph).Value | Select skuPartNumber,consumedUnits,prepaidUnits 
 
# Quick test
#$currentStatus
#$licenseInfo
 
########################################## Start Fase 3 ###########################################
 
# Start services check
$allservices = @()
 
ForEach ($service in $currentstatus){
	#Create temporary object to store information
	$info = "" | Select Name,status
	$info.name = $service.WorkloadDisplayName
	$statusName = $service.StatusDisplayName
	$info.status = SetStatus ($statusName)
	# Load temporary object into array
	$allservices += $info
}
 
#Now add a timestamp
$info = "" | Select Name,status
$info.name = "Date"
$info.status = $date
$allservices += $info
 
# Get number of rows in array so we can check if any services are added or removed
$countservices = $allservices.Count
# Get number of rows in last time csv 
$lasttimecount = (import-csv $csvfileservicesnow).count 
if ($countservices -eq $lasttimecount){
	write-host "The number of services did not change since last time"
}else{
	write-host "The number of services changed since last time"
	# Set a warning variable so we can pick this up later 
	$script:warning = "services"
}
 
# Save the status of this moment to csv file
$allservices | export-csv -NoTypeInformation $csvfileservicesnow
 
# Now transpose the csv file for keeping status history: # Use this to function transpose: https://gallery.technet.microsoft.com/scriptcenter/Transpose-CSV-table-data-33f73d02
## Using external script 
.\Transpose.ps1 -InputFile $csvfileservicesnow | Export-Csv $csvfileservicesnowtransposed -NoTypeInformation
 
#Append now to total 
$now = import-csv $csvfileservicesnowtransposed
$now | export-csv $csvfileservicestransposedtotal -NoTypeInformation -Append
 
# Cleanup services file
## Number of days you want to keep
$cleanupdaysservices = 8
#$cleandate = (Get-Date).adddays(-$cleanupdaysservices).ToString('yyyy/MM/dd HH:mm')
#$cleandate = Get-Date (Get-Date).adddays(-$cleanupdaysservices) -Format s
$cleandate = (Get-Date).adddays(-$cleanupdaysservices)
# Actual cleanup
Write-Host "Services: We always clean up the rows older than $cleanupdaysservices days, so all rows with a datestamp before $cleandate will be removed."
#Because the date will be imported as string we need to convert it to Date
$cleanupcsv = import-csv $csvfileservicestransposedtotal | where {(get-date $_.date) -ge $cleandate}
$cleanupcsv | export-csv $csvfileservicestransposedtotal -NoTypeInformation
 
########################################## Start Fase 4 ###########################################
 
# Start License checks
$alllicenses = @()
 
ForEach ($license in $licenseInfo){
	#Create temporary object to store information
	$info = "" | Select Name,InUse
	$licensename = $license.skuPartNumber
	$prepaid = $license | select -ExpandProperty prepaidUnits
	$available = $prepaid.enabled
	if ($licensename -eq $primarysku){$primaryavailable = $prepaid.enabled}
	$info.name = "$licensename - $available "
	$info.InUse = $license.consumedUnits
	# Load temporary object into array
	$alllicenses += $info
}
 
$info = "" | Select Name,InUse
$info.name = "Date"
$info.InUse = $date
$alllicenses += $info
 
# Get the maximum licences available for the primary licence (usually the E5 premium package)
$info = "" | Select Name,InUse
$info.name = "PrimaryMaxLicense"
$info.InUse = $primaryavailable
$alllicenses += $info
 
# Get number of rows in array so we can check if any license subscriptions are added or removed
$countlicenses = $alllicenses.Count	
# Get number of rows in last time csv 
$lasttimecount = (import-csv $csvfilelicensesnow).count 
if ($countlicenses -eq $lasttimecount){
	write-host "The number of license subscriptions did not change since last time"
}else{
	write-host "The number of license subscriptions changed since last time"
	# Set a warning variable so we can pick this up later 
	$script:warning = "licenses"
}
 
$alllicenses | export-csv -NoTypeInformation $csvfilelicensesnow
 
# Now transpose the csv file for keeping status history: # Use this to function transpose: https://gallery.technet.microsoft.com/scriptcenter/Transpose-CSV-table-data-33f73d02
## Using external script 
.\Transpose.ps1 -InputFile $csvfilelicensesnow | Export-Csv $csvfilelicensesnowtransposed -NoTypeInformation
 
#combine now with total 
$now = import-csv $csvfilelicensesnowtransposed
 
$now | export-csv $csvfilelicensestransposedtotal -NoTypeInformation -Append
 
# Cleanup licenses file
## Number of days you want to keep
$cleanupdayslicenses = 180
#$cleandatelicenses = (Get-Date).adddays(-$cleanupdayslicenses).ToString('yyyy/MM/dd HH:mm')
#$cleandatelicenses = Get-Date (Get-Date).adddays(-$cleanupdayslicenses) -Format s
$cleandatelicenses = (Get-Date).adddays(-$cleanupdayslicenses)
# Actual cleanup
Write-Host "Licenses: We always clean up the rows older than $cleanupdayslicenses days, so all rows with a datestamp before $cleandatelicenses will be removed"
$cleanupcsv = import-csv $csvfileservicestransposedtotal | where {(Get-Date $_.date) -ge $cleandatelicenses}
$cleanupcsv | export-csv $csvfileservicestransposedtotal -NoTypeInformation
 
########################################## Start Fase 5 ###########################################
 
# Send out warning emails if required 
if ($script:warning -ne "none"){
	$priority = "high"
	$subject = "BodEod Office 365 warning on: $script:warning"
	$body = "Check the output from script $scriptname.ps1 in $scriptlocation <br>"
	$body += "If $script:warning is status, then a service gave back a new status. Check <a href=https://docs.microsoft.com/en-us/office365/enterprise/view-service-health>here</a> for changed services statusses. <br>"
	$body += "If $script:warning is services, Microsoft probably added  or removed a service. Check the output and make changes to the required files and PowerBI<br>" 
	$body += "If $script:warning is licenses, you probably got a new subscription or removed one. Check the output and make changes to the required files and PowerBI<br>"
	Send-Email $subject $body
}
 
# Upload reports to sharepoint so powerbi can pick them up 
Upload-Report $csvfileservicestransposedtotal
 
Upload-Report $csvfilelicensestransposedtotal
 
Stop-Transcript

You need to schedule the script above according to your needs. I had very good results by scheduling the script every hour. Scheduling powershell scripts are explained in Schedule Tasks on Windows Server.

Note that the argument list should be something like -Executionpolicy Bypass -nologo -noninteractive -command "& {\\shift\dfs\SCRIPT_REPOSITORY$\DailyChecks\Office365Information.ps1}"

The functions script that is imported in the script above is explained in Automate compliance in Active Directory.

The transpose functionality is borrowed from here. I tried to convert it to a function or even to just include the code but it all failed. The only way I got it working is by keeping it as a separate script. For your convernience I also included the code here (without description):

    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    Param
    (
        #Custom input file name, default is "ProcessData.csv" in current directory
        [Parameter(Mandatory=$false,
                   Position=0)]
        $InputFile = "office365information-01.csv"
 
 
    )
 
 
 
 
#Extracting header info from CSV file
$pdata = Import-Csv $InputFile
$header = $pdata[0].psobject.properties.name
 
#$header[0]
 
$pdataT = New-Object –TypeName PSObject
 
 
#Transposing the data
 
#Creating the object
foreach ($para in $header)
{
 
#Find current position
#$header.IndexOf($para))
 
$pdataT | Add-Member –MemberType NoteProperty –Name $para -Value $null
 
    foreach ($obj in $pdata)
    {
 
    $pdataT | Add-Member –MemberType NoteProperty –Name $obj.$($para) -Value $null
 
 #$obj.$($para)
    }
 
  #Breaking as taking only first column data as rows\parameter
  break
 
}
 
 
 
 
#Extracting new header info
$newHeader = $pdataT[0].psobject.properties.name
 
#Blank array for result
$objResult = @()
 
 
# Value filling
foreach ($para in $Header)
 {
 
#Creating new object for every item
$objTemp = $pdataT | Select-Object *
 
  #Skipping the first column of old data, which is already header.
  if($header.IndexOf($para) -eq 0){continue}
 
  #First obj to be fillup by old header data
  $i = 0
  $objTemp.$($newHeader[$i]) = $para
 
   foreach ($obj2 in $pdata)
    {
 
    $i = $pdata.IndexOf($obj2) + 1
 
    #"$($newHeader[$i]) = $($obj2.Status)"
    $objTemp.$($newHeader[$i]) = $obj2.$para
 
    }
 
  #  $objResult += $pdataT
  #$objTemp | ft
  #Adding the objects to array
  $objResult += $objTemp
 }
 
 Write-Output $objResult

If the script ran successful the excel file now resides on Sharepoint Online. My advice would be to run the script a couple of times so you actually have some data before you continue so you can see some results right away. This is not necessay.

I already described how to import an excel file on SharePoint Online here.

All I want is to display a historicak view of the service status in Office 365:

Note that the maximum value is chosen for each of the values in the graph.

Create the visual like this to display a long term trend graph for your license usage. Note that the data available now is only for a week, but this will grow to a half year:

Create the visual like this to display the current number of licenses in use:

This is an example on how the dashboard could look like:

• Source explaining on using MS Graph en API -very useful
• Microsoft on getting started with the Office 365 Management API
• PowerShell Transpose Script to change the rows and columns in a csv file
• Office 365 Different Statuses
• Microsoft Graph Explorer