Using PowerShell to report on your Microsoft Teams data

Do you know how many Microsoft Teams you have or which Teams have guests or private channels? Or what Teams are ownerless and unused? If not have you thought of using PowerShell to report on your Microsoft Teams data to enable you to analyse the data in the product of your choice?

The Microsoft Teams Admin centre https://admin.teams.microsoft.com/ provides some top-level information however there are some downsides to this such as:

  1. There is limited filtering and sorting
  2. It may not show the information you need
  3. You may not be able to download the data

These limitations are acceptable if you only have a few Teams and can easily scroll through the list. However in reality most organizations have hundreds or thousands of Teams and as a result need a solution that aloows them to analyse their data.

Read on to find out about the limitations in the reporting in the Teams Admin centre and using PowerShell to report on your Microsoft Teams data therefore enabling you to analyse the data in the product of your choice.

Reporting in the Teams Admin centre

From the Teams Admin centre there are two key two locations where you can view information about your Teams:

  • Teams > Manage Teams
  • Teams analytics and report

Manage Teams and users in a Team

The Manage Teams report shows a useful summary of all Active and Archived Teams. The limitations are that you can only filter the report on the Team name and cannot sort any of the columns. In addition you cannot export this report.

This is useful when you need to investigate a specific Team as you can search for it by name.

Furthermore you can drill down into a specific Team to see the users, channels and settings for the Team. Again this is useful when you investigate a specific Team as you can quickly see the details of the Team.

In conclusion the Manage Teams report is only useful if you need to investigate a specific team because you can search by a Team name.

Analytics & Reports > Usage Reports

The Teams analytics and report provides reports on insights and information about Teams usage over the last 7, 30 or 90 days. Importantly these reports can be filtered and indeed exported as Excel files.

However it is important to remember that these reports only show Teams that are active so consequently if a Team has not been active during the reporting period it will not be shown on the report.

In conclusion there reports are good to quickly view which Teams are active however for governance purposes we need to investigate inactive teams which would not be shown on the report.

Using PowerShell to export the data

Using PowerShell or Graph API allows you to report on your Microsoft Teams data and extract the data to allow you to analyse the data in the solution of your choice.

For this post I am using PowerShell in my examples and using the Get-Teams (Teams PowerShell) and Get-UnifiedGroup (Exchange PowerShell) cmdlets. The reason I use Get-UnifiedGroup is because it provide more detailed attributes on the Microsoft 365 group.

PowerShell Report 1: All Microsoft Teams

This first report is using PowerShell to report on all Microsoft Teams in the tenant and outputs the results into a CSV file.

This report uses the Get-UnifiedGroup cmdlet from Exchange PowerShell because it provides more details than the Get-Teams cmdlet. It is important to note that this is a Micrososft 365 Group cmdlet so will return details on all groups and not just Teams therefore the report must be filtered to only return Groups that were created by Teams -Filter {ResourceProvisioningOptions -eq "Team"}. Note remove this filter if you want all report on all Micrososft 365 Groups and not just Teams.

The PowerShell report generates CSV file that contains the following attributes for each Team. Full details of what can be output are detailed in the Microsoft documents Set-UnifiedGroup (ExchangePowerShell) | Microsoft Docs

  • Alias = Team display name
  • DisplayName
  • Access type = Private or Public
  • IsMembershipDynamic = True if a dynamic group or False is users are manually assigned memberhsip
  • SensitivityLabel = Group sensitivity label shown as the GUID
  • AllowAddGuest = Can guest be added to Group
  • GroupMemberCount = No of members in Team
  • GroupExternalMemberCount = No of external guests in Team
  • WhenCreated = Date and time group created
  • ExpirationTime = The Date and Time the Group expires. If not enabled this will be blank
  • Guid = Group unique ID

All Microsoft Teams PowerShell script

#Create credential object </code> </p>
$credentials = Get-Credential</code></p>

#Connect to Exchange Online PowerShell</code></p>
Connect-ExchangeOnline -Credential $credentials</p>

#Set the filename and location REMEMBER TO UPDATE PATH</p>
$exportLocation = "C:\temp\Powershell"</p>
$Path="$exportLocation\All_Teams_Report $((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).csv"</p>

#Set variable to hold properties of Group that you want to export</p>
$Properties = 'DisplayName', 'ManagedBy', 'AccessType', 'IsMembershipDynamic', 'SensitivityLabel', 'AllowAddGuests', 'GroupMemberCount', 'GroupExternalMemberCount', 'WhenCreated', 'ExpirationTime', 'Guid'</p>

#Generate a list of Groups that were generated from Teams and export as CSV</p>
$Teams = (Get-UnifiedGroup -Filter {ResourceProvisioningOptions -eq "Team"} -ResultSize Unlimited | Sort DisplayName | Select $Properties | Export-CSV -Path $Path -NoTypeInformation)</p>
Write-Host&nbsp; "Report exported to $($Path)"</p>

#Disconnect from PowerShell</p>
Disconnect-ExchangeOnline -Confirm:$false</p>

Report 2: Microsoft Teams that do not have owners

This second report is using PowerShell to report on all Microsoft Teams that do not have an owner and outputs the results into a CSV file.

An ownerless Team occurs when a user leaves the organisational and their account is removed and the user was the only owner of a Team. Ownerless teams are a governance problem as the remaining users in Team cannot to change the membership of the team (add or remove members) as they are not owners. This results in tickets to the service desk to resolve.

To report on Teams that do not have owners I have adapted the first script to only include Teams that do not have an owners by using a where statement Where-Object {-Not $_.ManagedBy}in the Get-UnifiedGroup to the script.

Update line of code

#Generate a list of Groups generated from Teams and have no owners and export as CSV
$Teams = (Get-UnifiedGroup -Filter {ResourceProvisioningOptions -eq "Team"} -ResultSize Unlimited | Where-Object {-Not $_.ManagedBy} | Sort DisplayName | Select $Properties | Export-CSV -Path $Path -NoTypeInformation) 
Write-Host  "Report exported to $($Path)" 

Report 3: List all members in each Team

This third report is using PowerShell to report on all users and their role in each Microsoft Team and outputs the results into a CSV file.

The PowerShell script using the Get-Team and Get-TeamUser cmdlets from Teams Powershell . Note: as this report is using Teams Powershell it will excludes other types of Microsoft 365 Groups, therefore no filtering is required.

The PowerShell script will loop through each Team getting the users in each team and then output into a CSV file.  The output contains:

  • Team name
  • Group ID
  • User
  • Email
  • Role (owner, member or guest)

All members in All Teams PowerShell script

#Set the filename and location
$exportLocation = "C:\temp\Powershell"

$Path="$exportLocation\All_Users_in Teams_Report $((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).csv"
   
#Connect to Teams
#$UserCredential = Get-Credential
#Connect-MicrosoftTeams -Credential $UserCredential

# Create variable of all Groups IDs
$AllTeamsInOrg = (Get-Team).GroupID
$TeamReport = @()

# Loop through each team and create list users and roles
ForEach($Team in $AllTeamsInOrg) {
   
    $team = Get-Team -GroupId $Team
    #Get all Users in Team
        $users = (Get-TeamUser -GroupId $team.groupID)

       #get details for each user
        ForEach($user in $users) {
     
            # Create an object to hold all values
            $teamReportObject = New-Object PSObject -Property @{
                TeamName = $team.DisplayName
                GroupID = $team.GroupID
                User = $user.Name
                Email = $user.User
                Role =  $user.Role
            }

            # Add to the report
            $TeamReport += $teamReportObject
        }
    }
#export csv file 
$TeamReport | select 'TeamName', 'GroupID', 'User', 'Email', 'Role' | Export-CSV $Path -NoTypeInformation
Write-Host  "Report exported to $($Path)"  
#Disconnect from PowerShell
Disconnect-ExchangeOnline -Confirm:$false

Teams Usage

For this last report you can download the Teams usage report from The Team Admin Centre > Analytics & reports >Usage reports. This will export the Teams that are active within the time specified include the type and volume of activity undertaken.

Alternatively you can use a PowerShell script. Office 365 IT Pros have created a great script that details the activity levels of Microsoft 365 Groups. Details can be found here https://office365itpros.com/2020/01/14/office-365-groups-teams-activity-report/ .

Summary

Finally once you have the reports on All Teams, All users in Teams and the Teams usage I recommend using PowerBi (or the analytical product of your choice) to answer questions such as ‘which ownerless Teams are inactive’ as well as providing graphical views of the data.

Keep Reading

PreviousNext