· 3 min read

Azure Automation to the Rescue – Session at Scottish Summit 2021

Hopefully, you have seen my session at the Scottish summit for 'Azure Automation to the rescue, PnP PowerShell your way to the fun stuff', whist quite a lengthy title, talks about how you can leverage Azure cloud services to offload

Hopefully, you have seen my session at the Scottish summit for 'Azure Automation to the rescue, PnP PowerShell your way to the fun stuff', whist quite a lengthy title, talks about how you can leverage Azure cloud services to offload

Hopefully, you have seen my session at the Scottish summit for “Azure Automation to the rescue, PnP PowerShell your way to the fun stuff”, whist quite a lengthy title, talks about how you can leverage Azure cloud services to offload tasks especially those that are repetitive or commonly requested – that can run on a schedule or ad-hoc.

{{< youtube 8qsnhfSilic >}}

I have mentioned a few resources and scripts within the session, that you can refer to below, if you would like to setup similar services.

Source Script for Reporting on Teams Private Channels

Adding and installing the Modules for local installation

This sample shows importing the Excel module into Azure Automation. Note: this is dependent on the Az PowerShell module (v5.5.0) to be installed on your machine.

New-AzAutomationModule `
    -AutomationAccountName "sample-testing-automation-modern" `
    -Name "ImportExcel" `
    -ContentLink "https://psg-prod-eastus.azureedge.net/packages/importexcel.7.1.1.nupkg" `
    -ResourceGroupName "sample-testing-rg"

The Azure Runbook for finding Teams Private Channels in a tenant and generating an Excel report

This is the script for the Teams Private Channels report runbook used in the session. This works if the services are setup with the sample from the PnP repository.

[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())"
}

The following resources were mentioned in my session, I recommend these fir further reading:

Thank you for reading more about my session, I hope you enjoyed it, if you have any feedback, please post below.

Enjoy!

Share:
Back to Blog