Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
#Teams and SharePoint usage data consolidation script
#Author: Alexander Holmeset
#Twitter: @AlexHolmeset
function Get-MSGraphAppToken{
Get an app based authentication token required for interacting with Microsoft Graph API
A tenant ID should be provided.
Application ID for an Azure AD application. Uses by default the Microsoft Intune PowerShell application ID.
.PARAMETER ClientSecret
Web application client secret.
# Manually specify username and password to acquire an authentication token:
Get-MSGraphAppToken -TenantID $TenantID -ClientID $ClientID -ClientSecert = $ClientSecret
Author: Jan Ketil Skanke
Contact: @JankeSkanke
Created: 2020-15-03
Updated: 2020-15-03
Version history:
1.0.0 - (2020-03-15) Function created
param (
[parameter(Mandatory = $true, HelpMessage = "Your Azure AD Directory ID should be provided")]
[parameter(Mandatory = $true, HelpMessage = "Application ID for an Azure AD application")]
[parameter(Mandatory = $true, HelpMessage = "Azure AD Application Client Secret.")]
Process {
$ErrorActionPreference = "Stop"
# Construct URI
$uri = "$tenantId/oauth2/v2.0/token"
# Construct Body
$body = @{
client_id = $clientId
scope = ""
client_secret = $clientSecret
grant_type = "client_credentials"
try {
$MyTokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing
$MyToken =($MyTokenRequest.Content | ConvertFrom-Json).access_token
Write-Warning "Failed to get Graph API access token!"
Exit 1
$MyHeader = @{"Authorization" = "Bearer $MyToken" }
catch [System.Exception] {
Write-Warning "Failed to get Access Token, Error message: $($_.Exception.Message)"; break
return $MyHeader
#You need an Azure App with Group.Read.All rights.
# Application (client) ID, tenant ID and secret
$tenantId = 'xxxxxxxxx'
$ClientID = 'xxxxxxxxx'
$ClientSecret = "xxxxxxxxx"
$Header = Get-MSGraphAppToken -TenantID $tenantId -ClientID $ClientID -ClientSecret $ClientSecret
function Get-GraphRequest($uri){
Process {
$ErrorActionPreference = "Stop"
try {
#Graph API request that loops through every '@odata.nextLink' if there are more than 1000 devices.
$content = while (-not [string]::IsNullOrEmpty($Uri)) {
# API Call
Write-Host "`r`nQuerying $Uri..." -ForegroundColor Yellow
$apiCall = Invoke-WebRequest -Method "GET" -Uri $Uri -ContentType "application/json" -Headers $Header -ErrorAction Stop -UseBasicParsing
Start-Sleep -Seconds 30
$apiCall = Invoke-WebRequest -Method "GET" -Uri $Uri -ContentType "application/json" -Headers $Header -ErrorAction Stop -UseBasicParsing
$nextLink = $null
$Uri = $null
if ($apiCall.Content) {
# Check if any data is left
$nextLink = $apiCall.Content | ConvertFrom-Json | Select-Object '@odata.nextLink'
$Uri = $nextLink.'@odata.nextLink'
$apiCall.Content | ConvertFrom-Json
catch [System.Exception] {
Write-Warning "Failed to complete request, Error message: $($_.Exception.Message)"; break
return $content.value
#Get all teams in your tenant.
$Teams = Get-GraphRequest "`$filter=resourceProvisioningOptions/Any(x:x eq 'Team')"
#Import the SharePoint Active Sites report from the SharePoint Admin Portal.
$sites = Import-Csv C:\temp\Sites__20200918081611816.csv
#Import the Teams Usage report from the Teams Admin Portal.
$teamsactivity = Import-Csv C:\temp\TeamsUsage_2020-06-19_2020-09-16.csv
$Report = @()
foreach($team in $teams){
$TempGroup = @()
#Finds the url for the Teams SharePoint site. This is used to connect the two CSV files together.
$SP_URL = (Invoke-RestMethod -Method get -Uri "$($team.ID)/sites/root" -Headers $Header -ContentType "application/json").weburl
$SPO_Site = $sites | Where-Object {$_.url -eq $SP_URL}
$teamactivity = $teamsactivity | Where-Object{$ -eq $team.Id}
$url2 = "$($team.ID)/channels?`$filter=membershipType eq 'private'"
$privatechannels = (Invoke-RestMethod -Headers $Header -Uri $url2 -Method GET -ContentType 'application/json').value
$url3 = "$($team.ID)/owners"
$tempOwners = ((Invoke-RestMethod -Headers $Header -Uri $url3 -Method GET -ContentType 'application/json').value).DisplayName
$tempOwnersCount = $tempOwners.count
$tempOwners = $tempOwners -join ' ,'
$url4 = "$($team.ID)/members"
$tempMembers = ((Invoke-RestMethod -Headers $Header -Uri $url4 -Method GET -ContentType 'application/json').value).DisplayName
$tempMemberCount = $tempMembers.count
$tempMembers = $tempMembers -join ' ,'
DisplayName = $team.Displayname
Description = $team.Description
mailNickname = $team.mailNickname
GroupID = $team.ID
OwnersCount = $tempOwnersCount
MembersCount = $tempMemberCount
ActiveUsers = $teamactivity.ActiveUsers
ActiveChannels = $teamactivity.ActiveChannels
PrivateChannels = $privatechannels.count
Guests = $teamactivity.Guests
ReplyMessages = $teamactivity.ReplyMessages
PostMessages = $teamactivity.PostMessages
MeetingsOrganized = $teamactivity.MeetingsOrganized
UrgentMessages = $teamactivity.UrgentMessages
Reactions = $teamactivity.Reactions
Mentions = $teamactivity.Mentions
ChannelMessages = $teamactivity.ChannelMessages
'LastActivity (UTC Time)' = $teamactivity.'LastActivity (UTC Time)'
'Last SP activity (UTC)' = $SPO_Site.'Last activity (UTC)'
'Storage used (GB)' = $SPO_Site.'Storage used (GB)'
'Date created' = $SPO_Site.'Date created'
'Created by' = $SPO_Site.'Created by'
'Files' = $SPO_Site.'Files'
'Files viewed or edited' = $SPO_Site.'Files viewed or edited'
'Page views' =$SPO_Site.'Page views'
'Page visits' = $SPO_Site.'Page visits'
'External sharing' = $SPO_Site.'External sharing'
TeamOwners = $tempOwners
TeamMembers = $tempMembers
$Report | Export-csv c:\temp\report.csv
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment