Want an easy way to see Teams and SharePoint usage data in the same report? Then you have come to the right place.
This reports on all your teams with SharePoint data, but not SharePoint sites without a team.
This will be a halfway automatic process, as you need to manually download two reports from the Teams and SharePoint admin portals. It is possible to get most of the SharePoint data from the Graph API, but that’s not the case for Teams at this point in time.
Go to the SharePoint admin portal, and find the Active Sites menu. Here you click the export button to get a CSV file with SharePoint activity data for the last 180 days.
Next, you go to the Teams admin portal, where you look for the Usage Report menu. Here you want to run the “Teams usage” report. Select 180 days as the date range. After clicking “Run report”, click the “Export to Excel” button on the right to generate a CSV file. You then find the CSV under Downloads.
So what you need to do before running the script is having an Azure App with Group.Read.All, User.Read.All and Directory.Read.All rights for the Graph request we use to find the teams SharePoint URL, Private channels and some other Teams data. The URL is the thing we use to tie the information from the two CSV files together.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #Teams and SharePoint usage data consolidation script | |
| #Author: Alexander Holmeset | |
| #Twitter: @AlexHolmeset | |
| function Get-MSGraphAppToken{ | |
| <# .SYNOPSIS | |
| Get an app based authentication token required for interacting with Microsoft Graph API | |
| .PARAMETER TenantID | |
| A tenant ID should be provided. | |
| .PARAMETER ClientID | |
| Application ID for an Azure AD application. Uses by default the Microsoft Intune PowerShell application ID. | |
| .PARAMETER ClientSecret | |
| Web application client secret. | |
| .EXAMPLE | |
| # Manually specify username and password to acquire an authentication token: | |
| Get-MSGraphAppToken -TenantID $TenantID -ClientID $ClientID -ClientSecert = $ClientSecret | |
| .NOTES | |
| Author: Jan Ketil Skanke | |
| Contact: @JankeSkanke | |
| Created: 2020-15-03 | |
| Updated: 2020-15-03 | |
| Version history: | |
| 1.0.0 - (2020-03-15) Function created | |
| #> | |
| [CmdletBinding()] | |
| param ( | |
| [parameter(Mandatory = $true, HelpMessage = "Your Azure AD Directory ID should be provided")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$TenantID, | |
| [parameter(Mandatory = $true, HelpMessage = "Application ID for an Azure AD application")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$ClientID, | |
| [parameter(Mandatory = $true, HelpMessage = "Azure AD Application Client Secret.")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$ClientSecret | |
| ) | |
| Process { | |
| $ErrorActionPreference = "Stop" | |
| # Construct URI | |
| $uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" | |
| # Construct Body | |
| $body = @{ | |
| client_id = $clientId | |
| scope = "https://graph.microsoft.com/.default" | |
| 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 | |
| If(!$MyToken){ | |
| 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 | |
| try{ | |
| RefreshToken | |
| $apiCall = Invoke-WebRequest -Method "GET" -Uri $Uri -ContentType "application/json" -Headers $Header -ErrorAction Stop -UseBasicParsing | |
| } | |
| catch{ | |
| 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 "https://graph.microsoft.com/beta/groups/?`$filter=resourceProvisioningOptions/Any(x:x eq 'Team')" | |
| #Import the SharePoint Active Sites report from the SharePoint Admin Portal. | |
| $sites = Import-Csv "c:\temp\Sites_20221214081815059.csv" | |
| #Import the Teams Usage report from the Teams Admin Portal. | |
| $teamsactivity = Import-Csv "c:\temp\TeamsTeamActivityDetail12_14_2022 8_17_55 AM.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 "https://graph.microsoft.com/v1.0/groups/$($team.ID)/sites/root" -Headers $Header -ContentType "application/json").weburl | |
| $SPO_Site = $sites | Where-Object {$_.url -eq $SP_URL} | |
| $teamactivity = $teamsactivity | Where-Object{$_.'team id' -eq $team.Id} | |
| $url2 = "https://graph.microsoft.com/beta/teams/$($team.ID)/channels?`$filter=membershipType eq 'private'" | |
| $privatechannels = (Invoke-RestMethod -Headers $Header -Uri $url2 -Method GET -ContentType 'application/json').value | |
| $url3 = "https://graph.microsoft.com/beta/groups/$($team.ID)/owners" | |
| $tempOwners = ((Invoke-RestMethod -Headers $Header -Uri $url3 -Method GET -ContentType 'application/json').value).DisplayName | |
| $tempOwnersCount = $tempOwners.count | |
| $tempOwners = $tempOwners -join ' ,' | |
| $url4 = "https://graph.microsoft.com/beta/groups/$($team.ID)/members" | |
| $tempMembers = ((Invoke-RestMethod -Headers $Header -Uri $url4 -Method GET -ContentType 'application/json').value).DisplayName | |
| $tempMemberCount = $tempMembers.count | |
| $tempMembers = $tempMembers -join ' ,' | |
| $Object=[PSCustomObject]@{ | |
| DisplayName = $team.Displayname | |
| Description = $team.Description | |
| mailNickname = $team.mailNickname | |
| GroupID = $team.ID | |
| TeamType = $teamactivity.'Team type' | |
| OwnersCount = $tempOwnersCount | |
| MembersCount = $tempMemberCount | |
| ActiveUsers = $teamactivity.'Active Users' | |
| ActiveChannels = $teamactivity.'Active Channels' | |
| PrivateChannels = $privatechannels.count | |
| Guests = $teamactivity.Guests | |
| ReplyMessages = $teamactivity.'Reply Messages' | |
| PostMessages = $teamactivity.'Post Messages' | |
| MeetingsOrganized = $teamactivity.'Meetings Organized' | |
| UrgentMessages = $teamactivity.'Urgent Messages' | |
| Reactions = $teamactivity.Reactions | |
| Mentions = $teamactivity.Mentions | |
| ChannelMessages = $teamactivity.'Channe lMessages' | |
| 'LastActivity (UTC Time)' = $teamactivity.'Last Activity Date' | |
| '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' | |
| 'SP URL' = $SP_URL | |
| TeamOwners = $tempOwners | |
| TeamMembers = $tempMembers | |
| }#EndPSCustomObject | |
| $Report+=$object | |
| } | |
| $Report | Export-csv "c:\temp\report.csv" |
A blog about automation and technologies in the cloud 