Script per generar un informe dels grups obsolets d’Office 365
Aquest script de Powershell, creat per Tony Redmond per a Office 365 for IT Pros, genera un informe per avaluar els grups d’Office 365 que poden estar obsolets i en desús, en funció de la seva activitat recent. Aquí presentem una versió parcialment traduïda al català, i amb algun canvi respecte a l’original, com el de generar els arxius de l’informe amb la data al seu nom, per si volem conservar diferents versions. Abans d’executar-lo, hem d’estar connectats a Exchange Online, Sharepoint Online i Teams.
# TeamsGroupsActivityReport.PS1
# A script to check the activity of Microsoft 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
# V4.0 11-Jan-2020
# V4.1 15-Jan-2020 Better handling of the Team Chat folder
# V4.2 30-Apr-2020 Replaced $G.Alias with $G.ExternalDirectoryObjectId. Fixed problem with getting last conversation from Groups where no conversations are present.
# V4.3 13-May-2020 Fixed bug and removed the need to load the Teams PowerShell module
# V4.4 14-May-2020 Added check to exit script if no Microsoft 365 Groups are found
# V4.5 15-May-2020 Some people reported that Get-Recipient is unreliable when fetching Groups, so added code to revert to Get-UnifiedGroup if nothing is returned by Get-Recipient
# V4.6 8-Sept-2020 Better handling of groups where the SharePoint team site hasn't been created
# V4.7 13-Oct-2020 Teams compliance records are now in a different location in group mailboxes
# V4.8 16-Dec-2020 Some updates after review of code to create 5.0 (Graph based version)
#
# https://github.com/12Knocksinna/Office365itpros/blob/master/TeamsGroupsActivityReport.ps1
#
CLS
# Comprovar que estem connectats a Exchange Online, SharePoint Online, i Teams
Write-Host "Comprovant que els moduls de PowerShell necessaris estan carregats..."
$ModulesLoaded = Get-Module | Select Name
If (!($ModulesLoaded -match "ExchangeOnlineManagement")) {Write-Host "Connecteu-vos a Exchange Online i reinicieu aquest script"; break}
If (!($ModulesLoaded -match "Microsoft.Online.SharePoint.PowerShell")) {Write-Host "Connecteu-vos a SharePoint Online i reinicieu aquest script"; break}
$OrgName = (Get-OrganizationConfig).Name
# OK, sembla que estem connectats a Exchange Online i SharePoint Online...
Write-Host "Comprovant els grups i els equips de Microsoft 365 al tenant:" $OrgName
# Configurem algunes coses que farem servir
$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 = [System.Collections.Generic.List[Object]]::new(); $ReportFile = "C:\temp\GroupsActivityReport_$((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).html"
$CSVFile = "C:\temp\GroupsActivityReport_$((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).csv"
$htmlhead="<html>
<style>
BODY{font-family: Arial; font-size: 8pt;}
H1{font-size: 22px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H2{font-size: 18px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H3{font-size: 16px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
TABLE{border: 1px solid black; border-collapse: collapse; font-size: 8pt;}
TH{border: 1px solid #969595; background: #dddddd; padding: 5px; color: #000000;}
TD{border: 1px solid #969595; padding: 5px; }
td.pass{background: #B7EB83;}
td.warn{background: #FFF275;}
td.fail{background: #FF2626; color: #ffffff;}
td.info{background: #85D4FF;}
</style>
<body>
<div align=center>
<p><h1>Informe d'activitat dels grups i els equips de Microsoft 365</h1></p>
<p><h3>Generat el: " + $date + "</h3></p></div>"
# Obtenir el llistat de grups en el tenant.
Write-Host "Obtenint el llistat dels grups de Microsoft 365..."
[Int]$GroupsCount = 0; [int]$TeamsCount = 0; $TeamsList = @{}; $UsedGroups = $False
$Groups = Get-Recipient -RecipientTypeDetails GroupMailbox -ResultSize Unlimited | Sort-Object DisplayName
$GroupsCount = $Groups.Count
# Si no trobem cap grup amb Get-Recipient, provem de fer-ho amb Get-UnifiedGroup.
If ($GroupsCount -eq 0) { #
Write-Host "Buscant el grups amb Get-UnifiedGroup"
$Groups = Get-UnifiedGroup -ResultSize Unlimited | Sort-Object DisplayName
$GroupsCount = $Groups.Count; $UsedGroups = $True
If ($GroupsCount -eq 0) {
Write-Host "No s'han trobat grups de Microsoft 365; sortint de l'script" ; break}
}
Write-Host "Completant la llista d'equips..."
If ($UsedGroups -eq $False) { # Completem la llista de hash dels equips amb una crida a Get-UnifiedGroup
Get-UnifiedGroup -Filter {ResourceProvisioningOptions -eq "Team"} -ResultSize Unlimited | ForEach { $TeamsList.Add($_.ExternalDirectoryObjectId, $_.DisplayName) } }
Else { # Encara tenim les dades a la variable $Groups, traiem el grup d'aquesta dada
$Groups | ? {$_.ResourceProvisioningOptions -eq "Team"} | ForEach { $TeamsList.Add($_.ExternalDirectoryObjectId, $_.DisplayName) } }
$TeamsCount = $TeamsList.Count
CLS
# Configurem la barra de progrés
$ProgDelta = 100/($GroupsCount); $CheckCount = 0; $GroupNumber = 0
# Bucle principal
ForEach ($Group in $Groups) { #Obtenim les propietats del grup
$G = Get-UnifiedGroup -Identity $Group.DistinguishedName
$GroupNumber++
$GroupStatus = $G.DisplayName + " ["+ $GroupNumber +"/" + $GroupsCount + "]"
Write-Progress -Activity "Comprovant el grup" -Status $GroupStatus -PercentComplete $CheckCount
$CheckCount += $ProgDelta; $ObsoleteReportLine = $G.DisplayName; $SPOStatus = "Normal"
$SPOActivity = "Biblioteca de documents activa"; $SPOStorage = 0
$NumberWarnings = 0; $NumberofChats = 0; $TeamChatData = $Null; $TeamsEnabled = $False; $LastItemAddedtoTeams = "N/A"; $MailboxStatus = $Null; $ObsoleteReportLine = $Null
# Comprovar els propietaris del grup
$ManagedBy = $G.ManagedBy
If ([string]::IsNullOrWhiteSpace($ManagedBy) -and [string]::IsNullOrEmpty($ManagedBy)) {
$ManagedBy = "Sense propietaris"
Write-Host $G.DisplayName "No te propietaris!" -ForegroundColor Red}
Else {
$ManagedBy = (Get-ExoMailbox -Identity $G.ManagedBy[0]).DisplayName}
# Edat del grup
$GroupAge = (New-TimeSpan -Start $G.WhenCreated -End $Today).Days
# Busquem informació d'activitat a la bústia del grup
$Data = (Get-ExoMailboxFolderStatistics -Identity $G.ExternalDirectoryObjectId -IncludeOldestAndNewestITems -FolderScope Inbox)
If ([string]::IsNullOrEmpty($Data.NewestItemReceivedDate)) {$LastConversation = "No s'han trobat elements"}
Else {$LastConversation = Get-Date ($Data.NewestItemReceivedDate) -Format g }
$NumberConversations = $Data.ItemsInFolder
$MailboxStatus = "Normal"
If ($Data.NewestItemReceivedDate -le $WarningEmailDate) {
Write-Host "Darrera conversa creada: " $G.DisplayName "en data" $Data.NewestItemReceivedDate "-> Obsolet?"
$ObsoleteReportLine = $ObsoleteReportLine + " Data de la darrera conversa a l'Outlook: " + $LastConversation + "."
$MailboxStatus = "La safata d'entrada no s'ha fet servir recentment"
$ObsoleteEmailGroups++
$NumberWarnings++ }
Else
{# Si hi ha menys de 20 converses, ho indiquem
If ($Data.ItemsInFolder -lt 20) {
$ObsoleteReportLine = $ObsoleteReportLine + " Nomes s'han trobat " + $Data.ItemsInFolder + " elements de conversa d'Outlook."
$MailboxStatus = "Numero de converses baix"
$NumberWarnings++}
}
# Comprovem l'activitat al Sharepoint
If ($G.SharePointSiteURL -ne $Null) {
$SPOStorage = (Get-SPOSite -Identity $G.SharePointSiteUrl).StorageUsageCurrent
$SPOStorage = [Math]::Round($SpoStorage/1024,2) # Emmagatzematge de SharePoint en GB
$AuditCheck = $G.SharePointDocumentsUrl + "/*"
$AuditRecs = $Null
$AuditRecs = (Search-UnifiedAuditLog -RecordType SharePointFileOperation -StartDate $WarningDate -EndDate $Today -ObjectId $AuditCheck -ResultSize 1)
If ($AuditRecs -eq $Null) {
$ObsoleteSPOGroups++
$ObsoleteReportLine = $ObsoleteReportLine + "No s'ha detectat activitat al Sharepoint els darrers 90 dies." }
}
Else
{
# La biblioteca de Sharepoint està en blanc
$ObsoleteSPOGroups++
$AuditRecs = $Null
$ObsoleteReportLine = $ObsoleteReportLine + "No s'ha creat la llibreria de Sharepoint."
}
# Report to the screen what we found - but only if something was found...
If ($ObsoleteReportLine -ne $G.DisplayName)
{
Write-Host $ObsoleteReportLine
}
# Generar els criteris d'obsolescència dels grups...
If ($AuditRecs -eq $Null) {
$SPOActivity = "No s'ha detectat activitat al Sharepoint els darrers 90 dies."
$NumberWarnings++ }
If ($G.SharePointDocumentsUrl -eq $Null) {
$SPOStatus = "No s'ha creat la llibreria de Sharepoint."
$NumberWarnings++ }
$Status = "Actiu"
If ($NumberWarnings -eq 1)
{
$Status = "No gaire actiu"
}
If ($NumberWarnings -gt 1)
{
$Status = "Inactiu"
}
# Si el grup té l'equip habilitat, trobar la data de la darrera conversa a l'equip
If ($TeamsList.ContainsKey($G.ExternalDirectoryObjectId) -eq $True) {
$TeamsEnabled = $True
[datetime]$DateOldTeams = "1-Jun-2021" # Després d’aquesta data, Microsoft hauria d’haver mogut les dades antigues de Teams a la nova ubicació
$CountOldTeamsData = $False
# Start by looking in the new location (TeamsMessagesData in Non-IPMRoot)
$TeamsChatData = (Get-ExoMailboxFolderStatistics -Identity $G.ExternalDirectoryObjectId -IncludeOldestAndNewestItems -FolderScope NonIPMRoot | ? {$_.FolderType -eq "TeamsMessagesData" })
If ($TeamsChatData.ItemsInFolder -gt 0) {$LastItemAddedtoTeams = Get-Date ($TeamsChatData.NewestItemReceivedDate) -Format g}
$NumberOfChats = $TeamsChatData.ItemsInFolder
# If the script is running before 1-Jun-2021, we need to check the old location of the Teams compliance records
If ($Today -lt $DateOldTeams) {
$CountOldTeamsData = $True
$OldTeamsChatData = (Get-ExoMailboxFolderStatistics -Identity $G.ExternalDirectoryObjectId -IncludeOldestAndNewestItems -FolderScope ConversationHistory)
ForEach ($T in $OldTeamsChatData) { # We might have one or two subfolders in Conversation History; find the one for Teams
If ($T.FolderType -eq "TeamChat") {
If ($T.ItemsInFolder -gt 0) {$OldLastItemAddedtoTeams = Get-Date ($T.NewestItemReceivedDate) -Format g}
$OldNumberofChats = $T.ItemsInFolder
}}}
If ($CountOldTeamsData -eq $True) {
$NumberOfChats = $NumberOfChats + $OldNumberOfChats
If (!$LastItemAddedToTeams) { $LastItemAddedToTeams = $OldLastItemAddedToTeams }
}
If (($TeamsEnabled -eq $True) -and ($NumberOfChats -le 100)) { Write-Host "El grup" $G.DisplayName "te nomes" $NumberOfChats "registres de compliment" }
}
# Generem la informació que presentarem a l'informe
$ReportLine = [PSCustomObject][Ordered]@{
Nom = $G.DisplayName
Propietari = $ManagedBy
Membres = $G.GroupMemberCount
Convidats = $G.GroupExternalMemberCount
Descripcio = $G.Notes
Activitat_Correu = $MailboxStatus
Team_Habilitat = $TeamsEnabled
Darrer_Xat = $LastItemAddedtoTeams
Numero_Xats = $NumberofChats
Darrera_Conversa = $LastConversation
Numero_Converses = $NumberConversations
Activitat_Sharepoint = $SPOActivity
Emmagatzematge_SPO_GB = $SPOStorage
Estat_SPO = $SPOStatus
Data_Creacio = Get-Date ($G.WhenCreated) -Format g
Edat_en_Dies = $GroupAge
Alertes = $NumberWarnings
Estat = $Status}
$Report.Add($ReportLine)
#Fi del bucle principal
}
If ($TeamsCount -gt 0) {
$PercentTeams = ($TeamsCount/$GroupsCount)
$PercentTeams = ($PercentTeams).tostring("P") }
Else {
$PercentTeams = "No s'han trobat equips" }
# Creem l'informe en HTML
$htmlbody = $Report | ConvertTo-Html -Fragment
$htmltail = "<p>Informe creat per a: " + $OrgName +
"</p>" +
"<p>Numero de grups analitzats: " + $GroupsCount + "</p>" +
"<p>Numero de grups potencialment obsolets (segons l'activitat al Sharepoint): " + $ObsoleteSPOGroups + "</p>" +
"<p>Numero de grups potencialment obsolets (segons l'activitat en converses): " + $ObsoleteEmailGroups + "<p>"+
"<p>Numero de grups amb l'equip activat : " + $TeamsCount + "</p>" +
"<p>Percentatge: de grups amb l'equip activat" + $PercentTeams + "</body></html>" +
"<p>-----------------------------------------------------------------------------------------------------------------------------"
$htmlreport = $htmlhead + $htmlbody + $htmltail
$htmlreport | Out-File $ReportFile -Encoding UTF8
$Report | Export-CSV -NoTypeInformation $CSVFile
$Report | Out-GridView
# Resum
CLS
Write-Host " "
Write-Host "Resultats"
Write-Host "-------"
Write-Host "Numero de grups analitzats :" $GroupsCount
Write-Host "Numero de grups potencialment obsolets (segons l'activitat al Sharepoint):" $ObsoleteSPOGroups
Write-Host "Número de grups potencialment obsolets (segons l'activitat en converses) :" $ObsoleteEmailGroups
Write-Host "Numero de grups amb l'equip activat :" $TeamsList.Count
Write-Host "Percentatge: de grups amb l'equip activat :" $PercentTeams
Write-Host " "
Write-Host "Podeu trobar l'informe en format html a " $ReportFile "i en CSV a" $CSVFile
# An example script used to illustrate a concept. More information about the topic can be found in the Office 365 for IT Pros eBook https://gum.co/O365IT/
# and/or a relevant article on https://office365itpros.com or https://www.petri.com. See our post about the Office 365 for IT Pros repository # https://office365itpros.com/office-365-github-repository/ for information about the scripts we write.
# Do not use our scripts in production until you are satisfied that the code meets the need of your organization. Never run any code downloaded from the Internet without
# first validating the code in a non-production environment.
