A reader asked how easy it might be to find out what SharePoint Online sites are connected to Office 365 Groups and Teams and how the link works. Well, the easy answer is to say that you can get a list of group-enabled sites by running the Get-SPOSite cmdlet as follows:
# Fetch a list of SharePoint Online sites created using the Office 365 Groups template
Get-SPOSite -Template "GROUP#0" -IncludePersonalSite:$False -Limit All
However, the output is a simple list that’s not very interesting. The list also includes sites belonging to deleted groups that are being retained by Office 365 because of a retention policy. Things get a lot more interesting when you use the group identifier for a site to look at some group information. To have SharePoint return the group identifier, you must pass the Detailed parameter to Get-SPOSite. This slows down processing, but it means that you get a group identifier. The group identifier (a GUID) can be used with the Get-AzureADGroup, Get-UnifiedGroup, and Get-Team cmdlets (alas, all in separate PowerShell modules) to fetch different information about the group. You can also use the identifier with other cmdlets to retrieve group member information, and so on.
Scripting a Report of Group-Enabled Sites
Equipped with the knowledge of how to get to group information, we can build a script to generate a report. Here’s a rough and ready script to do the job.
# Report Group-enabled SharePoint sites
# Find all sets created with the Office 365 Group template and reports them in a CSV file. Checks if group exists for the site
# and if it is enabled for Microsoft Teams
Cls
Write-Host "Analyzing SharePoint sites created with the Office 365 Groups template..."
$Sites = Get-SPOSite -Template "GROUP#0" -IncludePersonalSite:$False -Limit All
$Report = [System.Collections.Generic.List[Object]]::new()
$ErrorSites = 0
$i = 0
Foreach ($Site in $Sites) {
$i++
$ProgressBar = "Processing site " + $Site.Title + " (" + $i + " of " + $Sites.Count + ")"
Write-Progress -Activity "Checking SharePoint Sites" -Status $ProgressBar -PercentComplete ($i/$Sites.Count*100)
$GroupId = (Get-SpoSite $Site.Url -Detailed).GroupId.Guid
# Check if the Office 365 Group exists
$ErrorText = $Null
$O365Group = $Null
$O365Group = (Get-UnifiedGroup -Identity $GroupId -ErrorAction SilentlyContinue)
If ($O365Group -eq $Null) {
$O365GroupName = "Unknown Office 365 Group"
$ErrorText = "Failed to find Office 365 Group for site " + $Site.Title + " identifier " + $GroupId
$O365GroupMembers = 0
$ErrorSites++ }
Else {
$O365GroupName = $O365Group.DisplayName
$O365GroupMembers = $O365Group.GroupMemberCount
# Check if site has a team
$TeamsEnabled = $True
Try {Get-Team -GroupId $GroupId | Out-Null}
Catch {$TeamsEnabled = $False} }
# Generate a line
$ReportLine = [PSCustomObject]@{
Site = $Site.Title
URL = $Site.URL
LastContent = $Site.LastContentModifiedDate
O365Group = $O365GroupName
Members = $O365GroupMembers
TeamEnabled = $TeamsEnabled
Error = $ErrorText }
$Report.Add($ReportLine)
}
Write-Host "All done" $ErrorSites "sites reported errors"
$Report | Sort Site | Export-CSV c:\temp\Sites.csv -NoTypeInformation
As with all my scripts, there’s lots that could be done to improve the robustness of the code, improve error handling, and so on. Creating a production-ready script is not the point. What I want to illustrate is how to exploit the connection between SharePoint Online and other PowerShell modules.
Reviewing the SharePoint Online Sites Report
The report is output as a CSV file. You can add extra columns to the file by adding properties to the table generated by the script, which I’ve limited to site title, URL, the last date for content, the name of the Office 365 group, count of group members, a True/False flag to show if the group is enabled for Teams, and some error text. The date for last content modified is unreliable as content can be modified by background activities, so it’s included as an example of using information returned by Get-SPOSite. Figure 1 shows an example of the CSV file.
Figure 1: Reviewing group-connected SharePoint Online sites in Excel
The script highlights sites that have a group identifier that no longer points to a valid Office 365 group. These situations are likely to be caused by groups that have been removed but the site remains, probably because an Office 365 retention policy governs the site. You can try to remove the site in the SharePoint Online Admin Center: if you see an error, you know that a retention policy is blocking the deletion.
I look forward to hearing how people amend or expand the script to meet different circumstances. Please post a comment if you do.
For more information about working with SharePoint Online, Groups, and Teams through SharePoint, see the hundreds of code examples included in the Office 365 for IT Pros eBook. We’re bound to have something that will make you think!
So if I run the script in this article, I never get the GroupId attribute
.
A workaround is to search the matching on the SharePoint URL (not way to use -Filter so Where-Object is used…) :
$GroupId = (Get-UnifiedGroup | Where-Object {$_.SharePointSiteUrl -eq $Site.url}).ExternalDirectoryObjectId
Absolutely. A call is made to the Get-UnifiedGroup cmdlet to fetch group properties. The WhenCreated property stores the creation date of the group, so just add that to the set of properties output in the report.
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
Hi Tony,
It seems the “Detailed” parameter has been deprecated (according to https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/get-sposite?view=sharepoint-ps)
So if I run the script in this article, I never get the GroupId attribute
.
A workaround is to search the matching on the SharePoint URL (not way to use -Filter so Where-Object is used…) :
$GroupId = (Get-UnifiedGroup | Where-Object {$_.SharePointSiteUrl -eq $Site.url}).ExternalDirectoryObjectId
What version of the SharePoint Online PowerShell module are you using? This script works with version 16.0.9021.1201. That’s all I can guarantee!
Hello Tony,
is it possible to get in the reporting file the date creation of the Teams?
Absolutely. A call is made to the Get-UnifiedGroup cmdlet to fetch group properties. The WhenCreated property stores the creation date of the group, so just add that to the set of properties output in the report.