There have been many clients that have asked for ways to automate processes in SharePoint Online without the need to get developers involved. Some of the requests were; create site collections, create sub-sites, apply permissions to a specific list or list item. In short, the kind of activity that only Admins have access rights to do but regular users ask for on a day to day basis.
If you are a SharePointer, like me, then you will likely already know about these abilities to automate business processes in at least one workflow product, Nintex. Disclaimer here, I like Nintex but do not work for them and am not recommending them for one reason, price.
I admit, I am fiscally conservative (read: cheap) when it comes to putting a solution together for my clients. In this case there is a small amount of cost which is all part of what we must bear to give rise to the robots….
The intent of this blog is to show you a way to leverage Flow, SharePoint and Azure Automation to automate the provision of a site collection without the need for the requester to have admin rights on the tenant. If you have read my other blog posts you will know that I am a little bit of a control nerd so I will include the ability to APPROVE the provision before it is complete. Let’s get to it…
SharePoint List and Flow
First we will create a list that holds our requests as shown below:
We add the URL Name, Status, Owner and Final URL columns to the Custom list. Of course, this is a demo so in production you can add many more. Next we create a Flow and the best way to do so is to click the Flow button on the list:
But contrary to what you may expect, I recommend that you click “See your flows”:
You should start from a blank Flow. When the next page comes up select the trigger “When an Item is created” as show below:
You will configure the trigger with the Url of the site and select the list, in this case AddSC.
Next we will add an initialize variable action as we have to initialize it first before we can use it:
As you have noted before my smart friends, I scrub out the domain this is created under but of course yours will be fully filled in.
Also note that I stopped at the path Sites, this will allow me to add the name of the supplied site to the variable with an append to string variable;
Although I am not building this in, you will want to use an action to check the status of the URL as to whether it exists or not since if the site does exist then the Flow would fail.
Next we are going to get an approval and response before we go any further.
So this approval will stop the Flow until there is a response, the response could come from an email or it could come from the Flow mobile app. This is really nice in that if you are the admin, you can approve or reject while sunning at the beach researching your next project….
Now we will put a condition in to see what response the admin has to our request for a site collection:
If Approved = Yes is all we are really here for so I will focus on that, I am sure that you are perfectly capable of setting up a nastygram email on the No side:
I am going to show you the Create Job action and then we are going to jump over into Azure Portal to set up the job:
So you will notice in this shot that there are several required fields, subscription, resource group and automation account that are required in order to make this work. I will now show you Azure Automation in Azure Portal then we will come back to this screen.
If you do not have an Azure account, you will need to sign up for one. I won’t go through the process here but it is not hard to do. Once you have an account, go to https://portal.azure.com and sign in to see your dashboard:
First we search for RESOURCE GROUP in the search box at the top, we then create a new Resource group that in my case is called Automation.
We then click to open the resource group and click ADD at the top of the RG. Once the screen comes up type in Automation and you should see the following:
Select Automation and you will be prompted to create an automation account. This would be a good place to stop and explain a couple of things. First, you need to be a Global Admin to even get to this point so if you are not a GA then find someone to work with that understands what you are trying to do. Next, when you click Create you will be setting up a run-as account in order to allow the automation to run as an elevated account without the requester necessarily logging in as one. But put your GA fears to rest, the run-as account can only call runbooks (scripts) that have already been set up so no rogue PowerShell will be run.
Once you click Create you will be prompted with the following:
There is a lot going on here so lets break it down. The Name field has to be unique and we would recommend something descriptive. The subscription is important, you must pick your subscription or the rest of the dialog does not work. Under Resource Group, pick the Resource Group that you already set up (which is why we had you set it up first). Under location, Our recommendation is pick the location that is closest to you. Under “Create Run-as account” leave that as Yes as that is really what we are here to do. I would highly recommend checking “Pin to dashboard” so it makes it easy to return to. Click Create.
Next click on the Automation account and you will see the following:
Again, a lot to unpack here and we wont go over all of it just the parts that pertain to our Flow (remember that?). You will want to click on Runbooks under Process Automation. Next Click “Add a Runbook” and click “Create a New Runbook” then fill out the Name of the runbook and select “Graphical PowerShell Workflow” as shown below:
Once you create the Runbook you will see:
A Note here: You will also need to add the SharePoint PnP module to the runbook account. Go back to the Account setting page and scroll down to Modules Gallery and you will see:
Click in the Search box at the top and type “PNP” hit enter and the search should show:
Select the top one, “SharePointPnPPowerShellOnline” and then click Import at the top of the screen. You can read more about SharePoint PnP for PowerShell in my previous blog post.
This should put the module in the Modules section as shown below:
While in the Account, select Credentials under Shared resources and click “Add a Credential”:
Fill in the info with an existing Azure AD user account that has at least SharePoint Admin rights. This user will be used by the run-as account to run the script.
Now Open the runbook you created earlier and go to the tool bar at the top and click “Input and Output”:
You should add the following based on the columns that were added to the custom list; CreateSC
We make them mandatory so they are surfaced as part of the Workflow.
Next we go to the search box on the top left “Search Library Items” and type in “Connect-pnp” and we get back the list of items we can add, we then select and add “Connect-PnPOnline” to the canvas:
Once there, click on the Blue box and you will see a blade on the right come out with parameters, click on the parameters to expand them:
We have 3 parameters that need to be filled in here, the first is the URL parameter which can be any url in your sharepoint tenant but leaving this blank will cause it to fail. Nothing will be done to the site in this URL parameter.
The second is most important, it is the SharePoint Admin site for the tenant and both of these can be constants.
The third param are the Credentials that we set up earlier:
Next we do a search for New-PnPTenantSite commandlet and add it to the canvas. You will notice that you can pull a connector between the two actions and you should do so. Then click on the line and the following blade should come up:
Make sure that Sequence is selected and close the Blade.
Now look at the parameters of the New-PnPTenantSite action much as we did earlier. There are a good many more parameters to set but we are only setting 9 of them:
In the params above you should set RemoveDeletedSite if you think the site may be still in the site collection recycle bin, this will remove it and allow the new site to be created otherwise it will fail.
In the TimeZone param, if you are on the Eastern TimeZone then set it to 10, if you need other time zones for this run Get-PnPTimeZoneId to get the list.
In the Title param, we go in and select Runbook Input as the Datasource and select the Input we created earlier:
We will do the same for the siteowner and siteurl params.
As you can see above, the template we are using is the Team site (STS#0) as a constant. If we choose to we could pass the template into the workflow much as we did the others and allow the user to select only the templates we choose for them.
Since we are doing additional activity after the site is created, we set the Wait param to true.
Finally, in the main screen of the runbook, we click on Publish to complete the sequence on the Azure Automation.
Back to the Future
Now lets go back to the action in Flow:
Now that you have walked through setting the automation, you understand what these parameters do and why they are needed. Notice at the bottom the Parameters for Input that we added, this is where they get surfaced so that you can set them.
You remember that we set the job up to wait until the site collection was created, now is the point at which that becomes important. We want to write back to the list item that started the request but want to make sure the job completed before sending out an email to the requester.
The ID is from the action “When an item is created” as are the Title, URL Name, Owner Claims (since the Site Owner column was a person field) and the FullURL (column type hyperlink) comes from the variable.
The final action is to send an email to the person that created the item and any others that should know about the site collection:
And there you have it, a NO-CODE solution to automate the self service provisioning of site collections. One thing that I want to point out is that if you manage the site collections in this manner then you will always have a list of the site collections in your environment and the dates they were created.
If you feel that this has been of some interest to you all I ask is that you post a comment and let me know.
Live Long and Prosper,