SharePoint Online Workflow Webhook | Tailspintoys

As a follow up to one of my earlier posts where I Create AD Users with SharePoint Online as frontend, I now wanted to share an extension of this solution where we will utilize Azure Automation with a Hybrid Worker to do the heavy lifting.
So basically, instead of having a script as a scheduled task reading the SharePoint list and perform tasks based on status, we will now have a SharePoint workflow to initiate our 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. 🙂

OVERVIEW
The post will cover the following steps

Create an Azure Automation runbook with a WebHook that executes the runbook on a Hybrid Worker.
Create a basic SharePoint workflow in SharePoint Designer with approval steps prior creating the user.
End user experience.

PREREQUISITES
I assume that you already have the following in place.

A custom SharePoint list with columns to fit your needs.
A machine with Sharepoint Designer 2013 installed. Note that you need to have Service Pack 1 installed in order to connect to SharePoint Online.
Azure PowerShell
An Azure Automation Account with a Hybrid Worker registered. Note that you since a couple of weeks can run your Hybrid worker with a other credentials then local system, which is really nice in our particular case. Since I will use Tao Yang’s SharePointOnline PowerShell module which is utilizing the SharePoint Server 2013 Client Components SDK to integrate with SharePoint Online, that module including the SDK needs to be installed on the Hybrid Worker(s) as well.
A service account that have permissions to the SharePoint list that will be used to write back status and information.

CREATE THE AUTOMATION RUNBOOK AND WEBHOOK
I will use Azure PowerShell create and publish the runbook.

1. First we need to create a credential asset that will be used to write back information to our list.

 
#Login to Azure 
Login-AzureRmAccount
#Define ResourceGroup and Automation Account to be used used for with Automation cmdlets
$PSDefaultParameterValues = @{
    '*-AzureRmAutomation*:ResourceGroupName' = 'ResourceGroup'
    '*-AzureRmAutomation*:AutomationAccountName' = 'MyAutomationAccount'
}
#Create credential object with the SPO service account
$SPOCreds = Get-Credential 
#Create credential asset in Azure Automation
$SPOCredsHt = @{
    Name = 'SPOAdmin'
    Description = 'SharePoint Online Service Account for user onboarding'
    Value = $SPOCreds
}
New-AzureRmAutomationCredential @SPOCredsHt

2. Next up we need to create the actual runbook. Since we are doing everything with PowerShell, we need to save the script to a file and import it to Azure Automation. My example runbook (CreateADUserfromSPO.ps1) can be found below.

$SPORunbookHt = @{
    Path = 'C:\CreateADUserfromSPO.ps1'
    Name = 'Create-ADUsersFromSPO'
    Type = 'PowerShell'
    Published = $true
}
Import-AzureRmAutomationRunbook @SPORunbookHt

CreateADUserfromSPO.ps1

param ( 
    [object]$WebhookData
)

#SharePoint Connection Info
$SPCredentials = Get-AutomationPSCredential -Name 'SPOAdmin'
$SPConnection = @{
    SharePointSiteURL = "https://threesixfivelab.sharepoint.com"
    Username = $SPCredentials.Username
    Password = $SPCredentials.GetNetworkCredential().Password
}
#Create an object from the webhook request
$UserData = ConvertFrom-Json -InputObject $WebhookData.RequestBody


$TargetOU = "OU=Users,OU=ACME,DC=corp,DC=acme,DC=com"
$ListName = "UserOnboarding"
$PasswordLength = "12"
$UPNSuffix = '365lab.net'
$FirstName = $UserData.FirstName
$LastName = $UserData.LastName
$Title = $UserData.Title
$Manager = $UserData.Manager

#region functions
function Convert-ToLatinCharacters {
    param(
        [string]$inputString
    )
    [Text.Encoding]::ASCII.GetString([Text.Encoding]::GetEncoding("Cyrillic").GetBytes($inputString))
}
  
function Get-JDDNFromUPN {
    param (
        [ValidateScript({Get-ADUser -Filter {UserprincipalName -eq $_}})] 
        [Parameter(Mandatory=$true)][string]$UserPrincipalName
    )
        $ADUser = Get-ADUser -Filter {UserprincipalName -eq $UserPrincipalName} -ErrorAction stop
        return $ADUser.distinguishedname
}
  
function New-JDSamAccountName {
    param (
        [Parameter(Mandatory=$true)][string]$FirstName,
        [Parameter(Mandatory=$true)][string]$LastName,
        [parameter(Mandatory=$false)][int]$FirstNameCharCount = 3,
        [parameter(Mandatory=$false)][int]$LastNameCharCount = 3
    )
    #Construct the base sAMAccountName
    $BaseSam = "{0}{1}" -f (Convert-ToLatinCharacters $FirstName).Substring(0,$FirstNameCharCount),(Convert-ToLatinCharacters $LastName).Substring(0,$LastNameCharCount)
  
    #Add a number until you find a free sAMAccountName
    if (Get-ADUser -Filter {samaccountname -eq $BaseSam} -ErrorAction SilentlyContinue) {
        $index = 0
        do {
            $index++
            $sAMAccountName = "{0}{1}" -f $BaseSam.ToLower(),$index
        } until (-not(Get-ADUser -Filter {samaccountname -eq $sAMAccountName } -ErrorAction SilentlyContinue))
    } else {
        $sAMAccountName = $BaseSam.tolower()
    }
    return $sAMAccountName
}
      
function New-JDUPNAndMail {
    param (
        [Parameter(Mandatory=$true)][string]$FirstName,
        [Parameter(Mandatory=$true)][string]$LastName,
        [Parameter(Mandatory=$true)][string]$UPNSuffix
     )
    #Construct the base userPrincipalName
    $BaseUPN = "{0}.{1}@{2}" -f (Convert-ToLatinCharacters $FirstName).replace(' ','.').tolower(),(Convert-ToLatinCharacters $LastName).replace(' ','.').tolower(),$UPNSuffix
      
    if (Get-ADUser -Filter {userprincipalname -eq $BaseUPN} -ErrorAction SilentlyContinue) {
        $index = 0
        do {
            $index++
            $UserPrincipalName = "{0}{1}@{2}" -f $BaseUPN.Split("@")[0],$index,$UPNSuffix
        } until (-not(Get-ADUser -Filter {userprincipalname -eq $UserPrincipalName} -ErrorAction SilentlyContinue))
  
    } else {
        $UserPrincipalName = $BaseUPN
    }
    return $UserPrincipalName
}
  
function New-JDADUser {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param (
        [Parameter(Mandatory=$true)][string]$FirstName,
        [Parameter(Mandatory=$true)][string]$LastName,
        [Parameter(Mandatory=$true)][string]$UserPrincipalName,
        [Parameter(Mandatory=$true)][string]$sAMAccountName,
        [Parameter(Mandatory=$true)][string]$Title,
        [Parameter(Mandatory=$true)][string]$OU,
        [Parameter(Mandatory=$true)][string]$Manager,
        [Parameter(Mandatory=$true)][int]$PasswordLength = 12
    )
     #Generate a password
     $Password = [System.Web.Security.Membership]::GeneratePassword($PasswordLength,2)
  
     #Construct the user HT
     $ADUserHt = @{
        GivenName = $FirstName
        SurName = $LastName
        ChangePasswordAtLogon = $true
        EmailAddress = $UserPrincipalName
        UserPrincipalName = $UserPrincipalName
        sAMAccountName = $sAMAccountName
        Title = $Title
        Name = "$FirstName $LastName ($sAMAccountName)"
        Displayname = "$FirstName $LastName"
        Manager = $Manager
        Path = $OU
        AccountPassword = (ConvertTo-SecureString -String $Password -AsPlainText -Force)
        Enabled = $true
        OtherAttribute = @{proxyAddresses = "SMTP:$UserPrincipalName"}
     }
     try {
        #Create the user and return a custom object
        New-ADUser @ADUserHt -ErrorAction Stop 
        Write-Verbose "Successfully created the user $($ADUserHt.Name)"
        [pscustomobject] @{
            sAMAccountName = $ADUserHt.sAMAccountName 
            UserPrincipalName = $ADUserHt.UserPrincipalName 
            Password = $Password
        }
     } catch {
        Write-Warning "Error creating the user $($ADUserHt.Name) `r`n$_"
     }
}
#endregion functions
    try {
        $sAMAccountName = New-JDSamAccountName -FirstName $Firstname -LastName $LastName
        $UPNandMail = New-JDUPNAndMail -FirstName $Firstname -LastName $LastName -UPNSuffix $UPNSuffix
        $ManagerDN = Get-JDDNFromUPN -UserPrincipalName $Manager
  
        #Create the user in Active Directory
        $NewAdUserHt = @{
            FirstName = $Firstname
            LastName = $LastName
            Manager = $ManagerDN
            sAMAccountName = $sAMAccountName
            UserPrincipalName = $UPNandMail
            Title = $Title
            OU = $TargetOU
            PasswordLength = $PasswordLength
        }
        Write-Output $NewAdUserHt
        $ADUser = New-JDADUser @NewAdUserHt -ErrorAction Stop
        $UpdateHt = @{
            Status = 'Created'
            UserName = $ADUser.sAMAccountName 
            EmailAddress = $ADUser.UserPrincipalName 
        }
        Update-SPOListItem -SPOConnection $SPConnection -ListItemID $UserData.ID -ListFieldsValues $UpdateHt -ListName $ListName
    } catch {
         $UpdateHt = @{
            Status = 'Error'
        }
        Update-SPOListItem -SPOConnection $SPConnection -ListItemID $UserData.ID -ListFieldsValues $UpdateHt -ListName $ListName
        Write-Warning $_
    }

3. Last step is to create the Webhook that will be used when triggering the runbook from the SharePoint workflow. As notified when running the cmdlet, save the WebhookURI, you will need it later and cannot see it more than on Webhook creation. I have set the Webhook expirytime to 2 years, change it according to your needs.

$SPOWebHookHt = @{
    Name = 'SPOWorkflow'
    RunbookName = 'Create-ADUsersFromSPO'
    IsEnabled = $true
    ExpiryTime = (Get-Date).AddDays(730)
}
New-AzureRmAutomationWebhook @SPOWebHookHt

CONFIGURE THE SHAREPOINT WORKFLOW
1. Open SharePoint Designer and open your website containing the custom list.
2. Add a new List Workflow to your site and associate the workflow with your custom list.

3. Click on the workflow and edit the Start Options to kick of the workflow when a new item has been created.

4. Edit and customize the workflow to fit your needs. In my case, I will have one stage for manager approval and one stage for executing the automation runbook.

Manager approval stage
a. Create a “Start a task process action” to submit the request to the manager.

b. Create a condition to Execute the runbook if the request was approved.

Execute Azure Automation Runbook stage
a. First build a dictionary with the data you want to pass to the automation webhook.

b. Insert a “Call HTTP Web Service” action and configure it to POST the earlier created dictionary to the Webhook url you saved creating the Webhook.

c. Our last step before ending the workflow will be to write back the response from the webhook to the SharePoint list item. This makes it easier for us to track if the task was properly submitted to Azure Automation.
5. Check the workflow for errors before saving and publishing. This is my finished workflow before publishing it.

IN ACTION
First, we create a new item in the SharePoint list to start the process. 2016-01-09_19-04-18

The manager approval process sends an email to the new users manager as we stated in the workflow.

After the manager has approved the request, the workflow will kick off the runbook, create the user in Active Directory and write back the UserName/EmailAddress and status of the newly created user to the list. In this example, a password is generated but not distributed to anyone.

My hope is that you with this post have got some inspiration on what you can achieve with Azure Automation in combination with SharePoint (Online or On-Prem). Sky is the limit!

/Johan

Tailspintoys – 365lab.net

Office 365, Azure and Microsoft Infrastructure with a touch of PowerShell.

Tag Archives: SharePoint Online Workflow Webhook

Create AD Users with help from Azure Automation and SharePoint Online