Primer: Output Data Generated with an Azure Automation Runbook to a SharePoint List

Execute an Azure Automation Runbook and Store its Results in a SharePoint Online List Item

Yesterday, I explained the basics of how to use Azure Automation to run a script using Microsoft Graph PowerShell SDK cmdlets. Today, I want to extend the knowledge outlined in that article to demonstrate another important aspect: How to output information from an Azure Automation runbook.

Azure Automation runbooks execute on headless servers that you don’t control. Runbooks can create output data but need to get that information back to whoever needs it. Available Microsoft 365 methods to share information include:

Creating a file in a SharePoint Online document library.
Posting a message to a Teams chat or channel.
Sending email.
Creating items in a list in a SharePoint Online site.

This article covers how to use the last method because SharePoint lists are a good way to capture the information generated by background processes. The script used yesterday reports user accounts created in the last 30 days. We’ll extend it to find some additional information and create a list item containing the data.

The Basics – Resources and Permissions

The first version of the script uses two modules of the Microsoft Graph PowerShell SDK for authentication and to find user accounts. To interact with SharePoint sites, we must add the Microsoft.Graph.Sites module and because the script generates some information about Microsoft 365 Groups, add the Microsoft.Graph.Groups module too.

The automation account already has the User.Read.All Graph permission. To read details of groups, it needs the Group.Read.All permission. The interaction with sites is both read (to access the site and find the target list) and write (to create items in the target list), so the automation account needs the Sites.ReadWrite.All permission. We’ll add the two permissions using PowerShell as follows:

Connect-MgGraph -Scopes AppRoleAssignment.ReadWrite.All
# Add Graph permissions to the service principal for the automation account
$GraphApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'"
$TargetSP = Get-MgServicePrincipal -filter "displayname eq 'M365Automation'"

[array]$Permissions = "Group.Read.All", "Sites.ReadWrite.All"

ForEach ($Permission in $Permissions){
    $Role = $GraphApp.AppRoles | Where-Object {$_.Value -eq $Permission}

    # Create the parameters for the new assignment
    $AppRoleAssignment = @{}
    $AppRoleAssignment.Add("PrincipalId",$TargetSP.Id)
    $AppRoleAssignment.Add("ResourceId",$GraphApp.Id)
    $AppRoleAssignment.Add("AppRoleId",$Role.Id)

    $RoleAssignment = New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $TargetId -BodyParameter $AppRoleAssignment
    If ($RoleAssignment.AppRoleId) {
        Write-Host ("{0} permission granted to {1}" -f $Role.Value, $TargetSP.DisplayName)
    }
}

After these commands execute, Figure 1 shows what you should see when viewing the permissions for the automation account in the enterprise apps section of the Entra admin center.

Graph permissions needed to interact with users, groups, and sites. — Figure 1: Graph permissions needed to interact with users, groups, and sites

Prepare the Target List

Although you can create a list in a SharePoint Online site with PowerShell (here’s how), it’s easier to do this work through the SharePoint browser interface. Select a target site and create a list there. I used a minimal set of fields to capture details like the number of user accounts, the number of Microsoft 365 groups, the names of recently added user accounts, and a timestamp. Don’t get too worried about what data is output: we’re just exploring principles here instead of creating a fully-fledged solution. Remember to note the name of the fields you add to the list because they’ll need to be stated in the script (field names are case sensitive).

Amend the Runbook Code

Yesterday’s script is simple and spans just a few commands to find and list recently added user accounts. Today’s script needs more code. Let’s investigate.

First, we need to connect to the SharePoint site that holds the target list. This code takes a URL for a site and converts it to a SharePoint site identifier that can be used to find a site. We can then look for the target list and fetch its details.

$Uri = "https://office365itpros.sharepoint.com/sites/Office365Adoption"
$SiteId = $Uri.Split('//')[1].split("/")[0] + ":/sites/" + $Uri.Split('//')[1].split("/")[2]
$Site = Get-MgSite -SiteId $SiteId
If (!$Site) {
    Write-Output ("Unable to connect to site {0} with id {1}" -f $Uri, $SiteId) 
    Exit
}
$List = Get-MgSiteList -SiteId $Site.Id -Filter "displayName eq 'Tenant Statistics'"
If (!$List) {
    Write-Output ("Unable to find list 'Tenant Statistics' in site {0}" -f $Site.DisplayName)
    Exit
}

The script includes some simple code to find user accounts and Microsoft 365 groups:

[array]$UserAccounts = Get-MgUser -All -PageSize 500 -Filter "userType eq 'Member'"
[array]$M365Groups = Get-MgGroup -Filter "groupTypes/any(c:c eq 'unified')" -All -PageSize 500

Finally, the runbook has the code to create an item in the target list. This is accomplished by creating a hash table to hold details of the fields (inside a separate hash table). What seems to be an odd structure is because PowerShell is mimicking a JSON structure for a payload body submitted to the Graph request to add the item. In any case, here’s the code:

$NewItemParameters = @{
   fields = @{
     Title               = Get-Date ($Date) -format s
     Rundate             = $RunDate
     NumberM365Groups    = $M365Groups.Count
     NumberUserAccounts  = $UserAccounts.Count
     RecentUserAccounts  = $RecentUserAccounts
   }
}

$NewItem = New-MgSiteListItem -SiteId $Site.Id -ListId $List.Id -BodyParameter $NewItemParameters
If ($NewItem) {
    Write-Output ("Added item to list {0}" -f $List.DisplayName)
} Else {
    Write-Output "Failed to add item to list"

I can’t emphasize too much the importance of testing code interactively before submitting it to Azure Automation. When that happens, after running in test mode, the runbook should report that it created a new item in the list (Figure 2).

Testing that the Azure Automation runbook works and creates an item in the target SharePoint list. — Figure 2: Testing that the Azure Automation runbook works and creates an item in the target SharePoint list

To verify that the runbook succeeded, go to the SharePoint site and open the target list. The item(s) created by the runbook should be present.

Items created by the runbook in a SharePoint Online list. — Figure 3: Items created by the Azure Automation runbook in a SharePoint Online list

Running Azure Automation Runbooks Aren’t So Hard After All

I hope that by now you’ll understand that running PowerShell scripts with Azure Automation is not particularly difficult. Once a runbook can output data, that data can be processed further. Lists are particularly adaptable in this way because there are many ways to reuse the data through channels like Power Apps or Power BI.

There are many code examples available that can help to solve automation problems. But the most important thing is to get the basics right. When that happens, everything clicks into place.

The script I used for the runbook can be downloaded from the Office 365 IT Pros repository on GitHub.

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365.

Cart

Cart