# A script to check the activity of Office 365 Groups and Teams and report the groups and teams that might be deleted because they're not used. # We check the group mailbox to see what the last time a conversation item was added to the Inbox folder. # Another check sees whether a low number of items exist in the mailbox, which would show that it's not being used. # We also check the group document library in SharePoint Online to see whether it exists or has been used in the last 90 days. # And we check Teams compliance items to figure out if any chatting is happening. # Created 29-July-2016 Tony Redmond # V2.0 5-Jan-2018 # V3.0 17-Dec-2018 # VCloudiway 1.0 01-Jul-2019 - (works in all language, call invoke-commands) # Original script can be found here : https://gallery.technet.microsoft.com/Check-for-obsolete-Office-c0020a42 # Check that we are connected to Exchange Online $Cred = Get-Credential -Message "Please enter Exchange Online admin email address and password" $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid -Authentication Basic -AllowRedirection -Credential $Cred Write-Host "Checking that prerequisite PowerShell modules are loaded..." Try { $OrgName = (Invoke-Command -Session $Session -ScriptBlock { Get-OrganizationConfig | Select-Object Name } -ErrorAction Stop).Name } Catch { Write-Host "Your PowerShell session is not connected to Exchange Online." Write-Host "Please connect to Exchange Online using an administrative account and retry." Break } # And check that we're connected to SharePoint Online as well Try { $SPOCheck = (Get-SPOTenant -ErrorAction SilentlyContinue ) } Catch { Write-Host "Your PowerShell session is not connected to SharePoint Online." Write-Host "Please connect to SharePoint Online using an administrative account and retry." Break } # And finally the Teams module Try { $TeamsCheck = (Get-Team) } Catch { Write-Host "Please connect to the Teams PowerShell module before proceeeding." Break } # OK, we seem to be fully connected to both Exchange Online and SharePoint Online... Write-Host "Checking for Obsolete Office 365 Groups in the tenant:" $OrgName # Setup some stuff we use $WarningDate = (Get-Date).AddDays(-90) $WarningEmailDate = (Get-Date).AddDays(-365) $Today = (Get-Date) $Date = $Today.ToShortDateString() $TeamsGroups = 0 $TeamsEnabled = $False $ObsoleteSPOGroups = 0 $ObsoleteEmailGroups = 0 $Report = @() $ReportFile = "c:\temp\GroupsActivityReport.html" $CSVFile = "c:\temp\GroupsActivityReport.csv" $htmlhead="

Office 365 Groups and Teams Activity Report

Generated: " + $date + "

" # Get a list of all Office 365 Groups in the tenant Write-Host "Extracting list of Office 365 Groups for checking..." $Groups = Invoke-Command -Session $Session -scriptblock { Get-UnifiedGroup -ResultSize Unlimited | select-object -property DisplayName,DistinguishedName,GroupMemberCount,GroupExternalMemberCount,Notes,SharePointDocumentsUrl,ExternalDirectoryObjectId,ManagedBy,Alias } | Sort-Object DisplayName # And create a hash table of Teams $TeamsList = @{} Get-Team | ForEach { $TeamsList.Add($_.GroupId, $_.DisplayName) } Write-Host "Processing" $Groups.Count "groups" # Progress bar $ProgDelta = 100/($Groups.count) $CheckCount = 0 $GroupNumber = 0 # Main loop ForEach ($G in $Groups) { $GroupNumber++ $GroupStatus = $G.DisplayName + " ["+ $GroupNumber +"/" + $Groups.Count + "]" Write-Progress -Activity "Checking group" -Status $GroupStatus -PercentComplete $CheckCount $CheckCount += $ProgDelta $ObsoleteReportLine = $G.DisplayName $SPOStatus = "Normal" $SPOLastAccessDate = $Null $SPOActivity = "Document library in use" $NumberWarnings = 0 $NumberofChats = 0 $TeamChatData = $Null $TeamsEnabled = $False $LastItemAddedtoTeams = "No chats" $MailboxStatus = $Null # Check who manages the group $ManagedBy = $G.ManagedBy If ([string]::IsNullOrWhiteSpace($ManagedBy) -and [string]::IsNullOrEmpty($ManagedBy)) { $ManagedBy = "No owners" Write-Host $G.DisplayName "has no group owners!" -ForegroundColor Red} Else { $ManagedBy = (Invoke-Command -Session $Session -ScriptBlock { Get-Mailbox -Identity $using:G.ManagedBy[0].DistinguishedName | Select-Object DisplayName }).DisplayName } # Fetch information about activity in the Inbox folder of the group mailbox $Data = (Invoke-Command -Session $Session -ScriptBlock { Get-MailboxFolderStatistics -Identity $using:G.Alias -IncludeOldestAndNewestITems -FolderScope Inbox | Select-Object NewestItemReceivedDate,ItemsInFolder}) $LastConversation = $Data.NewestItemReceivedDate $NumberConversations = $Data.ItemsInFolder $MailboxStatus = "Normal" If ($Data.NewestItemReceivedDate -le $WarningEmailDate) { Write-Host "Last conversation item created in" $G.DisplayName "was" $Data.NewestItemReceivedDate "-> Obsolete?" $ObsoleteReportLine = $ObsoleteReportLine + " Last conversation dated: " + $Data.NewestItemReceivedDate + "." $MailboxStatus = "Group Inbox Not Recently Used" $ObsoleteEmailGroups++ $NumberWarnings++ } Else {# Some conversations exist - but if there are fewer than 20, we should flag this... If ($Data.ItemsInFolder -lt 20) { $ObsoleteReportLine = $ObsoleteReportLine + " Only " + $Data.ItemsInFolder + " conversation item(s) found." $MailboxStatus = "Low number of conversations" $NumberWarnings++} } # Loop to check SharePoint document library If ($G.SharePointDocumentsUrl -ne $Null) { $pos = $G.SharePointDocumentsUrl.LastIndexOf('/') $SharePointDocumentsUrl = $G.SharePointDocumentsUrl.Substring(0, $pos) Write-Host $SharePointDocumentsUrl $SPOSite = (Get-SPOSite -Identity $SharePointDocumentsUrl) $AuditCheck = $G.SharePointDocumentsUrl + "/*" $AuditRecs = 0 $AuditRecs = (Invoke-Command -Session $Session -ScriptBlock { Search-UnifiedAuditLog -RecordType SharePointFileOperation -StartDate $using:WarningDate -EndDate $using:Today -ObjectId $using:AuditCheck -SessionCommand ReturnNextPreviewPage -ResultSize 1 }) If ($AuditRecs -eq $null) { #Write-Host "No audit records found for" $SPOSite.Title "-> Potentially obsolete!" $ObsoleteSPOGroups++ $ObsoleteReportLine = $ObsoleteReportLine + " No SPO activity detected in the last 90 days." } Else { $SPOLastAccessDate = $AuditRecs.CreationDate } } Else { # The SharePoint document library URL is blank, so the document library was never created for this group #Write-Host "SharePoint has never been used for the group" $G.DisplayName $ObsoleteSPOGroups++ $ObsoleteReportLine = $ObsoleteReportLine + " SPO document library never created." } # Report to the screen what we found - but only if something was found... If ($ObsoleteReportLine -ne $G.DisplayName) { Write-Host $ObsoleteReportLine } # Generate the number of warnings to decide how obsolete the group might be... If ($AuditRecs -eq $Null) { $SPOActivity = "No SPO activity detected in the last 90 days" $NumberWarnings++ } If ($G.SharePointDocumentsUrl -eq $Null) { $SPOStatus = "Document library never created" $NumberWarnings++ } $Status = "Pass" If ($NumberWarnings -eq 1) { $Status = "Warning" } If ($NumberWarnings -gt 1) { $Status = "Fail" } # If Team-Enabled, we can find the date of the last chat compliance record If ($TeamsList.ContainsKey($G.ExternalDirectoryObjectId) -eq $True) { $TeamsEnabled = $True $TeamChatData = (Invoke-Command -Session $Session -ScriptBlock { Get-MailboxFolderStatistics -Identity $using:G.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory | Select-Object NewestItemReceivedDate,ItemsInFolder }) If ($TeamChatData.ItemsInFolder[1] -ne 0) { $LastItemAddedtoTeams = $TeamChatData.NewestItemReceivedDate[1] $NumberofChats = $TeamChatData.ItemsInFolder[1] If ($TeamChatData.NewestItemReceivedDate -le $WarningEmailDate) { Write-Host "Team-enabled group" $G.DisplayName "has only" $TeamChatData.ItemsInFolder[1] "compliance record(s)" } } } # Generate a line for this group for our report $ReportLine = [PSCustomObject][Ordered]@{ GroupName = $G.DisplayName ManagedBy = $ManagedBy Members = $G.GroupMemberCount ExternalGuests = $G.GroupExternalMemberCount Description = $G.Notes MailboxStatus = $MailboxStatus TeamEnabled = $TeamsEnabled LastChatAccess = $LastItemAddedtoTeams MessagesInChannels = $NumberofChats LastMailboxAccess = $LastConversation MessagesInMailbox = $NumberConversations LastAccessFile = $SPOLastAccessDate SPOActivity = $SPOActivity SPOStatus = $SPOStatus NumberWarnings = $NumberWarnings Status = $Status} # And store the line in the report object $Report += $ReportLine #End of main loop } # Create the HTML report $PercentTeams = ($TeamsList.Count/$Groups.Count) $htmlbody = $Report | ConvertTo-Html -Fragment $htmltail = "

Report created for: " + $OrgName + "

Number of groups scanned: " + $Groups.Count + "

" + "

Number of potentially obsolete groups (based on document library activity): " + $ObsoleteSPOGroups + "

" + "

Number of potentially obsolete groups (based on conversation activity): " + $ObsoleteEmailGroups + "

"+ "

Number of Teams-enabled groups : " + $TeamsList.Count + "

" + "

Percentage of Teams-enabled groups: " + ($PercentTeams).tostring("P") + "" $htmlreport = $htmlhead + $htmlbody + $htmltail $htmlreport | Out-File $ReportFile -Encoding UTF8 # Summary note Write-Host $ObsoleteSPOGroups "obsolete group document libraries and" $ObsoleteEmailGroups "obsolete email groups found out of" $Groups.Count "checked" Write-Host "Summary report available in" $ReportFile "and CSV file saved in" $CSVFile $Report | Export-CSV -NoTypeInformation $CSVFile