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 tasks especially those that are repetitive or commonly requested – that can run on a schedule or ad-hoc.

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.

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

Links to mentioned or related resources

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!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: