Instantly share code, notes, and snippets.
Created
December 14, 2022 08:26
-
Save AlexanderHolmeset/f6c7f0d13b8e2fb8400b22f31faa9219 to your computer and use it in GitHub Desktop.
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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment