TeamsSharePointUsageReport.ps1

#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__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 "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{$_.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
            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'
            'SP URL' = $SP_URL
            TeamOwners = $tempOwners
            TeamMembers = $tempMembers


            }#EndPSCustomObject
        $Report+=$object
    



}


$Report | Export-csv c:\temp\report.csv