I have been recently working on a solution for a customer whereby they required Microsoft 365 Service Messages to be logged as an incident using a Service Desk tool and publish to Microsoft Teams Channel.

Microsoft Teams – This was the simple bit as a nice person in the community built a PowerShell script available on GitHub.

Now for the publishing the information to SharePoint Online, this has caused quite a few headaches from banging my head on my desk but it now working. The reason for publishing to SharePoint List is so that it can be used / reference as a data source to pulling the information into ServiceNow.

You can bit with Power Automate however the information required and provided is lacking so the route I am going to demostrate provide a lot more details. Just like the Microsoft Teams post

I have been recently working on a solution for a customer whereby they required Microsoft 365 Service Messages to be logged as an incident using a Service Desk tool and publish to Microsoft Teams Channel.

Microsoft Teams – This was the simple bit as a nice person in the community built a PowerShell script available on GitHub.

https://github.com/einast/PS_M365_scripts/blob/master/AzureAutomation/AzO365ServiceHealth.ps1

Now for publishing the information to SharePoint Online, this has caused quite a few headaches from banging my head on my desk but it now works. The reason for publishing to SharePoint List is so that it can be used / reference as a data source to pulling the information into ServiceNow.

You can bit with Power Automate however the information required and provided is lacking so the route I am going to demonstrate provide a lot more details. Just like the Microsoft Teams post

Create the SharePoint Site

The first thing to do is create a SharePoint Site that can be used as the location for the data. This can be simply done by connecting to SharePoint via PowerShell and running the following command.

You will need the following PowerShell Module
https://www.powershellgallery.com/packages/PnP.PowerShell

$URL =  "blogabout.sharepoint.com"
$Admin = "thewatcher@blogabout.cloud"

$cred = (Get-Credential)

Connect-AzureAD -Credential $cred

Connect-PnPOnline -UseWebLogin -Url $url

New-PnPTenantSite -Url $URL -Title "Health Status" -Owner "$Admin" -TimeZone "2"     

We now need to gather some information which will be used later in this blog. Using the same PowerShell console run the following cmdlet

Get-PnPList

Make a note of the Id for Health Status as we will need this later on.

We now need to configure the SharePoint List with required columns for receiving the data.

The column used are;

– Title
– Status
– Severity
– Service
– Classification
– Uri
– Message
– Reference

Modifying your Azure Automation Account for SharePoint Lists

In this post, I will make an assumption that you have already implemented pushing messages to Microsoft Teams. If you havent, please visit this article.

First of you all, we need to add PnP.PowerShell module to this Azure Automation account. Go to Modules under Shared Resources and Browse Gallery.

Search for PnP.PowerShell and click on the module

Click Import

The import may take a few minutes to complete and once complete we can go on to the next step.

Select Credentials from Shared Resources

Important Notice

Some of the next steps I have already completed in my environment.

Click “Add a credential” and provide;

-Name – This is important for the PowerShell script to call the credentials
– Username
– Password

You can also provide a description if required.

It is time to now modify the Runbook with the required PowerShell cmdlets for publishing to SharePoint Lists.

Select your run book to edit the script

Under User-Defined Variables I have added the following;

Please note: I am not using Automated Variables in this script but they could be used if you add the variable and use Get-AutomatedVariable -Name

$SpListName = "Health-Status"
$ListName = "Add the Health Status ID you gathered earlier"
$SpSiteUrl = "https://blogabout.sharepoint.com/sites/$SpListName"

https://pnp.github.io/powershell/articles/authentication.html

$myCred = Get-AutomationPSCredential -Name 'MyCredential'
$userName = $myCred.UserName
$securePassword = $myCred.Password
$password = $myCred.GetNetworkCredential().Password

$SpCreds = New-Object System.Management.Automation.PSCredential ($userName,$securePassword)

As you can see from above I am using Get-AutomationPSCredential -Name MyCredential to call stored credentials.

Under the following PowerShell cmdlet in the script

# If any new posts, add to Teams
Invoke-RestMethod -uri $uri -Method Post -body $Payload -ContentType 'application/json; charset=utf-8'

I have added;

# Add news post to Sharepoint Site
Connect-PnPOnline $SpSiteUrl -Credentials $SpCreds
$list =  Get-PnPList -identity $ListName

Add-PnPListItem -List $list -Values @{"Title"=$inc.Title;"Reference"=$inc.Id;"Status"=$inc.Status;"Severity"=$inc.Severity;
                "Service"=$inc.WorkloadDisplayName;"Classification"=$inc.Classification;"Uri"=$inc.PostIncidentDocumentUrl;"Message"=$Message}

This will now allow you to publish the data to SharePoint as shown below,

I am using Power Automate to trigger the Runbook for more detail visit the below post.

Hopefully this post will help you publish the data to SharePoint.

Regards
The Author – Blogabout.Cloud