Planet PowerShell logo

Contents

Creating a Powershell Automation Scheduling App With Azure and Microsoft Powerapps

For me, one of the most annoying kinds of tickets you can receive at a helpdesk is a request for temporary permissions. You receive a ticket stating that “this user” should be granted access to this SharePoint site, and the permission should be removed after 1 week. Or “this old mailbox” should be restored, and “that user”, should only have access for the next couple of days.

The Tickets are easy enough to complete, but the annoying part is to schedule that you need to perform a task two times, one for granting the permissions, then wait. And then the second time to remove the permission, before you can actually close the ticket.

This led me to think about how I could use an Azure Automation Account, a PowerApp, and PowerShell to create an automation framework for easy scheduling of these types of automation.

You can find all code related to this project in my Github Repo

Overview of the project

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/application-diagram.png

Creating the Azure resource

For the project you will need the following resource:

  • Azure Automation Account
  • Storage Account
  • Azure Functions App
  • Azure API Management

for creating the resources you can use the following cli commands;

Creating resource group:

1
2
3
4
$rg = "rg-test-automation"
$location = "North Europe"

az group create --resource-group $rg --location $location

Azure Automations Account:

1
2
3
$aaccountname = "aa-test-automations"

az automation account create --automation-account-name $aaccountname --location $location --sku "Free" --resource-group $rg

Storage Account:

1
2
3
$storageaccountname = "scriptingchrisstorage"

az storage account create --name $storageaccountname --resource-group $rg --location $location --sku Standard_LRS

Azure Function App:

1
2
3
4
$appname = "fa-test-automations"
$consumptionLocation = "northeurope"

az functionapp create --resource-group $rg --consumption-plan-location "northeurope" --name $appname --os-type Linux --runtime powershell --storage-account $storageaccountname --runtime-version 7.2 --functions-version 4

Azure API Management:

1
2
3
$apimname = "test-automation-api"

az apim create --name $apimname --resource-group $rg --publisher-name "ScriptingChris" --publisher-email "[email protected]" --no-wait

Creating a service principal for running the Azure Function integration into the Azure Automation Account

You will need a service principal for connecting from you Azure Function App to your Automation Account. To create the service principal you can use the following command:

1
az ad sp create-for-rbac --name "sp-test-automation" --role "contributor" --scopes "/subscriptions/<subscription-id>/resourceGroups/rg-test-automation/providers/Microsoft.Automation/automationAccounts/aa-test-automation"

The command will give an output similar to below. Here it is important to note down the “appId”, “password” and the “tenant”, since you will use the info later.

1
2
3
4
5
6
{
  "appId": "2fe6c698-15f2-4b19-9c1d-99beb94777af",
  "displayName": "sp-test-automation",
  "password": "R6asdsAQs6H_MPO2Xs414emKsdsuW-APOMas~i",
  "tenant": "<tenant-id>"
}

Creating the Azure Function

The Azure function will be a quite simple PowerShell script. The script will be able to take different parameters provided by the PowerApp. These parameters then define which runbook should be scheduled and provide the parameters to that specific Runbook.

When creating this setup, and the runbooks for this setup it is very important to think about which types of Automation scripts run and which types of parameters these runbooks need to be able to run.

Setting the Appsettings for passing credentials to the function

If you use the Azure Function extension in vscode, you can open the file local.settings.json and add in the following variables:

Setting Value
SP_APP_ID Service Principal App id
SP_APP_SECRET Service Principal Secret
TENANT_ID Azure Tenant id
RESOURCE_GROUP Name of the resource group
AUTOMATION_ACCOUNT Name of the automation account

your local.settings.json should look similar to:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME_VERSION": "~7",
    "FUNCTIONS_WORKER_RUNTIME": "powershell",
    "SP_APP_ID": "ServicePrincipalAppId",
    "SP_APP_SECRET": "<ServicePrincipalSecret>",
    "TENANT_ID": "<TenantId>",
    "RESOURCE_GROUP": "rg-test-automation",
    "AUTOMATION_ACCOUNT": "aa-test-automation"
  }
}

The local.settings.json is only for testing locally, you will need to set the appsettings for the FunctionApp either in Azure or through vscode, before you deploy the functions.

Setting the Route Parameter

To be able to select which runbook you want to schedule just by passing in the runbook name directly into the URL, you will need to set the route parameter in your function.json file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "Request",
      "methods": [
        "post"
      ],
      "route": "{RunbookName}"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "Response"
    }
  ]
}

with the route parameter {RunbookName}, you can now pass the name of the runbook, to the Azure function by passing it into the URL

Here is an example: http://localhost:7071/api/test-runbook

Creating the PowerShell function which creates the schedules

I have created the PowerShell function shown below. I won’t go too much in-depth about the logic, but basically, the function accepts the start time, description, and the runbook parameters through an HTTP request body.

An example of the body could be:

1
2
3
4
5
6
{
    "StartTime": "2022-05-20 09:45:00",
    "Parameters": {
        "Name": "Christian"
    }
}

The function then checks to see if the requested runbook exists. If it exists then it will try to create a new schedule and then assign the schedule to the specified runbook.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

$body = $Request.Body # Saving request body into variable
$params = $Request.Params # Saving route parameter into variable

Write-Output "StartTime: $($body.StartTime)"
Write-Output "RunbookName: $($params.RunbookName)"

# Connecting to Azure with service principal
Write-Output "Creating the credential object for the service principal: $($env:APP_ID)"
$Credential = New-Object System.Management.Automation.PSCredential `
    -ArgumentList $env:SP_APP_ID, `
    (ConvertTo-SecureString $env:SP_APP_SECRET -AsPlainText -Force)

Write-Output "Connecting to Azure with service principal"
Connect-AzAccount -ServicePrincipal -TenantId $env:TENANT_ID -Credential $Credential


# Checking if the runbook exists
Write-Output "Checking that Runbook exists"
try {
    $Runbook = Get-AzAutomationRunbook `
        -ResourceGroupName $env:RESOURCE_GROUP `
        -AutomationAccountName $env:AUTOMATION_ACCOUNT `
        -Name $params.RunbookName
    Write-Output "Runbook found!"
}
catch {
    $Runbook = $false
}


# If the runbook exists, then create a new schedule
if($Runbook -ne $false) {
    # Scheduling the PowerShell Runbook in Azure
    # To Find your timezone you can use: ([System.TimeZoneInfo]::Local).Id
    $TimeZone = "Romance Standard Time" # Set the timezone for your environment
    $ScheduleId = "automation-" + (New-Guid).Guid.split("-")[0] #  Gives an id looking like: automation-b0736aa1

    try {
        Write-Output "Creating Runbook schedule with id: $($ScheduleId)"
        $output = New-AzAutomationSchedule `
            -AutomationAccountName $env:AUTOMATION_ACCOUNT `
            -Name $ScheduleId `
            -StartTime (Get-Date $body.StartTime) `
            -Description $body.Description `
            -OneTime `
            -ResourceGroupName $env:RESOURCE_GROUP `
            -TimeZone $TimeZone
        
        Write-Output "Registering the new schedule with a runbook"
        $Registration = Register-AzAutomationScheduledRunbook `
            -AutomationAccountName $env:AUTOMATION_ACCOUNT `
            -Parameters $body.Parameters `
            -RunbookName $Runbook.Name `
            -ScheduleName $ScheduleId `
            -ResourceGroupName $env:RESOURCE_GROUP
    }
    catch {
        Write-Error -Message "$($_)"
    }
}
else {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 404 # If the runbook doesn't exists then provide a 404 error
        Body = "404 - Runbook: $($body.RunbookName), was not found"
    })
}


if($Registration) {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 201 # if the schedule was created then provide a 201 status code
        Body = $output | ConvertTo-Json
    })
}
else {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 500 # if the creation of the schedule failed, then provide a 500 error
        Body = "$($Error[0])"
    })
}

Creating the PowerShell function which get current scheduled runbooks

The second function i have created is made for retrieving all the scheduled runbooks. This way you can get a good overview in the app of which runbooks are already scheduled and waiting to be executed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

# Connecting to Azure with service principal
Write-Output "Creating the credential object for the service principal: $($env:APP_ID)"
$Credential = New-Object System.Management.Automation.PSCredential `
    -ArgumentList $env:SP_APP_ID, `
    (ConvertTo-SecureString $env:SP_APP_SECRET -AsPlainText -Force)

Write-Output "Connecting to Azure with service principal"
Connect-AzAccount -ServicePrincipal -TenantId $env:TENANT_ID -Credential $Credential

try {
    $Schedules = Get-AzAutomationSchedule `
        -ResourceGroupName $env:RESOURCE_GROUP `
        -AutomationAccountName $env:AUTOMATION_ACCOUNT | `
        Where-Object {$null -ne $_.NextRun} | `
        Select-Object Name, CreationTime, NextRun, TimeZone, Description | `
        ConvertTo-Json

        Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
            StatusCode = 200 # If the runbook doesn't exists then provide a 404 error
            Body = $Schedules
        })
}
catch {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 500 # if the creation of the schedule failed, then provide a 500 error
        Body = "$($Error[0])"
    })
}

Testing the Azure Function

Now I have created a very simple Automation Runbook, which takes a parameter “Name” and writes out a message.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
param (
	[Parameter(Mandatory=$true)]
	[String]$Name
)

$now = (Get-Date).ToString("yyyy-MM-dd HH:mm")
Write-Output "PowerShell Runbook was stated at: $($now)"

Start-Sleep -s 2

Write-Output "Hello $($Name)!"

I can test the first function, by adding a new schedule, with the following PowerShell script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$body = @{
	"StartTime" = "2022-05-20 16:50:00";
	"Description" = "This is just a test schedule";
	"Parameters" = @{
		"Name" = "Christian"
	}
} | ConvertTo-Json

$headers = @{
    "Content-Type" = "application/json"
}

$RunbookName = "test-runbook"
$uri = "http://localhost:7071/api/" + $RunbookName


Invoke-RestMethod -Method "POST" -Uri $uri -Body $body -Headers $headers

The script should return the following output:

1
201 - Runbook schedule: automation-3d2a0864, has been registered

I can then test the second function, to see which schedules I have already created with the script below:

1
2
3
4
5
6
7
$headers = @{
    "Content-Type" = "application/json"
}

$uri = "http://localhost:7071/api/RetrieveCurrentScheduledRunbooks"

Invoke-RestMethod -Method "GET" -Uri $uri -Headers $headers

The function should return an output similar to below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
Name         : automation-64f218cc
CreationTime : 20/05/2022 16.25.35
NextRun      : 20/05/2022 16.55.00
TimeZone     : Europe/Paris
Description  : This is just a test schedule

Name         : automation-9a3de384
CreationTime : 20/05/2022 16.25.25
NextRun      : 20/05/2022 16.50.00
TimeZone     : Europe/Paris
Description  : This is just a test schedule

Name         : automation-f001ee3c
CreationTime : 20/05/2022 16.25.52
NextRun      : 20/05/2022 17.03.00
TimeZone     : Europe/Paris
Description  : This is just a test schedule

If I then check my schedules in my Azure Automation account, i can see that the schedule has been created an are ready to be executed.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/scheduling-powershell-automations.png

Adding the Azure Function to Azure API Management

Before you can utilize the Azure Function inside PowerApps, you will need to provide the function through API Management.

You do this by going into apim and then clicking on APIs. Then click on “Create from Azure resource” and choose “Function App”

Then you can import the functions you just created.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/azure-api-management-azure-function.png

Setting the correct HTTP response for the APIs

Now for the API to work with Microsoft PowerApps, it is important to set the correct response. If the request-response is not set correctly PowerApps, don’t know how to handle the API.

Start by selecting the first function “POST AzureAutomationIntegration”, then in the “Frontend section” click on the edit logo.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/editing-azure-api.png

Then go to Responses and click on “+ Add response”, and select 201 Created.

Then for the Content-Type select: application/json

for the sample you can enter the following JSON:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
  "StartTime": "2022-05-20T17:15:00+02:00",
  "ExpiryTime": "2022-05-20T17:15:00+02:00",
  "IsEnabled": true,
  "NextRun": "2022-05-20T17:15:00+02:00",
  "Interval": null,
  "Frequency": 0,
  "MonthlyScheduleOptions": null,
  "WeeklyScheduleOptions": null,
  "TimeZone": "Europe/Paris",
  "ResourceGroupName": "rg-test-automation",
  "AutomationAccountName": "aa-test-automation",
  "Name": "automation-2ab008f0",
  "CreationTime": "2022-05-20T16:53:04.447+02:00",
  "LastModifiedTime": "2022-05-20T16:53:04.447+02:00",
  "Description": "This is just a test schedule"
}

Then press Save

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/editing-azure-api-2.png

then do the same for the second function, except the response should be 200 OK, and the sample JSON should be:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
[
  {
    "Name": "automation-2ab008f0",
    "CreationTime": "2022-05-20T16:53:04.447+02:00",
    "NextRun": "2022-05-20T17:15:00+02:00",
    "TimeZone": "Europe/Paris",
    "Description": "This is just a test schedule"
  },
  {
    "Name": "automation-56dfaaa2",
    "CreationTime": "2022-05-20T16:52:00.453+02:00",
    "NextRun": "2022-05-20T17:15:00+02:00",
    "TimeZone": "Europe/Paris",
    "Description": "This is just a test schedule"
  }
]

Importing the API into PowerApps as a custom connector

To make it easy to use the API, you can import it as a custom connector inside PowerApps. This way you can define the request input and output to make it easier to use in PowerApps and Power Automate.

Start by going to https://powerapps.microsoft.com then click on “Data” –> “Custom Connectors”. Then click on “+ New custom connector” and choose “Create from Azure Services”. Then just select your subscription, service, service name, and the name of the API.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerapps-custom-connector.png

Now for configuring the custom connector you can go to section “3. Definition”. This is where you will define you actual requests used by PowerApps, for connecting to your API.

To make it easy, you can import a sample output from a request, then PowerApps will be smart enough to generate the right output.

Scroll down until you find the “Response” here click on the white space.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/custom-connector-response.png

Then click on “+ Import from sample”, here you should paste a sample output into the field. To get a sample output you can just make a request to the API, and copy-paste the output you get.

Example output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
  "StartTime": "2022-05-22T19:15:00+02:00",
  "ExpiryTime": "2022-05-22T19:15:00+02:00",
  "IsEnabled": true,
  "NextRun": "2022-05-22T19:15:00+02:00",
  "Interval": null,
  "Frequency": 0,
  "MonthlyScheduleOptions": null,
  "WeeklyScheduleOptions": null,
  "TimeZone": "Europe/Copenhagen",
  "ResourceGroupName": "rg-test-automation",
  "AutomationAccountName": "aa-test-automation",
  "Name": "automation-6ee43b79",
  "CreationTime": "2022-05-22T13:40:06.693+00:00",
  "LastModifiedTime": "2022-05-22T13:40:06.693+00:00",
  "Description": "This is just a test schedule"
}

Then click on the second API endpoint to create an output for it.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerapps-creating-a-custom-connector.png

Again scroll down to “Response” and click on the white field. Then click on “+ Import from sample”, then paste a sample of the output from that API endpoint.

Example output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[
  {
    "Name": "automation-2cb5a2e0",
    "CreationTime": "2022-05-21T07:18:05.48+00:00",
    "NextRun": "2022-05-23T19:33:00+02:00",
    "TimeZone": "Europe/Paris",
    "Description": "Test Description"
  },
  {
    "Name": "automation-68a21212",
    "CreationTime": "2022-05-21T07:20:24.247+00:00",
    "NextRun": "2022-05-23T19:33:00+02:00",
    "TimeZone": "Europe/Paris",
    "Description": "Second test description"
  },
  {
    "Name": "automation-6ee43b79",
    "CreationTime": "2022-05-22T13:40:06.693+00:00",
    "NextRun": "2022-05-22T19:15:00+02:00",
    "TimeZone": "Europe/Copenhagen",
    "Description": "This is just a test schedule"
  }
]

You then save the Custom connector by clicking on “Create Connector.” Now before you can test the connector you need to create a new connection. Click on “+ New connection”, after you clicked on “Create Connector”.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerapps-creating-a-custom-connector2.png

Here you need to paste in your API key. To get the API key, you need to head over to the Azure Portal, and into your API Management Service. In the Menu click on “Subscriptions”.

Then click on “+ Add subscription”, give it a Name and Displayname, for the scope select “API” and then select the name of your API.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/api-managemen-subscription.png

After you have created the Subscription, you can click on “Show/hide keys”. Copy the key and then paste it into the PowerApp Custom connector.

Your custom connector is now ready to be used in your PowerApp.

Creating the PowerApp

Now I won’t go in-depth on how I have created the PowerApp, this part is fairly simple and you can find a lot of resources online showcasing how to create all the logic in the app. Instead, i will just quickly walk over the logic I have used.

How does it work ?

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerapp-example.png

By pressing the button “Update Table”, the app will connect to the API and retrieve all the scheduled runbooks.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/example-powerapp2.png

And if you want to schedule a new automation, you can fill out the form and press the button “Schedule”. The app will provide part of the HTTP response from the API and show it in the Output screen.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/example-powerapp3.png

Schedule a new automation button

Since I want to retrieve output from the HTTP request I need to create the HTTP request inside a Power Automate Flow. All the flow does is it takes all the values from the form in the PowerApp, and send the data to the API.

To use the data from the PowerApp inside the Flow, I will create a Variable pr Data entry I need to use, then for the value of the variable I will select “Ask in PowerApps”.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerautomate-flow.png

Since i have create the API as a custom connector in the PowerApp, I can create a Custom Task for connecting to the API.

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/powerautomate-flow2.png

I then take the Output from the Custom Connector action and send it back to the PowerApp

/images/creating-a-powershell-automation-scheduling-app-with-azure-and-microsoft-powerapps/example-powerautomate-flow3.png

At last inside the PowerApp I will need to use the following code, for calling the Power Automate flow, and saving the response data from the flow, into a variable. The reason I am saving the data in a variable is to be able to show the data in the Outputs text box.

1
Set(myOutput, ScheduleAzureAutomation.Run(Dropdown1.SelectedText.Value,txtStartTime.Text,txtDescription.Text,txtName.Text))

Updating the data table button

To update the data table, with the button, it is as simple as using the following code on the button:

Set(myData, 'fa-test-automation'.getretrievecurrentscheduledrunbooks())

and then the data tables “Items” field should contain:

SortByColumns(myData, "NextRun")

This way the data table will show the response from the API call, and sort it by date ascending.

Conclusion

This way of using a PowerApp to schedule your automation is a very simple way of simplifying scheduling PowerShell scripts. This could enable IT, supporters, in your Team to perform actions that may otherwise require, knowledge of Servers, Azure, or PowerShell, to be able to complete these types of tasks, freeing up your time for other tasks.

This app I showcased here can be improved in many different ways. You could create a menu layout for scheduling different tasks, you could also create a button for running the Runbook instantly instead of scheduling it.

One of the problems with this app is that if you have multiple Runbooks which require different parameters, you will need to create a separate flow for the different runbooks, and I would create a sub-screen (or a popup), which contained all the input fields for the different runbook parameters.