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.
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.
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | <# 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 & number of previous days to report on. #> $outputpath = "c:\temp\leonarmston_Teams1.csv" $SPOAdminSite = "https://contoso-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 "PnP.PowerShell" -ErrorAction SilentlyContinue) { $PnPModule = Get-InstalledModule -Name "PnP.PowerShell" -ErrorAction SilentlyContinue Write-Host "Module: $($PnPModule.Name) installed and it is version $($PnPModule.Version)" -ForegroundColor Green } else { Write-Host "PnP PowerShell Module (PnP.PowerShell) Not Installed" -ForegroundColor Red Write-Host "Run PowerShell ISE as an Administrator and install the PowerShell module PnP.PowerShell Online by running the command:" Write-Host "Install-Module PnP.PowerShell" -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 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.
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!
I’m getting this error, any ideas?
At line:1 char:111
+ … isplayName, Channels, “Created By”, “Team members”, Owners, Guests, `
+ ~
Missing expression after ‘,’ in pipeline element.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpression
Hi Matt
Just thought I’d give this a 2022 update and see if it still runs ok. Have made a few changes and I was able to run the script successfully and get data about the recently created Teams.
Are you running the script line by line or all at once? Try running all at once.
Thanks
but if the Team was created based in existing group not work
Hi Fernando
Correct – it will only display when the SharePoint site was created. So Sites that have been teamified later wont show the date it was Teamified!
Hi Leon – To get total number of Team created within last few mins (say 10 mins) instead of days what is required in this script? And also will the script give output instantly instead of giving after lengthy duration for last few mins statistics ?