Pages

Saturday, April 29, 2023

Configuring reminders for "Send approval email" action in Logic Apps

A few of my contacts reached out to me with questions after my previous post:

Using Microsoft Forms and Logic App to create an automated submissions and approval process for Azure AD User Creation
http://terenceluk.blogspot.com/2023/04/using-microsoft-forms-and-logic-app-to.html

… where I demonstrated a workflow for using Microsoft Forms to generate an approval email and create an Azure AD account. One of the challenges they faced was that the approval emails can potentially be missed by the approver, which would leave the workflow continuously running. The ask was whether there would be a way to send a reminder to the approver after a duration. The features available for the Logic App action Send approval email:

image

… does not provide a built-in way to resend the approval email or reminder so the workaround is to place a parallel branch to continuously loop for a duration and send a reminder if the approval process has not completed and this post serves to demonstrated how this can be done.

Additional Steps

The additional steps we would add into the workflow I demonstrated in my other post are the following:

  1. Initialize a variable to store the approval response as being true or false
  2. A Until Control that serves as a loop until a condition is met
  3. Set the initialized variable that stores the approval response to true when the approval email has been responded to

The following is the workflow with the additional steps.

image

Step #1 - Initialize a variable to store the approval response as being true or false

Begin by adding an action to initialize a variable with the following configuration:

Name: ApprovalResponse
Type: Boolean
Value: false

The purpose of this step is to create a variable with the value set as false to determine whether the approval process has completed. Given that this is the start of the workflow and the approval email hasn’t even been created and sent, the value is set to false.

image

Step #2 – A Until Control that serves as a loop until a condition is met

Add a parallel branch beside the Send approval email action:

image

Add an Until Control Action:

image

The Until control will serve to continuously loop until the ApprovalResponse variable is set to true. This variable will continue to be false until the other parallel branch has continued with a step that will set the variable to true.

Configure this parallel branch as follows:

1. Place the ApprovalResponse variable as the condition to check whether it is equal to true

image

2. Add a Delay action with the desired wait time between reminder email should be sent out

image

3. Add Condition Control to check whether the ApprovalResponse variable is still false after the delay

image

image

4. Send a reminder email out

Here is full branch configuration:

image

Step #3 – Set the initialized variable that stores the approval response to true when the approval email has been responded to

The next action is to add a Set variable action post Send approval email action to set the ApprovalResponse to true that will stop the parallel branch until loop:

image

This step will stop the while loop from another iteration now that the condition to stop the loop is true.

Step #4 – Add Terminate Action to stop the loop from continuing to run until the 2hr is up

The last step is to add an action to the end of each successful branch to terminate the job rather than have it continue running until the 2hr loop is up. Forgetting to add this step will display a 2-hour runtime for each trigger of this Logic App, which while would still work, does not provide true runtime duration for troubleshooting.

Proceed to add the Terminate action to each successful branch end:

image 

Additional Question

Using multiple Logic App expressions together

Another question I received was whether it was possible to combine multiple Logic App expressions together. One scenario that could cause this workflow to fail is if the requestor of the form accidentally places a space at the beginning or end of the new user’s first or last name because that would cause the User Principal Name to contain a space and leading to the Azure AD Create user to fail. To do this, simply add the desired expression in front of the existing expression for the string.

In the example below, the trim and toLower function is used together:

image

The code would look as such:

"userPrincipalName": "@{trim(toLower(body('Get_response_details')?['r9b9abc58c2fb468181b916528b9b97ad']))}.@{trim(toLower(body('Get_response_details')?['r17ea325b22b044c2ba26346a3e98c787']))}@contoso.onmicrosoft.com"

image

I hope this helps anyone who may be looking for answers to the above two questions.

Tuesday, April 18, 2023

Automating the creation of Azure Calculator estimates with Selenium and Python

One of the tedious tasks I have to perform in my role is to create estimates on the Azure Calculator and I dread the evenings when I start the process of sizing multiple environments that contain 100 or more virtual machines. Those who have used the Azure Calculator will know that each component needs to be added independently, parameters configured as desired, then another component gets added to start the process again. There isn’t a way to display the resources in an Excel spreadsheet-like format because resources have different parameters. What this means is that we would try to use shortcuts where if we are creating multiple VMs, we would duplicate the object and update the settings. I recently had to update the region for 12 estimates that contained 80 virtual machines and it took quite a bit of time with a substantial amount of clicking because there isn’t a way to update the configuration.

Microsoft must have received feedback about this so they have provided another approach for estimates via the Azure Retail Prices REST API (https://learn.microsoft.com/en-us/rest/api/cost-management/retail-prices/azure-retail-prices) but this doesn’t generate an estimate on the Azure Calculator portal tool where we can send to others for review or update (I did a bit of research a while back and couldn’t figure out a way but feel free to comment if I am incorrect).

So after going through the 12 estimates and hundreds of virtual machines exercise over a weekend, I decided to look at whether I could automate the process of configuring the Azure Calculator portal (https://azure.microsoft.com/en-ca/pricing/calculator/) but programmatically. A bit of research led me to Selenium WebDriver and Python. Selenium WebDriver is a web framework that allows testers to execute activities performed in a browser for testing web applications. Paired with the ChromeDriver, it can automate actions within Google Chrome with Java or Python. My Java programming days date back to University and I’ve always wanted to spend some time to learn Python so I was determined to spend my weekend writing a Python script that will:

  1. Open an Excel spreadsheet with virtual machines and configuration
  2. Open the Chrome browser
  3. Navigate to the Azure Calculator
  4. Read each row in the Excel spreadsheet and add virtual machines into the Azure Calculator

This post describes the setup for those who aren’t familiar with setting up Selenium WebDriver for Chrome, installing Python, and writing the script that will execute this task. Version 1 of the code can be found at my GitHub repo here: https://github.com/terenceluk/Azure/blob/main/Azure%20Calculator/Azure-Calculator-Estimate-Generator.py

This is my first pass at creating this automation and there are many other improvements (e.g. setting license type, setting the Managed Disks Redundancy, error handling, etc.) I would like to add but I hope to be able to share this out now in case I do not get back to it in the following weeks or months.

Step #1 – Setting up the Desktop

Begin by downloading Python and installing it onto your desktop: https://www.python.org/downloads/

image

I use Visual Studio Code for all my development work so I proceeded to install the Python extension:

image

Proceed to open the command prompt and run the following Python command to install the Selenium package:

pip install -U selenium

image

Once completed, you can use the following command to verify the installation of Selenium:

python -c "import selenium; print(selenium.__version__)"

image

Next, we’ll need to install the Python library openpyxl, which is what we’ll be using to open the Excel spreadsheet containing the virtual machines to be added to the Azure Calculator estimate:

python -m pip install openpyxl

image

As with checking the installed Python version, the following command can do the same for openpyxl:

python -c "import openpyxl; print(openpyxl.__version__)"

image

The following folder can be found on your Windows desktop upon successfully installing the openpyxl library:

C:\Program Files\Python311\Lib\site-packages

image

Next, we’ll need to download the Chrome Driver in which Selenium WebDriver will use. Navigate to the URL https://chromedriver.storage.googleapis.com/index.html and download the version of the driver that matches the major version of the Google Chrome that will be used:

image

You can check the version of Google Chrome via the Help > About Google Chrome:

image

Or navigate to: chrome://version/

image

We’ll be using 112.0.5615.49 of the Chrome Driver as it is the closest match to the 112.0.5615.87 Google Chrome I have installed:

image

The zipped package should contain the chromedriver.exe:

image

Unpack to desired location:

image

Step #2 – Grabbing the Python Script

From here, launch Visual Studio code and paste in the Version 1 of the code can be found at my GitHub repo here: https://github.com/terenceluk/Azure/blob/main/Azure%20Calculator/Azure-Calculator-Estimate-Generator.py

Update the constants defined at the beginning of the script:

image

Step #3 – Setting up the Excel file with Virtual Machines

Then create a spreadsheet with the following columns:

  • Virtual Machine Name
  • Region
  • Operating System
  • Type
  • Tier
  • Instance Size
  • Quantity
  • Hours
  • computeBillingOption
  • Managed Disk Tier
  • Disk Size
  • Disks QTY

Note that the column heading names do not matter so you can name it as anything you like.

image

Step #4 – Executing the Python Script

You should now be able to run the python script with:

python azure-Calculator.py

image

What I’ve learned through writing this script is that any subtle changes to the Azure Calculator webpage or browser version can break the script and maintenance on this script will be required whenever such changes happen. I learned this after upgrading my Chrome browser from 112.0.5615.87 to 112.0.5615.121 on the following Monday when a duplicate of the add virtual machine button appeared in the code causing my code to no longer be able to select the button. To get around this, I located the search field, sent the text Virtual Machines that eliminated the duplicate, then proceeded to add the new VM object. It is difficult to strike a good balance between being too generic or too precise when located the buttons, fields, dropdowns so be prepared to see failures and update the code as required.

I also chose to stop the driver from closing the browser so I can add more items that are not virtual machines, share the quote via a URL, or export the excel. This in turn does not terminate the python script upon closing the browser so use CTRL+C to terminate it. As mentioned earlier, there are plenty of improvements that can be made so please feel free to fork the repo and update/improve the code.

Hope this helps anyone who may be looking for a way to automate the process of creating the estimate.

Monday, April 3, 2023

Using Microsoft Forms and Logic App to create an automated submissions and approval process for Azure AD User Creation

Being an architect usually means that I do not usually take part in ongoing operational activities when an environment has gone live and therefore the days where I handle requests for new Azure AD account creations are supposed to be behind me but most of my fellow colleagues may know that this isn’t usually the case as there are plenty of scenarios where we still need to roll up our sleeves and perform activities such as provision accounts. Account provisioning should be managed by a proper ticketing system, but it may not be available for new greenfield environment builds that has not been transitioned to operations. What I’ve found over the years is that large projects that last upwards of a year or more can have a large volume of new account requests and a large set of unidentified accounts when it is ready to be transitioned from the project to sustainment team.

One of the ways I’ve tried to better manage, and track Azure AD accounts is through the use of Microsoft Forms and a Logic App that automates the process of creating user accounts through an approval process. Microsoft Forms provides an easy way to create a secure form for requestors to fill out and track the submissions, while Logic App allows me to create a flow that sends approval to a group of administrators, automates the creation of accounts, and sends notifications. I’ve found that this temporary solution has served very well for situations where I do not have a ticketing system and I only have access to Microsoft Azure and 365 licenses.

This blog post serves to provide steps for how to create the form and the automation, and I hope it will help others who might need an interim solution for account creation and management.

Create an Automation Account Runbook to Generate a Random Password

While it is possible to have the Azure AD step generate a password when creating an account, most of the environments I’ve worked in has had more stringent requirements so we will need to create an Automation Account Runbook to randomly generate a password to be used when creating the Azure AD account. I won’t be providing too prescriptive steps for creating the Automation Account Runbook but would like to provide enough information for its creation.

Create an Automation Account or reuse one that is already available. For this example, I am reusing an Automation Account to host the runbook that will generate a random password and return it back to, in our case, a Logic App:

image

The runbook code that generates the password can be found at my following GitHub repository: https://github.com/terenceluk/Azure/blob/main/PowerShell/Generate-Random-Password.ps1

image

To lock down the Automation Account so that it can only be access by the Logic App, we’ll turn on the System assigned identity in the Identity blade, which will allow us to grant permissions to the Logic App:

image

Creating the Microsoft Form

There are various ways to create a Microsoft Form such as navigating to https://forms.office.com or directly in Teams. For this example, I’ll be creating the form directly in a Teams team that I created for IT Requests as shown in the screenshot below.

image

Begin by clicking on the + sign, search for Forms, and then click on the Forms icon:

image

Provide a name for the form and click the Save button:

image

The new form will be displayed in the Teams tab. Proceed to edit the for and add the attributes you would like to collect. The form I created consists of the following fields:

  • Authorizing Manager – For placing an entry into Microsoft Form’s record as to who authorized this account creation
  • Requestor - For placing an entry into Microsoft Form’s record as to who requested this account creation (the submitter may not be the requestor)
  • First Name – Will be used for the Display Name and Given Name in the Create user azure ad action.
  • Last Name – Will be used for the Display Name and Surname Name in the Create user azure ad action.
  • Location – Will be used for the Office Location in the Create user azure ad action.
  • Job Title – Will be used for the Job Title in the Create user azure ad action.
  • Department – Will be used for the Department in the Create user azure ad action.
  • Company Name – For placing an entry into Microsoft Form’s record
  • Office 365 License – For determining whether this user requires an Office 365 license
  • Power BI License – For determining whether this user requires an Power BI license
  • DevOps Access – For determining whether to grant this user to DevOps
  • Date Required – For determining when this user account is required
  • Start Date – For determining when this user starts
  • End Date – For determining when this user account should expire
  • New User Contact Email – For determining what email address should be used to contact this user
  • New User Contact Number – For determining what phone number should be used to contact this user
  • Requestor's Email Address (approved and denied notifications) – For determining email address the new account details such as login name and temporary password should be sent to
image

You can preview the form by clicking on the Preview button:

image

With the form created, proceed to close the form in editing mode by click on the Edit | <Form name> and click on the Remove option:

image

The message can be a bit misleading but the reason why we’re removing it is because it is currently in edit mode:

image

With the form removed, proceed to click on the + sign in the tab again:

image

Click on the form icon again:

image

We will now use the Add an existing form option to add the form we had just created:

image

The form will now be in Fill mode (non editing):

image

The form can be accessed via the Teams channel or directly through a link (both require authentication by default). To obtain the link, simply navigate to https://forms.office.com and sign-in to locate the form:

image

Open the form and click on Collect Responses:

image

You can configure the permissions for accessing the form as well as shortening the URL:

image

Obtain the Form ID

Our Logic App will be triggered when a form has been submitted and the way it identifies the form is through its From ID. To find this unique string, navigate to www.office.com, click on the top left corner tile button and then Forms:

image

The most recent for we created will be displayed but if the recent list contains other documents you have worked on that has pushed the form off the list then we can navigate to it by clicking on the team under My Groups:

image

Proceed to open the form by clicking on it then with the form displayed in the browser, copy the URL and locate the string after the id= string and copy it:

https://forms.office.com/pages/designpagev2.aspx?origin=OfficeDotCom&lang=en-US&route=GroupForms&subpage=design&id=C0f0hB4_iUiflasPUUMCT_1a5E1qzx9IgJMuQa0Fbb9UME5RSlhYWFNLODdDQ1lVUzhINlRVRzBNNiQlQCN0PWcu

image

We’ll be using this form ID shortly so save it somewhere that can easily retrieved.

Logic App Flow

Before I begin, I would like to go through the flow of the submission, approval, processing, and notification flow of the Logic App as shown in the following screenshot.

image

The flow of the Logic App goes as such:

Approval Process

  1. The Logic App starts when a response is submitted through the Microsoft Forms
  2. The next step is to retrieve the values of each parameter in the submitted response so we can use it in the later actions
  3. The Initialize Variable is used for generating HTML code that will be used to generate an email as Send an email (V2) no longer accepts HTML code
  4. An approval email is sent to the desired approvers

Approved Process

If the one of approvers has approved the new account request then:

  1. An automated account runbook that generates a random password will be started to generate a random password
  2. When the password is generated, the password will be retrieved
  3. An Azure AD account will be created with the form submission values and the password generated

If Account Creation is Successful

If the Azure AD account creation is successful then:

  1. Set the initialized variable at the beginning of the workflow to contain the contents of the email to be sent out
  2. Send the email with the accounts detail to:
    1. The person who authenticated and submitted the form
    2. The email address provided in the field: Requestor's Email Address (approved and denied notifications)
    3. The infrastructure team

If Account Creation is Unsuccessful

If the Azure AD account creation is unsuccessful then:

  1. Set the initialized variable at the beginning of the workflow to contain the contents of the email to be sent out
  2. Send a friendly email with the accounts detail to the person who authenticated and submitted the form and the email address provided in the field: Requestor's Email Address (approved and denied notifications)
  3. Send a failure email with the accounts detail to the the infrastructure team
  4. Terminate the job and set the status as failed so the logic app run will be label as failed rather than success so it is easier to troubleshoot

Denied Process

If the one of approvers has rejected the new account request then:

  1. Set the initialized variable at the beginning of the workflow to contain the contents of the email to be sent out
  2. Send a friendly email with the accounts detail to the person who authenticated and submitted the form and the email address provided in the field: Requestor's Email Address (approved and denied notifications)

Create the Logic App

Now that we gone through the flow of the Logic App at a high level and have the form to collect the user input created, proceed to create a new Logic App:

image

With the Logic App created, proceed to navigate to the Logic app designer:

image

Scroll down to the Templates section and create a Blank Logic App:

image

A new blank template will be discovered for steps to be configured:

image

Type in Microsoft Forms in the search field and click on When a new response is submitted:

image

image

Sign into the tenant to create a connection to Microsoft Forms:

image

Select Enter custom Value for the Form Id:

image

Paste in the form ID we copied previously:

C0f0hB4_iUiflasPUUMCT_1a5E1qzx9IgJMuQa0Fbb9UME5RSlhYWFNLODdDQ1lVUzhINlRVRzBNNiQlQCN0PWcu

Proceed to create a new step, type in Get response details Microsoft forms in the search field and select the action:

image

image

Paste in the previously copied form Id into the Form Id field and select List of response notifications Response ID for the Response ID field:

image

The next step is to create an Initialize variable step to create a variable that will store our HTML code so we can use it to generate a properly formatted email out to notify the recipient about the status of the account creation. Add a new step, search for Initialize variable and select it under Actions:

image

Provide the following for the variable:

Name: NotificationEmailBody
Type: String
Value: <Blank>

image

Proceed to create a new step, type in Send approval email in the search field and select the action:

image

Fill in email address for approval.

One of the differences between Send approval email and Send an email (V2) is that the Body will treat the content as HTML code so paste in the code that will generate an email with HTML content:

image

The email template I used for this notification was taken from Sandro Pereira’s blog post here: https://blog.sandro-pereira.com/2020/01/26/logic-apps-how-to-send-a-well-formatted-html-email-notification-with-office-365-outlook-connector/

I made some changes to it as I only needed the table for the report. The modified HTML code can be found at my following GitHub repo: https://github.com/terenceluk/Microsoft-365/blob/main/HTML/ApprovalEmail.html

Note that you will need to update the body with the parameters submitted in the form by using the Dynamic content:

image

The following is a sample of the approval email that will be sent to the approvers:

image

With the approval email step completed, we will now create a condition that will determine whether the request has been approved or rejected. Proceed to create a new step, select Built-in and type in condition in the search field and select the action:

image

On the Condition box, click inside the Choose a value box.

image

From the dynamic content list that appears, under Send approval email, select the SelectedOption property.

image

In the middle comparison box, select the is equal to operator.

On the condition's right side, in the Choose a value box, enter the text, Approve.

When you're done, the condition looks like this example:

image

With the condition configured, we’ll be configuring the flow where the account creation is approved so proceed to navigate to the True block, add an action, search for Create job, and select Create Job Automation Account:

image

Fill in the required fields:

Subscription: <Select the subscription containing the Automation Account>
Resource Group: <Manually enter Resource Group containing Automation Account>
Automation Account: <Select the Automation Account containing the Runbook>
Runbook name: <Select the Runbook with the PowerShell script that generates the password>
Wait for Job: Yes
Managed identity: System-assigned managed identity

image

At this point, we’ll need to configure the required permissions so that the Logic App requires to authenticate to execute the Automation Runbook. Open another browser tab so you do not lose the Logic App step configuration, navigate to the Identity blade and turn on System-assigned:

image

Next, navigate to the Automation Account, open the Access control (IAM) blade, click on Role assignments, click Add, Add role assignment:

image

Search for the Automation Operator role:

image

Select the Members tab, click on Managed identity and select the Logic App for the Azure AD user creation:

image

Successfully assigning the permissions will display the following in the IAM blade:

image

With the permissions assigned to the Logic App so it can successfully execute the runbook, we can now add a step to retrieve the generated password from the runbook. Proceed to create an action, search for Get job output and select Get job output Azure Automation:

image

Fill in the required fields:

Subscription: <Select the subscription containing the Automation Account>
Resource Group: <Manually enter Resource Group containing Automation Account>
Automation Account: <Select the Automation Account containing the Runbook>
Job ID: <Use the Dynamic content feature to add the Job ID>

image

Create a new step, type in Create user azure ad in the search field and select the action:

image

Sign in to create the connection to Azure AD, grant the permissions request for the Logic App:

image

The available attributes are available:

  • Account Enabled
  • Display Name
  • Mail Nickname
  • Password
  • User Principal Name
  • Given Name
  • Surname
image

The fields from the form should be available as dynamic content to use:

image

A few customizations that will need to be added to the fields are as follow:

Mail Nickname: To maintain consistency, I prefer to have this value all in lower case

Password: This is provided by the previously executed runbook but I’ve noticed that the output adds \r\n to the password which would cause this step to fail so we need to remove the undesired characters

User Principal Name: As with the Mail Nickname, I prefer to have this value all in lower case

With the above identified, we’ll be using expression functions (https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference) to adjust the values for these parameters. Switch over to the Code view for the Logic App:

image

Edit the lines as highlighted in the screenshot below:

image

Mail Nickname:
"mailNickname": "@{toLower(body('Get_response_details')?['r9b9abc58c2fb468181b916528b9b97ad'])}.@{toLower(body('Get_response_details')?['r17ea325b22b044c2ba26346a3e98c787'])}",

Password:
"password": "@{replace(body('Get_job_output'),'\r\n','')}"

User Principal Name:
"userPrincipalName": "@{toLower(body('Get_response_details')?['r9b9abc58c2fb468181b916528b9b97ad'])}.@{toLower(body('Get_response_details')?['r17ea325b22b044c2ba26346a3e98c787'])}@contoso.onmicrosoft.com"

With the adjustments made, this is what the step would look like:

image

The next step will now fork out to two different paths as the Create user step can succeed or fail and we want to handle both. To create these two different paths, add two Set variable steps and configure one to should run after as being is successful and the other as has timed out and has failed:

image

When the step succeeds, we want to set the variable that we defined earlier with the body of the email we’ll send out to confirm the account has created and when it fails, repeat the same but with different content.

image

The success email will include a username, the temporary password, and with a table containing the form submission data:

image

The next step will use the Send an email from a shared mailbox (V2) to send a confirmation to the desired recipients:

image

You may notice that the variable for the failed user creation is a bit different than the success as it does not include the welcome message and I intentionally did this because we will be sending two different emails (one user friendly and one for IT) where the first part of the email is different:

image

With the body configured, proceed to create the email notifications to be sent out to the user and the IT team:

image

The last step is to add a Terminate action to set the status as failed because we this is not done then the Logic App run history will state the process as being successful:

image

The last part of the Logic App is to handle the process when the approval is rejected. Proceed to use the Set variable action to create the body of the email:

image

Lastly, create an action to send a denied notification email to the desired recipients:

image

This has been a very long post but I hope this can provide start of how you can set up an automated process with approval to create Azure AD accounts. I can think of many other improvements I’ll be adding in for the future and a few of them include:

  1. Using Microsoft Purview Message Encryption to encrypt the email with the password by using a keyword trigger
  2. Using PowerShell cmdlets to set attributes not configuration with the Create AD User step
  3. Adding user to a group that assigns licenses

Hope this has been helpful.