SharePoint Online as frontend for Azure Automation

Posted: 21st December 2015 by Anders Bengtsson in Azure

Back in the Orchestrator days we had the Service Manager self-service portal that we could use to submit items that trigger runbooks in Orchestrator. The integration between Service Manager and Orchestrator worked great and the self-service portal brought a lot of value to automation scenarios. But time change and now we have a new executor in Azure Automation J

The challenge is that there is no connector between Azure Automation and Service Manager or any other portal. In this blogpost we will look at how we can use SharePoint Online as a frontend to Azure Automation. The process of a new request will be:

  1. User submits a new item in a SharePoint list
  2. A SharePoint workflow trigger a Azure Automation runbook
  3. Azure Automation does magic
  4. Azure Automation update the list item in SharePoint
  5. The user sees the result in the SharePoint list

Setting up SharePoint

  1. Sign-in to SharePoint online
  2. Add a custom list, click on the Add list tile
  3. Download and install SharePoint Designer on your workstation
  4. Once you have installed SharePoint Designer, click on Edit List in the List toolbox, SharePoint Designer will start and load your SharePoint site
  5. In SharePoint Designer click Edit List columns
  6. In the Edit List view, use the Add New Column and Column Settings to configure the list as you need it to be. In my example I have a list with a number of fields that are needed to create a new service account in Active Directory. I have also added a column named Result that Azure Automation use to write back the result from the runbook. There is also a column named Azure Automation Status that is used to report back the response when submitting the job to Azure automation. The SP Workflow and the column will be automatic created when we connect a workflow to the list.
  7. When the list is as you like it, click SAVE and go back to SharePoint and refresh the page

The list is now created. You can click New Item in the list view and submit new items. You can click Edit this view and add the ID column. The runbook will use the ID field to keep track of which list item to work with.

Setting up Azure Automation

Next step is to setup the Azure Automation runbook and configure the webhook. More general information about webhooks can be found here.

  1. First thing we need to do is configure Azure Automation with a SharePoint Online module. Tao Yang have a good blog post about this. Tao blogpost is about import the module in SMA, but that you should not try to do J instead only follow Tao steps to build the ZIP file. You can download his ZIP file and then you add the two DLL files that he also link too.
  2. Once you have the complete ZIP file, browse to your automation account in Azure Automation and click on Assets and then Modules
  3. On the Modules page, click Add a module, and upload the ZIP file. Remember that if you are planning to use a Hybrid worker the module must be installed on all hybrid workers too
  4. After the module is imported you need to setup a connection to your SharePoint site, for example. Remember that the service account used cannot be configured with two factor authentication, the account also has to have permissions on the SharePoint site.
  5. I have put together an example runbook for this scenario, which can be found here. It will first show/output you all data that comes from the webhook. It will then connect to SharePoint and get the current list and list item. In the end of the runbook an account is created and a hash table is created to update back into SharePoint. Either use my example runbook or build a new runbook.
  6. Next step, after the runbook is in place, is to create a webhook, click on the runbook, click webhook and add a new webhook. Remember to copy the webhook URL before clicking Create

Configure the SharePoint workflow

It is now time to configure the SharePoint workflow that will trigger the runbook when a new list item is created.

  1. Open SharePoint Designer and load your SharePoint site
  2. In SharePoint Designer, click Lists and Libraries on the left side, then click on your list
  3. On the right side on SharePoint Designer, click New… next to Workflows
  4. Name the new workflow, for example Workflow 0003 in my example. Use SharePoint 2013 Workflow as platform type
  5. When the workflow is created, configure it to start automatically when a new item in the list is created
  6. Click Edit Workflow to start build the new workflow
  7. The runbook, when complete, should look like this
  8. The first step is to build a Directory, map list fields with variables
  9. Next step is call the runbook webhook, paste the webhook URL. Remember to change to HTTP POST

  10. The last step in the workflow post the response from Azure Automation back to a column in a list. When the workflow do the HTTP POST to trigger the runbook a message is sent back, that is the message that you will write back to a column. This will be a simple log if the job has been submitted to Azure Automation successfully
  11. When all steps in the workflow are configured, click SAVE and then PUBLISH in SharePoint Designer.

Testing 1-2-3

We have now built a list in SharePoint, we have built a workflow in SharePoint that will invoke a runbook. The runbook performs some action, in this example it creates an AD account, and sends the results back to the list in SharePoint.

We fill in the list item. I guess that with a bit more SharePoint skills it would be possible to hide the two last test fields when filling in the information. Those two fields are only used to store status.

After a couple of seconds we can see that the workflow has run (Stage1) and that there is a response from Azure Automation when triggering the webhook (Accepted)

In Azure Automation we can see that the jobs has completed. We can see a lot of info as Output from the runbook. In a production runbook you might want to scale down all the extra code and output J

If we go back to SharePoint and do a refresh we can see there is a result saying the account already exists in AD and no new account has been created. If we submit a new item with request for another account is works, the new account is created J

If you would like to add approval steps to your solution, read more here.

OMS black belt Jakob also have great ideas about using SharePoint Online that I recommend, read it here

  1. […] runbooks through an Azure Automation Webhook. I also want to thank Anders Bengtsson for his quite similiar post that finally made me finish this one up. […]

  2. Hi, Sorry about that. The link is now working, you can now download the example runbook. Thanks for letting me know

  3. Joe says:

    “I have put together an example runbook for this scenario, which can be found here.” – looks like link is missing. Awesome post though!