Office 365 Groups reporting and Power BI visualization

[AlexHolmeset]

I first published on my blog post here.  This is an improvement of my reporting script from my Power BI blog series. Read these two blog posts to better understand this script and article: Integrating PowerShell with PowerBI (Part 1) Integrating PowerShell with PowerBI: Module, script and reporting (Part 2) The script reports on a large variety of different data. It can for example check if a group is Teams enabled. It keeps track of number of groups, teams, owners and members over time so you can see the history on these numbers. After gathering the data, it creates a dataset on Power BI Online. Now you can open the Power BI Desktop client and import my template. After this you import the dataset, and publish it to Power BI Online. The template can be downloaded here. Step by step guide on importing the template and publishing the report:

1. Open Power BI Desktop and log in with the same account that holds the created dataset.
2. File > Import > Power BI template.
   
3. Press Edit.
   
4. Select the dataset you created and press Load.
5. Now you have a ready Power BI report. Press Publish.
6. Save changes.
7. Report is now published to Power BI Online. Pres Open.... to look at it.

Here you can see the published report from my demo. The demo will expire in about 70 days. The script is shown below. I have copied some parts of Tony Redmond's script and embedded into my post. Thanks to 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