Script reporting on all Microsoft Teams including connected SharePoint sites in a Microsoft 365 tenancy – without using Microsoft Graph

Script reporting on all Microsoft Teams including connected SharePoint sites in a Microsoft 365 tenancy – without using Microsoft Graph

The script below will give you a report of all the Microsoft Teams and details about the connected SharePoint sites in a Microsoft 365 Tenancy. It just requires the Microsoft Teams PowerShell Module and the SharePoint Online PnP PowerShell Module. It requires a user account to have the miminum Office 365 admin roles Teams Service Administrator and SharePoint Administrator.

It writes to a CSV the following properties: DisplayName, Channels, “Team members”, Owners, Guests, Privacy, Status, Description, Classification, “Group ID”, MailNickName, “Connected SP Site”, “Storage Used”, “Num Of Files”

Often the roadblock for getting all this information together on report is that it usually requires using Microsoft Graph and this can be complicated with generating an access token and the various Microsoft Graph permission levels that need to be assigned by a Tenant admin. My script gets around this by accessing a special SharePoint system list called DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS on your SharePoint Online Admin Site https://tenant-admin.sharepoint.com. Here many properties of the Teams connected SharePoint site i.e. Url, Num of Files, Storage Used and many more properties are exposed and could be potentially be added to the script to be reported on.

Some of the Information in the Report is available in the Teams Admin Centre https://admin.teams.microsoft.com/teams/manage but this does not show information about the connected SharePoint site.

Please let me know if you have any further comments, need any assistance or there are any missing properties that you would like me to try and add to the report?

#Script Generates report of MS Teams & connected SharePoint sites in an Office 365 tenancy.
#Requires an account with the mimimum Office 365 admin roles SharePoint Administrator and Teams Service Administrator
#Enter output CSV Path & your SP Admin Site below
$outputpath = "c:\temp\leonarmston_Teams.csv"
$SPOAdminSite = "https://leonarmston-admin.sharepoint.com"

if(Get-InstalledModule -Name "MicrosoftTeams" -ErrorAction SilentlyContinue)
{
    $TeamsModule = Get-InstalledModule -Name "MicrosoftTeams" -ErrorAction SilentlyContinue
    Write-Host "Module: $($TeamsModule.Name) installed and it is version $($TeamsModule.Version)" -ForegroundColor Green
}
else
{
    Write-Host "Microsoft Teams PowerShell Module (MicrosoftTeams) Not Installed" -ForegroundColor Red
    Write-Host "Run PowerShell as an Administrator and install the PowerShell module MicrosoftTeams Online by running the command:"
    Write-Host "Install-Module MicrosoftTeams" -ForegroundColor Yellow
    Return
}

if(Get-InstalledModule -Name "SharePointPnPPowerShellOnline" -ErrorAction SilentlyContinue)
{
    $PnPModule = Get-InstalledModule -Name "SharePointPnPPowerShellOnline" -ErrorAction SilentlyContinue
    Write-Host "Module: $($PnPModule.Name) installed and it is version $($PnPModule.Version)" -ForegroundColor Green
}
else
{
    Write-Host "PnP PowerShell Module (SharePointPnPPowerShellOnline) Not Installed" -ForegroundColor Red
    Write-Host "Run PowerShell ISE as an Administrator and install the PowerShell module SharePointPnPPowerShell Online by running the command:"
    Write-Host "Install-Module SharePointPnPPowerShellOnline" -ForegroundColor Yellow
    Return
}


if ($null -eq $cred)
{
    $cred = Get-Credential -Message "Enter an account with Teams Administrative credentials"
}

try
{
    Connect-MicrosoftTeams -Credential $cred -ErrorAction Stop
    Connect-PnPOnline -Url $SPOAdminSite -Credentials $cred -ErrorAction Stop
    $teams = Get-Team -ErrorAction Stop
    $list = Get-PnPList -Identity "/Lists/DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECO" -ErrorAction Stop
    $sites = Get-PnPListItem -List $list -ErrorAction Stop
}
Catch
{
    Write-Host "Error Message: $($_.exception.message) - TERMINATING SCRIPT" -ForegroundColor Red
    Return
}


$Hashtable = @()

foreach($team in $teams)
{
    $channels = Get-TeamChannel -GroupId $team.GroupId

    $users = Get-TeamUser -GroupId $team.GroupId

    $owners  = $users | Where-Object{$_.Role -eq "owner"}

    $members  = $users | Where-Object{$_.Role -eq "member"}
    
    $guestusers = $users | Where-Object{$_.Role -eq "guest"}

    if($team.Archived -eq $false)
    {
        $status = "Active"
    }

    if($team.Archived -eq $true)
    {
        $status = "Archived"
    }

    $connectedSPSite = $sites.FieldValues | Where-Object{$_.GroupId -eq $team.GroupId}

    $Hashtable += New-Object psobject -Property @{
    'DisplayName'           = $team.DisplayName;
    'Channels'              = $channels.Count;
    'Team members'          = $members.count;
    'Owners'                = $owners.count;
    'Guests'                = $guestusers.count;
    'Privacy'               = $team.Visibility;
    'Status'                = $status;
    'Description'           = $team.Description;
    'Classification'        = $team.Classification
    'Group ID'              = $team.GroupId;
    'MailNickName'          = $team.MailNickName;
    'Connected SP Site'     = $connectedSPSite.SiteUrl;
    'Storage Used'          = $connectedSPSite.StorageUsed;
    'Num Of Files'          = $connectedSPSite.NumOfFiles;
    }

}


$Hashtable | Select-Object DisplayName, Channels, "Team members", Owners, Guests, ` 
Privacy, Status, Description, Classification, "Group ID", MailNickName, "Connected SP Site", `
"Storage Used", "Num Of Files" | Export-Csv $outputpath -NoTypeInformation

Leave a Reply