Automated license expiration notifications using Microsoft Flow

Automated Software License Expiration Notifications Using Microsoft Flow

As IT professionals, we have a lot of tasks to complete on a daily basis. In my professional career, one of these tasks is keeping track of all kinds of licenses. For example, I have to keep track of a large number of annual, quarterly, or monthly subscriptions. I also have to make sure I’m aware of expiration dates.

With this in mind, I decided to implement a system that will allow me to keep all my licenses up to date. It’s a centralized, searchable database for storing license information. It also has the ability to send expiration date reports via email.

This isn’t the only use case where you can use this solution. Any data associated with expiration dates can use the same approach.

For this automated system, I will use three main components:

Backend Database – SharePoint list to store license information.
Workflow Engine – Microsoft Flow to send email notifications based on a business rule.
Email Notifications – Exchange Online as Delivery Method

Creating the SharePoint list

Let’s start by creating a SharePoint list with the required columns. This list can be embedded in one of your existing SharePoint sites, or even a team site. An Office 365 group is an easy way to create a team site for this purpose.

For this example, I built a very simple list based on the business needs. The columns and column types are shown in the screenshot below.

You can add additional columns based on your business needs. For more details on SharePoint columns and options, see the Microsoft Office Support webpage.

We can now add software licenses to the list. The screenshot below shows what the new form for adding items to the list looks like.

For this demonstration, I added some Office 365 licenses to the list.

Create the flow

Now let’s build the workflow that will send an email notification when the license expiration date is approaching. To do this, we’ll use Microsoft Flow as the workflow engine to check the license expiration date daily. This is why Flow will send an email if any items expire within the next seven days.

We need to configure five main steps in the workflow:

Workflow recurrence (when to trigger the workflow)
Get Items (Query SharePoint List)
Filter items (based on business needs, i.e. seven days before expiration date)
Create HTML output
Send an email

The screenshot below represents the final workflow configuration.

The Filter Array step must be added after the Create HTML table action is in place. You will need to place the Filter Array action between the Get Items and Create HTML Table steps. During setup, if you create the Filter Array before the Create HTML table action, Flow modifies the Create HTML table step. So that it applies to every element in the Array. I could not find any documentation explaining this behavior on the Microsoft Flow website. If you find or know the reason, please comment below.

Let’s go through each step one by one.

Recurrence

Recurrence is a very simple step. In this step, I set the workflow to run every day at 6:00 AM CST. You need to specify the interval, frequency, and time zone.

Get items

In the next step, Get Items, we need to query the data from the SharePoint list by specifying:

the URL of the SharePoint site
the name of the SharePoint list.

By running the Get Items action, we’ll retrieve all the items from the SharePoint list. We’ll then store them in the Values output object. You’ll find the usage of this object in the next step of the flow.

Next, let’s review the Create HTML Table action. This action is very useful for generating tabular reports from:

SharePoint List, SQL server,
Microsoft Dynamics and more.

Create HTML table is part of the Data Operations action group. To add the Create HTML table action to the flow, start by adding a blank action to the flow. Then, locate and click the Data Operations action group.

Next, find Create HTML Table in the Data Operations action group.

After adding the Create HTML Table action, we’ll need to link the dataset from the previous step as the source for the table. Click From, then Show Advanced Options. Under Dynamic Content, choose Value (which is the output from the previous step).

From there, we build our HTML table by specifying the fields we want to report on.

As mentioned at the beginning of the article, we will add a Filter Array step between the Get Items and Create HTML table actions. This allows us to filter the data according to our needs.

Filter items

In the From field of the Filter Array action, we need to specify a collection of data to filter. We’ll use the data object from the previous step, queried in the Get Items step and stored in the Value data object.

The Value object is automatically created by Flow and you can use it in the Dynamic Content library. First, let’s define the filtering parameter where the expiration date must be equal to [Today() + 7 days] to meet the business requirements. The left side of the filtering requirements is a SharePoint column named Expiration Date, available in the dynamic content. The right side of the filtering requirements is an expression.

To define the expression, click on the right side of the condition. Go to the Dynamic Content section, click the Expression tab, and enter the expression below.

addDays(utcNow(),7, ‘yyyy-MM-dd’

Or:

addDays – function to add a number of days to a date
utcNow() – today’s date
‘yyyy-MM-dd’ – Date comparison format. Be sure to use the uppercase “MM” for a month, as the lowercase “mm” represents minutes.

You can also review your expression in the advanced mode of this step by clicking Edit in advanced mode.

@equals(item()?['Expiration_x0020_Date'], addDays(utcNow(), 7, 'yyyy-MM-dd'))

Create HTML output

Now we need to change the data source in the HTML Create array from the Value object to the result of the Filter Array object. The need for this step is to build an HTML array with only the items that expire in seven (7) days, and not the entire SharePoint list.

Send an email

In the last step, we need to send an email only if the data table contains a license that expires in 7 days. To do this, I will check the length of the table.

First, add a condition and define the statement:

Because the condition is complex, we need to switch to advanced mode and enter the conditional expression below:

@greater(length(outputs('Filter_array').body), 0)

In the step above, we check if the table contains data. You can switch back to Basic mode and visually review what is happening in this step.

In the screenshot below you will find the steps to visually revise the expression.

If the array length is greater than zero, we will send an email with an HTML table that we built in the previous steps.

Flow created an output object from the Create HTML Table step. This allows it to be used in the body of the email.

Since the email body is an HTML table, we need to enable HTML mode in the email action. To do this, we go to Advanced mode and set the isHTML property to Yes.

Test the flow

Okay, we’ve done everything. Let’s test our solution. To do this, I’ll configure some licenses in the SharePoint list so that they expire in 7 days. Then, let’s run the flow manually. We can see that two licenses expire in 7 days (O365 Business Premium and O365E3).