As IT professionals, we have a lot of tasks that we run daily. One of those tasks in my professional career is to keep track of all kinds of licenses. There are a lot of subscriptions that I have to track either yearly, quarterly or monthly, and I have to make sure that I am aware of the expiration dates.
I decided to build a system which will give me a piece of mind and keep all my licenses up to date. A searchable, centralized database to store license information, with an ability to send expiration date reports to an email.
This is not the only use case where you can use this solution. Any data that is associated with expiration dates can use the same approach.
For this automated system, I am going to use three main components:
- Backend database – SharePoint list to store license information
- Workflow engine – Microsoft Flow to send email notification based on a business rule
- Email notifications – Exchange Online as the delivery method
Building the SharePoint List
Let’s start by building a SharePoint List with the required columns. The list can live within 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 have built a very simple list based on the business requirements. 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, visit Microsoft Office support webpage.
Now we can add software licenses to the list. The screenshot below shows what the new item form looks like for adding items to the list.
For this demonstration I’ve added a few Office 365 licenses to the list.
Creating the Flow
Now let’s build the workflow that will send an email notification when licenses are near their expiration date. We are going to use Microsoft Flow as the workflow engine to run daily and check the expiration date of the licenses. Flow will send an email if there are items that are expiring in the next seven days.
We need to configure five main steps in the workflow:
- Recurrence of the workflow (when to trigger the workflow)
- Get Items (Query SharePoint list)
- Filter Items (based on business needs, which is seven days prior the expiration date )
- Create an HTML output
- Send Email
The screenshot below is the final setup of the workflow.
Let’s review each step one by one.
The Recurrence (first step) is a very simple step where I set the workflow to run daily at 06:00 AM CST. You have to specify Interval, Frequency and Time Zone.
In the next step, Get Items, we need to query data from SharePoint list by specifying the URL of the SharePoint site and the SharePoint list name where we store information about our licenses.
By running the Get Items action, we will pull all the items from the SharePoint list and store them in the Values output object. You will find the use of this object in the next step of the flow.
Next, let’s review the Create HTML table action. This action is very useful to generate table reports from SharePoint List, SQL server, Microsoft Dynamics and more.
Create HTML table is a part of the Data Operations action group. To add Create HTML table action to the flow start by adding a blank action to the flow. Then, search and click on Data Operations action group.
Next, search for Create HTML table within the Data Operations action group.
After adding Create HTML table action, we will need to link the dataset from the previous action step as a source for the table. Click on From, and then Show advanced options. From Dynamic content choose Value (which is the output from the previous step).
After the dataset is configured, we will design our HTML table by specifying fields from the SharePoint list that we would like to report on.
As I mentioned at the beginning of the blog post, we will be adding a Filter Array step between Get Items and Create HTML table actions to filter data based on our business needs.
In the From field of Filter Array action, we need to specify a collection of data to filter. We are going to use data object from the previous step that was queried in the Get Items step and stored in the Value data object.
The Value object is automatically created by Flow and available for you to use in the Dynamic Content library. Now let’s set the filtering parameter where Expiration Date must be equal to [Today() + 7 days] to meet business requirements. The left side of the filtering requirements is a SharePoint column named Expiration Date, which is available in the dynamic content. The right side of the filtering requiremensts is an Expression.
To set the expression click on the right side of the condition, switch to Dynamic Content section, click on Expression tab and type below expression.
- addDays – function to add a number of Days to a date
- utcNow() – today’s date
- ‘yyyy-MM-dd’ – comparison date format. Make sure to use capitalize “MM” for a month, since lowercase “mm” represents minutes.
You can also review your expression in the advanced mode of this step by clicking on Edit in advanced mode.
@equals(item()?['Expiration_x0020_Date'], addDays(utcNow(), 7, 'yyyy-MM-dd'))
Now, as we have our data filtered, we need to change the source of data in Create HTML table from Value object to the outcome of Filter Array object. The need of this step is to build an HTML table only with items that are expiring in seven (7) days, not the entire SharePoint list.
In the last step, we need to send an email only if the data array contains at least one license that is expiring in 7 days. To achieve this goal, I am going to check the array length.
First, add a condition and set the statement:
Since the condition is complex, we have to switch to Advanced mode and enter the conditional expression below:
In the step above, we are checking if the array contains any data. You can switch back to Basic mode and review visually what is happening in this step.
In the screenshot below, you can find steps on how to review expression visually.
If the length of the array is greater than zero, we will be sending an email with an HTML table that we built in the previous steps.
As you can see, Flow created an output object from the Create HTML table step for us to use in the email body.
Since the body of the email is an HTML table, we will need to turn on an HTML mode in the email action by navigating to Advanced mode and setting an isHTML property to Yes.
Testing the Flow
Ok, we are all done. Let’s test our solution. For that, I’m going to set up a couple of licenses in SharePoint list to expire in 7 days and run the Flow manually. At the time I am running this flow today date is March 25, 2018. We can see that two licenses are expiring in 7 days (O365 Business Premium and O365E3).
To run the Flow manually, open the Expiration Notification Flow and click on Run Now.
After running the Expiration Notification Flow manually, let’s check the mailbox.
Awesome, we can see that expiring items are reported in our email message.
I hope this solution saves you time, and helps you track your software licenses to ensure that license expirations are not missed.