Kelverion Automation Portal Dispatcher

Posted on by Ryan Nielsen

In this post we are going to take a look at how we integrated our Azure Automation account with the Automation Portal. We refer to this integration as our universal dispatcher, it handles initiating all of our self service requests and sending status updates when a request reaches a terminal status.

Overview

Once submitted, the request is stored in the request table inside the Automation Portal database. An Azure Logic App monitors the request table for changes and triggers a runbook in the Azure Automation Account.

The Azure Logic App

If you installed the portal on-premise you will need to setup an on-premises data gateway before you can create your Logic App. Once you are ready, create a new Logic App and select “Blank Logic App” as the template.

The first activity in your logic app will be the SQL trigger “When an item is modified (V2)”. Configure it to connect to the Request table in your Automation Portal database. Next add the Filter Query parameter and set a filter to cover the states you will be taking action on. Ours looks something like this.

(State eq 'New' or State eq 'Approved' or State eq 'Complete' or State eq 'Failed') and (Deleted eq false)

In our case, we have not started requiring approval for any of our service offerings and did not include the “Pending Approval” status. However, we did include the “Approved” status because a user can go in and manually approve a request even if it didn’t require approval.

The next step in our Logic App will be the “Create job” step from the Automation Account connector. You will need to connect it to your Automation Account and select your main dispatcher runbook (we called ours “Process-KAPRequest”). When you are finished it should look something like this.

Azure Automation Runbooks

One of our goals with the Automation Account was to break our scripts down into small reusable runbooks. We also build everything with the assumption that any dependencies needed may not exist (PowerShell modules, executable, ect..).

The Automation Account portion of the dispatcher is broken out into two main runbooks.

  • Process-KAPRequest
  • Get-KAPRequestData

The first runbook Process-KAPRequest is initiated by the Logic App. It evaluates the state of the request and either initiates the runbook that will process the request, or sends the requester an email with the status of their request.

The second runbook Get-KAPRequestData retrieves the request data which is stored as encoded Xml and converts it into a PSCustomObject to simplify accessing the request data when processing the request.

Now lets take a look at the first runbook. Process-KAPRequest is broken out into two main functions ” Start-KAPRequest” and ” Send-KAPNotification”.

When a request comes in with a state of “New” or “Approved” we pass it over to the Start-KAPRequest function. First we retrieve the database name, server name, and credentials for our persistent data store from the automation account.

$PersistentDataStore = Get-AutomationVariable -Name 'PersistentDataStore'

$PersistentDataStoreServer = Get-AutomationVariable -Name 'PersistentDataStoreServer'

$PSCred = Get-AutomationPSCredential -name "SQLCred"

Then we use the Service name and Offering name from the request to create a query against our custom mapping table and retrieve the name of the runbook and hybrid worker group that will process the request. 

$QueryRunbookName = "SELECT [RunbookName],[HybridWorkerGroupName] FROM [PersistentDataStore].[dbo].[AutomationPortal_OfferingRunbookMapping] Where ServiceName = '$ServiceName' and OfferingName = '$OfferingName'"

Once we have retrieved the runbook information we start the runbook and then update the status of the the request in the Automation Portal database.

$RunbookDetails = .\invoke-DbaQuery.ps1 -Server $PersistentDataStoreServer -Database $PersistentDataStore -Query $QueryRunbookName -Credential $PSCred -ErrorAction "Stop"

$RunbookName = $RunbookDetails.RunbookName
Write-Verbose -Message "Runbook name: $RunbookName"
$HybridWorkerGroup = $RunbookDetails.HybridWorkerGroupName
Write-Verbose -Message "Hybrid worker group name: $HybridWorkerGroup"
$params = @{"RequestID"=$RequestID}
Try{
    $Job = .\Start-AzureRunbook.ps1 -Runbook $RunbookName -Parameters $Params -Runon $HybridWorkerGroup -ErrorAction "Stop"
    Write-Verbose -Message "Setting request status to in progress"
    .\Update-KapRequest.ps1 -RequestID $RequestID -Message "Successfully initiated request" -State "In Progress"
}
Catch{
    Write-Verbose -Message "Setting request status to failed"
    .\Update-KapRequest.ps1 -RequestID $RequestID -Message "Failed to initiated request" -State "Failed"
    Write-Error -Message $_.Exception.Message
}

The second runbook Get-KAPRequestData is called at the start of the job that is created to process the actual request. It accepts the requestID as a parameter, queries the Automation Portal database for the request data, then returns a PSCustomObject containing the request data.

Param (
    [Parameter(Mandatory=$true)][string]$RequestID
    )
# Query used to retreive automation request data from portal database.
$RequestQuery = "SELECT * FROM [AutomationPortal].[dbo].[Request] Where ID = $RequestID"

Write-Verbose -Message "Retrieving variables from automation account"
$AutomationPortalDatabase = Get-AutomationVariable -Name 'AutomationPortalDatabaseName'
Write-Verbose -Message "Retrieved Database name: $AutomationPortalDatabase"
$AutomationPortalDatabaseServer = Get-AutomationVariable -Name 'AutomationPortalDatabaseServer'
Write-Verbose -Message "Retrieved Server name: $AutomationPortalDatabaseServer"
$SQLCred = Get-AutomationPSCredential -name "SQLCred"
Write-Verbose -Message ("Credential name " + $SQLCred.username)

Write-Verbose -Message "Retrieving request data"
$RequestObject = .\Invoke-DbaQuery.ps1 -Server $AutomationPortalDatabaseServer -Database $AutomationPortalDatabase -Query $RequestQuery -Credential $SQLCred

Write-Verbose -Message "Extracting XML data from request"
$RequestXml = $RequestObject.Data

Write-Verbose -Message "Converting request XML to custom PSobject"
$RequestPSObject = .\Convert-KAPRequestXml.ps1 -RequestXml $RequestXml -ErrorAction "Stop"

Return $RequestPSObject

The Convert-KAPRequestXml child runbook contains the Xsl stylesheet provided by Kelverion. It uses a runbook I covered previously, Apply-XmlTransform to convert the encoded Xml data into something we can then process into the PSCustomObject.

Param (
    [Parameter(Mandatory=$true)][string]$RequestXml
	)

# Stylesheet provided by Kelverion for use with Automation Portal request data
$Xslt = [xml]@'
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output omit-xml-declaration="yes" indent="yes"/>
	<xsl:template match="/Request">
	<Request>
		<xsl:for-each select="Field">
			<xsl:variable name="fname"><xsl:value-of select="translate(string(@Name),' ','_')" /></xsl:variable>
			<xsl:element name="{$fname}">
				<xsl:attribute name='Id'><xsl:value-of select="@Id" /></xsl:attribute>
				<xsl:value-of select="." disable-output-escaping="yes" />
			</xsl:element>
				</xsl:for-each>
		</Request>
	</xsl:template>
</xsl:stylesheet>
'@

# Prepare request data for xml transform
Write-Verbose -Message "Creating XML object from request data"
$XmlData = [xml]$RequestXml

# Pass Xml data to transform runbook along with Xsl template
Write-Verbose -Message "Applying Xsl stylesheet"
$XmlOutput = .\Apply-XmlTransform.ps1 -Xml $XmlData -Xslt $Xslt -ErrorAction "Stop"

Now that the Xml data is ready to process we can create our PSCustomObject. We then process each of the child nodes adding the name value pairs to our object. If one of your fields contained a multi-value table its contents will also be stored as Xml. We loop through each of the child nodes in that fields table creating an array that gets added to our PSCustomObject like our string fields. 

$RequestPSObject = New-Object -TypeName PSCustomObject
$XmlOutput.ChildNodes.childnodes | ForEach-Object {
    If($_.'#Text' -like "*<row>*"){
        $ChildName = $_.Name
        $ChildXML = [xml]$_.'#Text'
        [array]$RequestChildArray = $null
            $ChildXML.ChildNodes.ChildNodes | ForEach-Object {
                $RequestChildPSObject = New-Object -TypeName PSCustomObject
                $_.ChildNodes | ForEach-Object { $RequestChildPSObject | Add-Member -MemberType NoteProperty -Name $_.Name -Value $_.'#Text' }
                [array]$RequestChildArray+=$RequestChildPSObject
            }
            $RequestPSObject | Add-Member -MemberType NoteProperty -Name $ChildName -Value $RequestChildArray
    }
    Else{
        $RequestPSObject | Add-Member -MemberType NoteProperty -Name $_.Name -Value $_.'#Text'
    }
}

return $RequestPSObject

All of the runbooks used in our dispatcher are available here. In my next post I will cover creating a service offering and and what the contents of the PSCustomObject look like.