This tutorial will be posted in several parts as I get time to put them together. This first post will contain the first 3 parts, which mainly consists of getting the back end of the app ready to create new users and provide them licences. I decided to split the posts due to the amount of time it took me to put this one together. I understand this initial post doesn't actually touch on power apps yet, but it will. I just wanted to ensure I covered as much bases as possible.
Bit of background info:
The app was created because I have managers at sites who request accounts/account changes all the time, but I don't want to give any of them admin centre access. The app allows me to create the requested accounts with the correct licences by simply accepting the approval email (which contains all of the right details of the account for me to check) and the log in details then subsequently emailed to the requester.
In short, this allows any user given access to the app the ability to:
- Create new office 365 users and assign licences.
And, for users they create in this app:
- Change current assigned licences, update passwords.
- Block users, convert inbox to shared inbox, remove licences.
Each action has its own approval process.
Part Zero: Prerequisites
First off, its assumed you are a global admin to set everything up in this tutorial, and that you have the relevant licences for SQL, servers etc and that you have access to azure. It might be possible to set up a non-admin account to do this, but as this was a learn-while-you-build project, I wanted as little issues to solve as possible.
Next, you will need a place to store the users you create within the app. I used an SQL database installed on a virtual machine in our azure environment, but I’m 99.9% sure this will work with an excel spreadsheet on SharePoint with a little tweaking. On the server, I have also installed the on-premise data gateway that allows me to access this SQL server in power apps and power automate.
The app currently only uses one table, which I have provided the code for below. Please feel free to make changes as you wish.
CREATE TABLE [dbo].[EmailUsers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NULL,
[Department] [nvarchar](255) NULL,
[First Name] [nvarchar](255) NULL,
[Job Title] [nvarchar](255) NULL,
[Last Name] [nvarchar](255) NULL,
[Line Manager] [nvarchar](255) NULL,
[Mobile Number] [nvarchar](255) NULL,
[Modified] [datetime] NULL,
[Office Location] [nvarchar](255) NULL,
[Created by] [nvarchar](255) NULL,
[Licence] [nvarchar](255) NULL,
[User Email] [nvarchar](255) NULL,
[Requested licence] [nvarchar](255) NULL,
CONSTRAINT [PK_EmailUsers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmailUsers] ADD CONSTRAINT [DF_EmailUsers_Created] DEFAULT (getdate()) FOR [Created]
GO
ALTER TABLE [dbo].[EmailUsers] ADD CONSTRAINT [DF_EmailUsers_Modified] DEFAULT (getdate()) FOR [Modified]
GO
When I originally built the table, it started out a lot smaller, but as I learnt how to do certain things extra columns were added which resulted eventually in column names like “licence” being used for licence approval state. This is because there is a significant delay in renaming columns and how long it takes to update in power apps when using the on-premise data gateway. It was also partially due to it starting out as more of a proof-of-concept project that ended up getting used.
The next thing you need to make sure you have is an Azure Automation account. The automation account has a feature called runbooks that will execute our power shell script for exchange online tasks, like converting an inbox to a shared one when a member of staff leaves. These runbooks can then subsequently be accessed in power automate, passing through any variable you wish to execute in any PowerShell you can think of. It’s a crazy powerful tool that I only discovered recently, and I will surely be exploring the possibilities of it in the future.
Part 1: User Groups and Licences.
Now that we have gone over the prerequisites we’re going to get started.
First off you’re going to create a user group. You will need to head to the azure portal to do this.
In the left side panel navigate to Azure Active Directory > Groups and click create group.
You can call this what you like, but in this tutorial I called mine AutomatedExchangeOnlineUsers. Set the group type to “security” and make sure the membership type is set to “assigned”. Make sure you set yourself as the owner and then click create.
Once the group is created, head back to the groups page in Azure Active Directory, search for your group. Click the groups name and make a note of its Object Id, you will need this later. On the left hand menu select licences, then add assignments.
Choose the licences you wish to automatically assign to this group. The first column is the licences, the second is the apps/licences that come with the selected licence.
Click Save.
I actually created 3 different groups for 3 different licencing options, which you will see later.
Part 2: Creating a flow to create new users and assign them to the correct group.
Head to power automate and create a new automated cloud flow.
Set the name to whatever you wish, and in the search box type SQL and then select “When an item is created (V2)” and click create.
Select your server, Database and Table name.
Below the box, click the little + sign and select “choose an action”, search for “Start and wait for an approval” and click the circled option below.
Complete this section any way you wish. All of the fields from the table can be pulled into this email. Below is what I chose.
Next, click the little + again and add a condition control
Set the value to “outcome” of the previous approval in the dynamic content option.
Type “Approve” in the value like below.
Under the If yes section, add a control again, but this time select “switch”.
This is where you will create separate paths based on how many groups you created earlier. If you set the “On” property to the “Requested licence type” column, you can create a separate path based on what is entered.
Under the first Case column, and find Azure AD Create User.
Complete the details as needed below. I used the table ID column to help the password be not easily guessable. Again, all of the fields are pulled from the dynamic content from the original table entry.
Select the + sign and search for Azure AD Add user to group.
Here, you will need to enter the object Id of one of the groups you created earlier, corresponding to the licence type you are filtering by in this path of the switch. Set the User Id to the ID created from the previous step from the dynamic content option.
Add your next action and find Office 365 outlook – Send an email (v2)
Complete the email to your liking using the dynamic content. I made sure to set the “To” field to the “Created by” column, thereby emailing the log-in info to the original requester.
By default, this email will come from yourself. You can change this by clicking the … and adding another account.
The last thing we want to do is update the original table and set the “Licence” column for the entry that triggered the flow.
Add another action below the email and search for SQL Update row (V2)
Set the server, database and table name to the correct details.
The “row Id” should be set to the id of the original table entry, and in the Licence column type “Approved”.
And that’s the main part of this flow completed. As you can see below, I created a path for each of the options I created. On a side note if you are going to have multiple paths, ensure you name each action separately. You need to add the new users ID in the “add to group” action from the flow path it was created on by selecting the right one in the dynamic content section, otherwise the flow will fail. Naming them makes it easier to identify the correct one.
As you can see, I have also added a delete row and send email action to the default option of the switch, which means any entry that doesn’t match one of the paths “Equals” fields, the row will be deleted with an error message sent to the requester saying along the lines of, the request was approved but their was a failure for some reason and the account has not been created.
Next, I set the “If no” section up from the original approval outcome. Like the default section of the switch, it deletes the entry on the table and sends an email explaining it was not approved.
Anyway, thats the end of the first post of the tutorial. Let me know what you think so far and i promise ill try to post the rest as soon as possible in this same post.
