Real-Time UC

A Universal Communications Blog by Office Apps and Services MVP Michael LaMontagne

Graph Office365 Projects Skype for Business Teams

Get-O365UsageReports: Use Graph to pull all Office 365 Usage Reports to Excel

In December 2017, the new Microsoft Graph reporting APIs were announced: https://techcommunity.microsoft.com/t5/Office-365-Blog/Announcing-the-General-Availability-of-Microsoft-Graph-reporting/ba-p/137838, also in this announcement was the deprecation of the Office 365 Reporting Web Service:

  • ConnectionbyClientType
  • ConnectionbyClientTypeDetail
  • CsActiveUser
  • CsAVConferenceTime
  • CsP2PAVTime
  • CsConference
  • CsP2PSession
  • GroupActivity
  • MailboxActivity
  • GroupActivity
  • MailboxUsage
  • MailboxUsageDetail
  • StaleMailbox
  • StaleMailboxDetail

Details on working with the Office 365 Usage reports via v1.0 API in Microsoft Graph: https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/resources/report

Details on working with the Office 365 Usage reports via beta API in Microsoft Graph: https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/resources/report

I specifically called out the beta API because at this time (Jan, 2018), the beta API has an additional six Microsoft Teams reports:

Armed with an Azure App Client ID with the Reports.Read.All permission created by my Manage-AzureAppRegistration script, I wanted to build a simple script to pull all the Office 365 Usage Reports.

Get-O365UsageReports can be found on the TechNet Gallery: Script Download: Get-O365UsageReports.ps1 https://gallery.technet.microsoft.com/Get-O365UsageReports-954fb5a3

Scrpit v2 no longer needs ADAL DLLs
The script has a prerequisite of having at least one of the following PowerShell Modules installed to load the Active Directory Authentication Library (ADAL) DLL:

Get-O365UsageReports leverages Microsoft Graph to loop through ~70 Office Usage Reports, generating a single Excel file (worksheet per report) and returns a hashtable with all the results.

Current Office 365 Usage Report list (beta):

  • getEmailActivityUserDetail
  • getEmailActivityCounts
  • getEmailActivityUserCounts
  • getEmailAppUsageUserDetail
  • getEmailAppUsageAppsUserCounts
  • getEmailAppUsageUserCounts
  • getEmailAppUsageVersionsUserCounts
  • getMailboxUsageDetail
  • getMailboxUsageMailboxCounts
  • getMailboxUsageQuotaStatusMailboxCounts
  • getMailboxUsageStorage
  • getOffice365ActivationsUserDetail
  • getOffice365ActivationCounts
  • getOffice365ActivationsUserCounts
  • getOffice365ActiveUserDetail
  • getOffice365ActiveUserCounts
  • getOffice365ServicesUserCounts
  • getOffice365GroupsActivityDetail
  • getOffice365GroupsActivityCounts
  • getOffice365GroupsActivityGroupCounts
  • getOffice365GroupsActivityStorage
  • getOffice365GroupsActivityFileCounts
  • getOneDriveActivityUserDetail
  • getOneDriveActivityUserCounts
  • getOneDriveActivityFileCounts
  • getOneDriveUsageAccountDetail
  • getOneDriveUsageAccountCounts
  • getOneDriveUsageFileCounts
  • getOneDriveUsageStorage
  • getSharePointActivityUserDetail
  • getSharePointActivityFileCounts
  • getSharePointActivityUserCounts
  • getSharePointActivityPages
  • getSharePointSiteUsageDetail
  • getSharePointSiteUsageFileCounts
  • getSharePointSiteUsageSiteCounts
  • getSharePointSiteUsageStorage
  • getSharePointSiteUsagePages
  • getSkypeForBusinessActivityUserDetail
  • getSkypeForBusinessActivityCounts
  • getSkypeForBusinessActivityUserCounts
  • getSkypeForBusinessDeviceUsageUserDetail
  • getSkypeForBusinessDeviceUsageDistributionUserCounts
  • getSkypeForBusinessDeviceUsageUserCounts
  • getSkypeForBusinessOrganizerActivityCounts
  • getSkypeForBusinessOrganizerActivityUserCounts
  • getSkypeForBusinessOrganizerActivityMinuteCounts
  • getSkypeForBusinessParticipantActivityCounts
  • getSkypeForBusinessParticipantActivityUserCounts
  • getSkypeForBusinessParticipantActivityMinuteCounts
  • getSkypeForBusinessPeerToPeerActivityCounts
  • getSkypeForBusinessPeerToPeerActivityUserCounts
  • getSkypeForBusinessPeerToPeerActivityMinuteCounts
  • getteamsDeviceUsageUserDetail
  • getteamsDeviceUsageUserCounts
  • getteamsDeviceUsagedistributionUserCounts
  • getteamsUserActivityUserDetail
  • getteamsUserActivityCounts
  • getteamsUserActivityUserCounts
  • getYammerActivityUserDetail
  • getYammerActivityCounts
  • getYammerActivityUserCounts
  • getYammerDeviceUsageUserDetail
  • getYammerDeviceUsageDistributionUserCounts
  • getYammerDeviceUsageUserCounts
  • getYammerGroupsActivityDetail
  • getYammerGroupsActivityGroupCounts
  • getYammerGroupsActivityCounts

Script parameters (Scrpit v2 no longer uses AzureTenantADName):

  • AzureTenantADName: Your ####.onmicrosoft.com tenant domain.
  • AzureAppClientId: Azure Application Client ID with Microsoft Graph - Read all usage reports permissions.
  • Credential: Prompts via Get-Credential to signin to your tenant.
  • Period: Reporting Period in days (‘D7’,‘D30’,‘D90’,‘D180’), default 30 days.
  • NoExcel: Optional switch to prevent Excel export.
  • File: XLS file name defaults to ‘c:\temp\O365Reports.xlsx’
  • APIVersion: API version, defaults to beta.

Examples:

$Results = .\Get-O365UsageReports.ps1
write-output "$($Results.keys)"
$results['teamsdeviceusageuser'] | ft

Above will prompt for Azure Tenant AD Domain Name, the Azure Application Client ID and credentials, before connecting to Microsoft Graph to pull the Office 365 Usage Reports for the last 30 days and saving to an Excel document in c:\temp\O365Reports.xlsx. The script will also return the Usage Reports as a hashtable in $Results. The write-output will list all the reports in the hastable and the last command will display a single report in a table.

I recommend voting up the User Voice: Allowing PowerShell ClientID to consume Office 365 Usage Reports. This would remove the requirement of building an Azure Application with this Reports.Read.All permission when trying to consume the Usage Reports via PowerShell.

Script Download: Get-O365UsageReports.ps1 https://gallery.technet.microsoft.com/Get-O365UsageReports-954fb5a3

Hugo-Octopress Theme | Powered by Hugo