Azure Automation provide powerful capabilities to automate tasks across services both inside and outside Azure platform. In this post I will cover a scenario where you connect to SharePoint Online from Azure Runbook and display the list of all provisioned site collections for a particular Office 365 tenant. This post is predominantly geared towards getting you started on Azure Automation and performed basic configuration needed to connect to SharePoint Online. Once equipped with this knowledge you can build Runbooks that tackle more advance automation scenarios.

Prerequisites

First things first, for successfully performing steps in this post you will need both Office 365 and Azure subscriptions. If you don't already have them you may want to register for free trial version of Azure subscription and Office 365 . You also need to sign up for Azure Automation Preview . I also recommend reading article Getting started with Azure Automation to get yourself familiar with the concepts of Runbooks and Assets.

Importing SharePoint Online Integration Module

The first step is to import the SPO PowerShell module to Azure Automation so you can leverage its Cmdlets. It's important to note that technically PowerShell modules and integration modules are different. There is an excellent article explaining differences among these two. I've chosen a simpler route (step by step walkthrough is provided later in this section) to create an SPO integration module by zipping the contents of SPO module and import it as an integration module asset into Azure Automation.

NOTE: Although this approach works fine you won't get the full Automation connection properties capabilities and may need to wrap parts of the code in InlineScript to have it function properly (if you are creating complex types and then referencing methods). For the Runbook authored in this post you're are not going to use any of these capabilities.

Let's begin the process of importing the SPO module. You begin by downloading and installing Microsoft SharePoint Online Management Shell on a local machine. Once successfully installed browse to folder "C:\Program Files\SharePoint Online Management Shell" right click and zip the entire folder. You can use Send to à Compressed (zipped) folder feature form the context menu in Windows to zip the folder.

 

You should now have a zip file with name "Microsoft.Online.SharePoint.PowerShell.zip". It is important that you do not change the name of this file otherwise you won't able to import it as Azure Automation as an Integration module.

Next, you will import the zip file as an integration module.

Login to Azure Management Portal and select AUTOMATION from the left pane.

NOTE: For this walkthrough I will be using my Azure Automation account that I've created earlier. If you don't already have Automation account created please read Getting started with Azure Automation for instructions to do so. It usually take a moment to get it created.

Select the account and click ASSETS option from the top pane.

From the bottom pane select IMPORT MODULE

You will see 'IMPORT INTEGRATION MODULE' dialog. Select the file 'Microsoft.Online.SharePoint.PowerShell.zip' that you have created earlier and press Complete (the checkmark on the bottom right of the dialog)

You will notice messages start appearing on the operations status pane.

In few moments you see the success messages. At this point you can simply press DISSMISSS COMPLETED and these messages will go away.

Notice "Microsoft.Online.SharePoint.PowerShell" now appears in the list of available assets of type module. Module details page captures the detail including name, version etc. along with the list of Cmdlets.

At this point you have successfully imported SPO Integration module. You're now ready to put the SPO Cmdlets into action by authoring a Runbook, but before that let's store user credentials to a secure location that are needed by Connect-SPOService Cmdlet as it make connection to SPO Admin site.

Creating Credential Asset

The SPO Connect-SPOService Cmdlet requires user credentials to connect to SPO site. In this case connection will be made to SPO Admin site (https://{tanent}-admin.sharepoint.com). Azure Automaton provide Credential Asset type for securely storing user credentials.

From the bottom pane press ADD SETTING and then select ADD CREDENTIAL. You will be presented with an ADD CREDENTIAL dialog.

Select Windows PowerShell Credential from a CREDENTIAL TYPE drop down. Also fill in the NAME and DESCRITPION fields with values shown below.

Type the user name of your Office 365 Admin account (it's usually in the form of username@tenant.onmicrosoft.com) along with its password. Press the complete checkmark.

You receive success message.

Authoring Runbook

You are now ready to author Display-AllSPOSite Runbook.

From the top pane select RUNBOOKS and then press NEW. Then select RUNBOOK à QUICK CREATE and fill the details as shown below. Finally press CREATE.

Notice the success message. Press EDIT RUNBOOK.

You will be taken to Author Runbook page with display-allsposites displayed at the top. Notice basic boilerplate workflow script is added for you.

Download Display-AllSPOSites Runbook script from TechNet script gallery. Replace the boilerplate script shown in the previous step with Display-AllSPOSites Runbook script you've downloaded from the gallery. Relevant part of the script is shown below (comments at the top are omitted)

Let's walk through the script in Runbook. There are two input parameters. First is SPOAdminUrl which takes the url to SPO Admin Site as a parameter. It's in the format https://{tenant}.sharepoint-admin.microsoft.com. Second parameter is PSCredName which takes the name of Credential Asset. By taking these two important input attributes as a parameter you can run this Runbook against different SPO tenants. Notice this way you're not storing or passing credential as plain text. The OutputType parameter is provided because Runbook output url of SPO sites, it's a best practice to provide OutputType parameter whenever your Runbook have an output.

Line 40 uses Get-AutomationPSCredential to fetch the PSCredential object from the Credential Asset. It takes the name of the asset as an input parameter.

Line 42 use Connect-SPOSite Cmdlet to connect to SPO Site. It takes url to SPO site and PSCredential as input parameters. At this point connection to SPO admin site will take place. Finally Line 28 use Get-SPOSite Cmdlet. There are no parameter passed so it lists all provisioned site collections. The url for each site is displayed to the Output (there are other attributes available including Site Name, Quota etc.).

Testing Runbook

You're done with authoring, let's take it to the test drive.

Press TEST icon on the bottom pane. You will be asked to save the Runbook, press YES.

You will be presented with input parameter values dialog. Type 'SPOAdminCred' for the PSCEDNAME and url of your SPO Admin Site for SPOADMINSITEURL parameter. Press Complete (the checkmark icon) to close the dialog.

 

Notice Output Pane will show the status of the Runbook. It goes from Submittingà Queue à Starting à Running à Completed.

Once the STATUS is changed to COMPLETED you will see the output similar to following. Actual output will differ based on your SPO tenant and provisioned site collections.

Publish Runbook

You have completed the authoring and testing of Runbook. At this point you can actually publish it. Until this point it was essentially in Draft mode and was only available for testing.

From the bottom pane select PUBLISH. You will be asked to confirm the publishing, select Yes.

This will save and publish the Runbook. This is essentially like putting your code from Integration to Production. Once completed the Runbook will change its status from Draft to Published. At this point it's ready to Start. Press the Start from the bottom pane.

You will be asked to provide the parameter, please enter the same information as mentioned in previous section. The Runbook is scheduled to be run by the Job. Press VIEW JOB and it will take you to the Job details page.

On details page you will see details related to the job, input parameters and output parameters.

 

Wrapping up

Azure Automation is a powerful feature with capability to automate tasks across various services (both inside and outside Azure). In this article I've covered basics of connecting to SPO via Runbook, you can apply this learning to other services in Office 365.In SPO alone, there are plethora of scenarios that are good candidate for automation ranging from DevOps tasks to Information Worker related tasks. For example, you can author a simple Runbook and schedule it to monitor certain set of attributes in SPO (e.g. site quota, number of sites etc.) and raise an alert whenever their values goes beyond a certain threshold. You can also consider automating the deployment of various SPO artifacts using Runbook on different SPO tenants in a uniform manner.