Teams Private Channels with Azure Automation Report

teamsprivate-channels-report-runbook.ps1

<# ----------------------------------------------------------------------------

Produces a report in Export Excel and Saves to SharePoint

Created:      Paul Bullock
Date:         18/01/2021
Disclaimer:   
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

This script uses the following samples as a reference:

    Connect to the SharePoint Online using Application Permissions -
        https://github.com/pnp/powershell/tree/dev/samples/Connect.AzureAutomation

With thanks to for the great modules:

    Doug Finke for Import Excel Module - https://github.com/dfinke/ImportExcel
    Erwin Van Hunen for PnP PowerShell - https://github.com/pnp/powershell
---------------------------------------------------------------------------- #>
[CmdletBinding()]
Param
(
    [string]$DefaultSite = "SS-Demo"
)

# Retrieves from the Azure Automation variables and certificate stores 
# the details for connecting to SharePoint Online
$azureAutomateCreds = Get-AutomationPSCredential -Name 'AzureAppCertPassword'
$appId = Get-AutomationVariable -Name "AppClientId"
$appAdTenant = Get-AutomationVariable -Name "AppAdTenant"
$app365Tenant = Get-AutomationVariable -Name "App365Tenant"
$appCert = Get-AutomationCertificate -Name "AzureAppCertificate"

# Addresses for the tenant
$adminUrl = "https://$app365Tenant-admin.sharepoint.com"
$baseSite = "https://$app365Tenant.sharepoint.com/sites/$DefaultSite"

try {
    Write-Verbose "Running Script..."

    #-------------------------------------------------
    # Connections to SharePoint
    #-------------------------------------------------
    
    # Export the certificate and convert into base 64 string
    $base64Cert = [System.Convert]::ToBase64String($appCert.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::Pkcs12, $azureAutomateCreds.Password))

    # Connect to the standard SharePoint Site
    $siteConn = Connect-PnPOnline -ClientId $appId -CertificateBase64Encoded $base64Cert `
        -CertificatePassword $azureAutomateCreds.Password `
        -Url $baseSite -Tenant $appAdTenant -ReturnConnection
    
    # Connect to the SharePoint Online Admin Service
    $adminSiteConn = Connect-PnPOnline -ClientId $appId -CertificateBase64Encoded $base64Cert `
        -CertificatePassword $azureAutomateCreds.Password `
        -Url $adminUrl -Tenant $appAdTenant -ReturnConnection

    # SharePointy Adminy Stuff here
    Write-Verbose "Connected to SharePoint Online Admin Centre"
    
    #-------------------------------------------------
    # Gather Reporting Data
    #-------------------------------------------------

    # Gets all Team Private Channels based on the template
    $teamPrivateChannels = Get-PnPTenantSite -Template "TEAMCHANNEL#0" -Connection $adminSiteConn
    
    #-------------------------------------------------
    # Produce and Save Reporting Data
    #-------------------------------------------------
    $now = [System.DateTime]::Now.ToString("yyyy-mm-dd_hh-MM-ss")
    $reportFileName = "teams-private-channels-$($now).xlsx"

    $ExcelReportSettings = @{
        Path = $reportFileName
        Title = "Teams Private Channel Report"
        WorksheetName = "Teams Private Channels"
        AutoFilter = $true 
        AutoSize = $true
    }

    Write-Verbose "Creating Excel File $reportFileName"
    $teamPrivateChannels | Select-Object Title,Url,StorageUsage,Owner,SiteDefinedSharingCapability `
        | Export-Excel @ExcelReportSettings

    # Save to SharePoint
    $file = Add-PnPFile -Path $reportFileName -Folder "Shared Documents" -Connection $siteConn
    Write-Verbose "Uploaded Excel File $reportFileName to SharePoint"
}
catch {
    # Script error
    Write-Error "An error occurred: $($PSItem.ToString())"
}