Report on all Microsoft Teams created in the past X number of days

Report on all Microsoft Teams created in the past X number of days

I created a script which generates a report of Microsoft Teams including the SharePoint site in an Office 365 tenancy created in a specified number of days with no need for Exchange Admin permissions or to use Microsoft Graph. This was recently requested on a Yammer community I belong to.

I then set to expand a script on my previous blog post Script reporting on all Microsoft Teams including connected SharePoint sites in a Microsoft 365 tenancy – without using Microsoft Graph.

This script requires the Microsoft Teams PowerShell Module and the SharePoint Online PnP PowerShell Module. It then requires the user account for the script to run under to have the Office 365 admin roles SharePoint Administrator and Teams Service Administrator.

The tricky part was that the PowerShell command Get-Team currently does not return a Created Date. So I needed to get creative.

Get-Team PowerShell output showing no Created date stamp

You could use the Exchange online PowerShell command Get-UnifiedGroup which does have a WhenCreated property but it is very common that Teams/SharePoint admins do not have Exchange Admin permissions.

Get-UnifiedGroup showing a WhenCreated stamp is available.

It would also be possible for you to use a combination of Microsoft Graph methods. This however brings a level of complexity and elevated permissions required that the Teams/SharePoint admin usually does not have.

The key part of my script is I query the SharePoint sites list (DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS) on the SharePoint admin site https://tenantname-admin.sharepoint.com. This displays all of the Sites on the Admin Centre. I can then identify the related SharePoint site to the Team by matching the GroupID found by running Get-Team. The related SharePoint site then has a property TimeCreated we can use as the Team creation date for filtering.

So we can use this in the script below which:

  • Gets all the Teams
  • Gets all the SP SItes by querying the SharePoint sites list in the SP Admin site for sites created in the last seven days
  • Loops through the all the Teams individually
  • Checks if the Team has a SharePoint site that exists in the SharePoint sites list items we retrieved earlier (filtered for TimeCreated in the last seven days)
    • If correct then details of the Team, SP Site will be added to a Hashtable
  • At the end of the script the HashTable is output to a CSV
<#
Script Generates report of MS Teams in an Office 365 tenancy created in the past specified number of days
Requires an account with the minimum Office 365 roles SharePoint Administrator and Teams Service Administrator
Enter output CSV Path, SP Admin Site &amp; number of previous days to report on.
#>
$outputpath = "c:\temp\leonarmston_Teams1.csv"
$SPOAdminSite = "https://leonarmston-admin.sharepoint.com"

$offsetDays = 7 #Enter the number of previous days to report on

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 -Query `
    "<View><Query><Where><Geq><FieldRef Name='TimeCreated' /><Value Type='DateTime'><Today OffsetDays='-$offsetDays' /></Value></Geq></Where></Query></View>" -ErrorAction Stop
}
Catch
{
    Write-Host "Error Message: $($_.exception.message) - TERMINATING SCRIPT" -ForegroundColor Red
    Return
}


$Hashtable = @()

foreach($team in $teams)
{
    $connectedSPSite = $sites.FieldValues | Where-Object{$_.GroupId -eq $team.GroupId}
    if($connectedSPSite.Count)
    {

        $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;
        'Created By'            = $connectedSPSite.CreatedBy;
        '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;
        'Time Created'          = $connectedSPSite.TimeCreated;
        'Storage Used'          = $connectedSPSite.StorageUsed;
        'Num Of Files'          = $connectedSPSite.NumOfFiles;
        }
    }

}


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

So there we have it a script to report on all Microsoft Teams including the SharePoint site created in the past seven days which then outputs to a csv.

As per normal let me know if you have any questions, comments or suggestions/requirements for further reporting and I’ll try and help out?

This Post Has 2 Comments

  1. Tim Gill

    This is golden! Thank you so much for creating this. Because of my organization’s needs, I slightly modified to include a list of usernames for Owners, Members, and Guests instead of just the count.

    1. Leon Armston

      Hi Tim

      Glad to hear it helped you out – great you extended it to show usernames!
      Yes it’s been a really useful script and could be extended for example to write on a schedule to a SharePoint list and then have email notifications when a new time is added.
      All the best!

Leave a Reply