These two tools work well if you want to get started with adoption and get an overview on the current situation and what effects user training for a specific service might have for example. If you want to take it to the next level there are endless possibilities for customization if you read the usage data and manage it thought scripts and apps.

Retrieve Office 365 usage data with Microsoft Graph

In the Office 365/Microsoft 365 admin portal you can export the usage data in CSV format. The CSV data that you get in the portal is the same as you get if you query Microsoft Graph (Microsoft is probably using Graph as well). Because I live and brethe PowerShell I chose to work with Microsoft Graph through a PowerShell script but it’s also possible t9 do this from a .NET application or anything that can talk with a REST API.

With a couple of smart tricks in PowerShell we can convert the CSV data that Graph returns to regular PowerShell objects. After that we can work with the objects just as we always do in PowerShell, like Format-Table, Format-List, Out-GridView or why not store the data in a SQL database and use Power BI to analyze it.

Register an app for Microsoft Graph in Azure AD

For our PowerShell script to be able to connect to Graph and retrieve the data we must first register an app in Azure AD and grant it permissions to read user data. We will do this in the Azure Portal.

Go to Azure Active Directory and App registrations.

Create a new app and fill in the form like this.

Note the application ID. We will use this in the script.

Open the properties of the new app and click on Keys. Create a new key by enter a Description and then click Save.

Note the key that will show up under Value (you can only see the key this one time so make sure you save it somewhere).

Click on Required permissions and then on Add.

Chose Microsoft Graph under API.

Chose Read all usage reports under Select permissions and then click Save.

Grant the permissions that we added by clicking Grant Permissions. This must be done by an administrator so if you don’t have administrative permissions in Azure AD you have to ask an administrator to grant the permissions instead.

PowerShell function that connects to Microsoft Graph

This function is a general Graph connect function and it will connect to Graph and then call a URL that will return the answer.

function Get-GraphApi {
 param (
 [parameter(Mandatory=$true)]
 $ClientID,

[parameter(Mandatory=$true)]
 $ClientSecret,

[parameter(Mandatory=$true)]
 $TenantName,

[parameter(Mandatory=$true)]
 $Url
 )


 # Graph API URLs.
 $LoginUrl = "https://login.microsoft.com"
 $RresourceUrl = "https://graph.microsoft.com"
 
 
 # Compose REST request.
 $Body = @{ grant_type = "client_credentials"; resource = $RresourceUrl; client_id = $ClientID; client_secret = $ClientSecret }
 $OAuth = Invoke-RestMethod -Method Post -Uri $LoginUrl/$TenantName/oauth2/token?api-version=1.0 -Body $Body
 
 
 # Check if authentication is successfull.
 if ($OAuth.access_token -eq $null)
 {
 Write-Error "No Access Token"
 }
 else
 {
 # Perform REST call.
 $HeaderParams = @{ 'Authorization' = "$($OAuth.token_type) $($OAuth.access_token)" }
 $Result = (Invoke-WebRequest -UseBasicParsing -Headers $HeaderParams -Uri $Url)

# Return result.
 $Result
 }
}

We will call this function from our Get-UsageReportData function below. Get-UsagReportData is also the function that will handle the raw data that Graph returns and then filter out the CSV content and convert it to PowerShell objects. Note the trick we are using to split “\?\?\?” to remove the raw data that comes before the CSV part in the response from Microsoft Graph.

function Get-UsageReportData {
 param (
 [parameter(Mandatory = $true)]
 [string]$ClientID,

[parameter(Mandatory = $true)]
 [string]$ClientSecret,

[parameter(Mandatory = $true)]
 [string]$TenantName,
 
 [parameter(Mandatory=$true)]
 $GraphUrl
 )

try {
 # Call Microsoft Graph and extract CSV content and convert data to PowerShell objects.
 ((Get-GraphApi -ClientID $ClientID -ClientSecret $ClientSecret -TenantName $TenantName -Url $GraphUrl).RawContent -split "\?\?\?")[1] | ConvertFrom-Csv
 }
 catch {
 $null
 }
}

With these two functions in place it is simple to retreive usage data in the following way.

$ClientID = "e05e2a69-26b0-4496-bc47-befd55b83f16" # You registered apps App ID.
$ClientSecret = "oo87Z52Ckr7Q0AzLKqtFH567Dne4FQvWeVs2rNYG/Pw=" # Your registered apps key.
$TenantName = "yourtenant.onmicrosoft.com" # Your tenant name.
$GraphUrl = "https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(period='D7')" # The Graph URL to retrieve data.

$UsageData = Get-UsageReportData -ClientID $ClientID -ClientSecret $ClientSecret -TenantName $TenantName -GraphUrl $GraphUrl
$UsageData

Example output:

Report Refresh Date : 2018-09-21
User Principal Name : user.username@altitude365.com
Last Activity Date : 2018-09-21
Is Deleted : False
Deleted Date :
Assigned Products : WINDOWS 10 ENTERPRISE E5+ENTERPRISE MOBILITY + SECURITY E5+OFFICE 365 ENTERPRISE E5 WITHOUT AUDIO CONFERENCING
Team Chat Message Count : 4
Private Chat Message Count : 333
Call Count : 3
Meeting Count : 1
Has Other Action : Yes
Report Period : 7

In this case we are retreiving the usage data of all Teams users from the last month. Additional information about this exact Graph URL can be found here:

https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/api/reportroot_getteamsuseractivityuserdetail

All avaiable usage data reports that can be retreived can be found in the menu on this page.

https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/resources/report

Here are some examples of reports that you can get data from.

How Altitude 365 use this

Even though the built-in reports are powerful we can do a lot more when we retreive the usage data and manage it ourselves. Here at Altitude 365 we use the usage data together with Power BI to help our customers with adoption, license optimization, clean-up, security and automatic advisory that would not have been possible without using Microsoft Graph. The customer get amazing intights in how there organization uses Office 365.

If you are interested in building solutions against Microsoft Graph or if you need assistance with adoption, license optimization, and more, Altitude 365 can help you. Contact us if you want to learn more!