Informes de grupos de Office 365 y visualización de Power BI

Publiqué por primera vez en la publicación de mi blog aquí. Esta es una mejora de mi secuencia de comandos de informes de mi serie de blogs de Power BI. Lea estas dos publicaciones de blog para comprender mejor este script y artículo: Integración de PowerShell con PowerBI (Parte 1) Integración de PowerShell con PowerBI: Módulo, script e informes (Parte 2) El script informa sobre una gran variedad de datos diferentes. Por ejemplo, puede comprobar si un grupo está habilitado para equipos. Realiza un seguimiento del número de grupos, equipos, propietarios y miembros a lo largo del tiempo para que pueda ver el historial de estos números. Después de recopilar los datos, crea un conjunto de datos en Power BI Online. Ahora puede abrir el cliente de Power BI Desktop e importar mi plantilla. Después de esto, importe el conjunto de datos y publíquelo en Power BI Online. La plantilla se puede descargar aquí. Guía paso a paso para importar la plantilla y publicar el informe:

1. Abra Power BI Desktop e inicie sesión con la misma cuenta que contiene el conjunto de datos creado.
2. Expediente > Importar > Plantilla de Power BI.
   
3. prensa Editar.
   
4. Seleccione el conjunto de datos que creó y presione Carga.
5. Ahora tiene un informe de Power BI listo. prensa Publicar.
6. Guardar cambios.
7. El informe ahora se publica en Power BI Online. Pres Abierto…. para mirarlo.

Aquí puede ver el informe publicado de mi demostración. La demostración caducará en unos 70 días. El guión se muestra a continuación. He copiado algunas partes del guión de Tony Redmond y las he incrustado en mi publicación. ¡Gracias Tony!

#Office 365 Groups Reporting script.
#This script generates a report on Office 365 Groups, and publishes it to a Power BI Online dataset.
#Import the Power BI template to Power BI Desktop to have a already setup visual report.
#Thanks to Tony Redmond, as i have copied parts of his script: https://gallery.technet.microsoft.com/Check-for-obsolete-Office-c0020a42
#
#Blog: alexholmeset.blog
#Twitter: twitter.com/alexholmeset

#Connects to PowerBI
Connect-PowerBI -AuthorityName demotenant.onmicrosoft.com -ClientId 'xxxxxxxxx-xxxxxx-xxxxxxx-xxxxxx-xxxxxx'  -UserName 'admin@M365x96x23223508.onmicrosoft.com' -Password '*********'

#Remember to connect to Exchange Online and SharePoint Online.

$GroupsInfo = @()
$NumberofGroups = @()
$TeamsCount = 0
$NumberofOwners = @()
$NumberOfMembers = @()

$WarningDate = (Get-Date).AddDays(-90)
$WarningEmailDate = (Get-Date).AddDays(-365)
$Today = (Get-Date)
$Date = $Today.ToShortDateString()

$Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged,SharePointSiteUrl
$GroupsCount = $Groups.count
foreach($Group in $Groups) {

    Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
    Start-Sleep -Seconds 2

    #Write-Host "Checking Group:" $Group.DisplayName
    $ObsoleteReportLine = $Group.DisplayName
    $SPOStatus = "Normal"
    $SPOActivity = "Document library in use"
    $NumberWarnings = 0
    $NumberofChats = 0
    $TeamChatData = $Null
    $LastItemAddedtoTeams = "No chats"
    $MailboxStatus = $Null
   
 # Fetch information about activity in the Inbox folder of the group mailbox  
    $Data = (Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestITems -FolderScope Inbox)
    $LastConversation = $Data.NewestItemReceivedDate
    $NumberConversations = $Data.ItemsInFolder
    $MailboxStatus = "Normal"
   
     If ($Data.NewestItemReceivedDate -le $WarningEmailDate)
       {
       #Write-Host "Last conversation item created in" $Group.DisplayName "was" $Data.NewestItemReceivedDate "-> Could be Obsolete?"
       $ObsoleteEmailGroups = $ObsoleteEMailGroups + 1
       $ObsoleteReportLine = $ObsoleteReportLine + " Last conversation dated: " + $Data.NewestItemReceivedDate + "."
       $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 items found."
            $MailboxStatus = "Low number of conversations found"
            $NumberWarnings++
          }
       Else
          {
          # Write-Host $Group.DisplayName "has" $Data.ItemsInFolder "size of conversation items: " $Data.FolderSize
       }
    }
 
 # Loop to check SharePoint document library
     If ($Group.SharePointDocumentsUrl -ne $Null)
        {
        $SPOSite = (Get-SPOSite -Identity $Group.SharePointDocumentsUrl.replace("/Shared Documents", ""))
        $AuditCheck = $Group.SharePointDocumentsUrl + "/*"
        $AuditRecs = 0
        $AuditRecs = (Search-UnifiedAuditLog -RecordType SharePointFileOperation -StartDate $WarningDate -EndDate $Today -ObjectId $AuditCheck -SessionCommand ReturnNextPreviewPage)
     If ($AuditRecs -eq $null) 
            {
            #Write-Host "No audit records found for" $SPOSite.Title "-> It is potentially obsolete!"
            $ObsoleteSPOGroups++   
            $ObsoleteReportLine = $ObsoleteReportLine + " No SPO activity detected in the last 90 days."         
            }
        Else 
            {
            #Write-Host $AuditRecs.Count "audit records found for " $SPOSite.Title "the last is dated" $AuditRecs.CreationDate[0]
            }}
        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" $Group.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 $Group.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 ($Group.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 ($TeamsEnabled -eq $True) 
     {   
     $TeamChatData = (Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory)
     If ($TeamChatData.ItemsInFolder[1] -ne 0) {
     $LastItemAddedtoTeams = $TeamChatData.NewestItemReceivedDate[1]
     $NumberofChats = $TeamChatData.ItemsInFolder[1]
     }
 }





    $Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members #| Select-Object -ExpandProperty "Name"
    $Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners #| Select-Object -ExpandProperty "Name" 
    $MembersCount = $Members.count
    $OwnerCount = $Group.ManagedBy
    $SharePointUrl = $Group.SharePointSiteUrl
    $SharePointSite = Get-SPOSite -Identity $SharePointUrl
    $SharePointLastModified = $SharePointSite.LastContentModifiedDate | Get-Date -Format yyyy.MM.dd
    $TeamsEnabledCheck = Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory | Select-Object FolderType
 
    If ($TeamsEnabledCheck.FolderType -like 'TeamChat') {
 
            $TeamsEnabled = "True"
            $TeamsCount++
 
    }
        else {
            $TeamsEnabled = "False"
    }

    


    foreach($Owner in $Owners){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Owner.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner="Owner"
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            TeamsEnabled = $TeamsEnabled
            LastChat = [string]$LastItemAddedtoTeams
            NumberChats         = [string]$NumberofChats
            LastConversation    = [string]$LastConversation
            NumberConversationsInbox = $NumberConversations
            SPOActivity         = [string]$SPOActivity
            SPOStatus           = $SPOStatus
            NumberWarnings      = $NumberWarnings
            Status              = $Status
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = [string]$Group.WhenChanged
            SharePointLastModified = [string]$SharePointLastModified
            Description = $Group.Notes
            }#EndPSCustomObject
        $GroupsInfo+=$object
    }


    foreach($Member in $Members){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Member.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner="Member"
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            TeamsEnabled = $TeamsEnabled
            LastChat = [string]$LastItemAddedtoTeams
            NumberChats         = [string]$NumberofChats
            LastConversation    = [string]$LastConversation
            NumberConversationsInbox = $NumberConversations
            SPOActivity         = [string]$SPOActivity
            SPOStatus           = $SPOStatus
            NumberWarnings      = $NumberWarnings
            Status              = $Status
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = [string]$Group.WhenChanged
            SharePointLastModified = [string]$SharePointLastModified
            Description = $Group.Notes
            }#EndPSCustomObject
        $GroupsInfo+=$object
    }

    
$object2=[PSCustomObject]@{

    Name = $Group.DisplayName
    NumberOfOwners = $OwnerCount.count
    Date = Get-Date -format dd.MM.yyyy
}#EndPSCustomObject
$NumberofOwners+=$object2


$object3=[PSCustomObject]@{

    Name = $Group.DisplayName
    NumberOfMembers = $MembersCount
    Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$NumberofMembers+=$object3

    $GroupsCount--

}

$TeamsCountArray = @()
$object4=[PSCustomObject]@{

    NumberOfTeams = $TeamsCount
    Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$TeamsCountArray+=$object4

$object5=[PSCustomObject]@{
    
    NumberOfGroups = $Groups.Count
    Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$NumberofGroups+=$object5



#Connects to PowerBI
Connect-PowerBI -AuthorityName M365x963508.onmicrosoft.com -ClientId '1bdaca9e-4ca2-4dbb-aaed-dda6620bb5b2'  -UserName 'admin@M365x963508.onmicrosoft.com' -Password 'aholmez@2511'
###
 
#Groups reporting data.
#Defines collums in the table you are going to create.
#Comment out after first time setup.

$col1 = New-PowerBIColumn -ColumnName Name -ColumnType String
$col2 = New-PowerBIColumn -ColumnName Group -ColumnType String
$col3 = New-PowerBIColumn -ColumnName Email -ColumnType String
$col4 = New-PowerBIColumn -ColumnName UserName -ColumnType String
$col5 = New-PowerBIColumn -ColumnName NumberOfMembers -ColumnType Int64
$col6 = New-PowerBIColumn -ColumnName MemberOrOwner -ColumnType String
$col7 = New-PowerBIColumn -ColumnName NumberOfOwners -ColumnType Int64
$col8 = New-PowerBIColumn -ColumnName GroupType -ColumnType String
$col9 = New-PowerBIColumn -ColumnName TeamsEnabled -ColumnType String
$col10 = New-PowerBIColumn -ColumnName LastChat -ColumnType String
$col11 = New-PowerBIColumn -ColumnName NumberChats -ColumnType Int64
$col12 = New-PowerBIColumn -ColumnName LastConversation -ColumnType String
$col13 = New-PowerBIColumn -ColumnName NumberConversationsInbox -ColumnType Int64
$col14 = New-PowerBIColumn -ColumnName SPOActivity -ColumnType String
$col15 = New-PowerBIColumn -ColumnName SPOStatus -ColumnType String
$col16 = New-PowerBIColumn -ColumnName NumberWarnings -ColumnType Int64
$col17 = New-PowerBIColumn -ColumnName Status -ColumnType String
$col18 = New-PowerBIColumn -ColumnName ExternalMemberCount -ColumnType Int64
$col19 = New-PowerBIColumn -ColumnName WhenChanged -ColumnType String
$col20 = New-PowerBIColumn -ColumnName SharePointLastModified -ColumnType String
$col21 = New-PowerBIColumn -ColumnName Description -ColumnType String
$col22 = New-PowerBIColumn -ColumnName NumberOfGroups -ColumnType Int64
$col23 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
$col24 = New-PowerBIColumn -ColumnName NumberOfTeams -ColumnType Int64
$col25 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
$col26 = New-PowerBIColumn -ColumnName Name -ColumnType String
$col27 = New-PowerBIColumn -ColumnName NumberOfOwners -ColumnType Int64
$col28 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
$col29 = New-PowerBIColumn -ColumnName Name -ColumnType String
$col30 = New-PowerBIColumn -ColumnName NumberOfMembers -ColumnType Int64
$col31 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime


#Creates table from defined collums.
#Comment out after first time setup.
$GroupsTable = New-PowerBITable -TableName GroupsTable -Columns $col1,$col2,$col3,$col4,$col5,$col6,$col7,$col8,$col9,$col10,$col11,$col12,$col13,$col14,$col15,$col16,$col17,$col18,$col19,$col20,$col21
$NumberofGroupsHistoryTable = New-PowerBITable -TableName NumberofGroupsHistoryTable -Columns $col22,$col23
$TeamsCountHistoryTable = New-PowerBITable -TableName TeamsCountHistoryTable -Columns $col24,$col25
$NumberofOwnersHistoryTable = New-PowerBITable -TableName NumberofOwnersHistoryTable -Columns $col26,$col27,$col28
$NumberofMembersHistoryTable = New-PowerBITable -TableName NumberofMembersHistoryTable -Columns $col29,$col30,$col31


#Creates dataset from defined table.
#Comment out after first time setup.
$GroupsDataset = New-PowerBIDataSet -DataSetName GroupsDataSet -Tables $GroupsTable,$TeamsCountHistoryTable,$NumberofOwnersHistoryTable,$NumberofMembersHistoryTable,$NumberofGroupsHistoryTable


#Adds dataset and get datasetid.
#Comment out after first time setup.
$GroupsDatasetID = Add-PowerBIDataSet -DataSet $GroupsDataset


#Take note of datasetid so you have it for when you are updating the dataset.
#$GroupsDatasetID = 'asdfasdfdasfa' 
$GroupsDatasetID




 
#Remove commeting after firsttime setup on line below.
#Remove-PowerBIRows -DataSetId $GroupsDatasetID -TableName GroupsTable
 
Add-PowerBIRows -DataSetId $GroupsDatasetID -TableName GroupsTable -Rows $GroupsInfo
Add-PowerBIRows -DataSetId $GroupsDatasetID -TableName NumberofGroupsHistoryTable -Rows $NumberofGroups
Add-PowerBIRows -DataSetId $GroupsDatasetID -TableName TeamsCountHistoryTable -Rows $TeamsCountArray
Add-PowerBIRows -DataSetId $GroupsDatasetID -TableName NumberofOwnersHistoryTable -Rows $NumberofOwners
Add-PowerBIRows -DataSetId $GroupsDatasetID -TableName NumberofMembersHistoryTable -Rows $NumberofMembers