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

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 7 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!

  2. Matt

    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

    1. Leon Armston

      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.


  3. Fernando

    but if the Team was created based in existing group not work

    1. Leon Armston

      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!

  4. Arun

    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 ?

Leave a Reply