Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

For this demonstration I’ve added a few Office 365 licenses to the list.

Automated Software License Expiration Notifications Using Microsoft Flow

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:

  1. Recurrence of the workflow (when to trigger the workflow)
  2. Get Items (Query SharePoint list)
  3. Filter Items (based on business needs, which is seven days prior the expiration date )
  4. Create an HTML output
  5. Send Email

The screenshot below is the final setup of the workflow.

Automated Software License Expiration Notifications Using Microsoft Flow

The Filter Array step, which is the third step in the screenshot above, needs be added after the Create HTML table action is in place. You will have to place the Filter Array action between the Get Items and Create HTML Table steps.During workflow setup, if you create the Filter Array before the Create HTML table action is created, for some reason Flow changes Create HTML table step to apply for each item in the Array. I did not find any documentation explaining this behavior on Microsoft Flow’s website. If you find or know the reason, please comment below.

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

Next, search for Create HTML table within the Data Operations action group.

Automated Software License Expiration Notifications Using Microsoft Flow

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).

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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(utcNow(),7, ‘yyyy-MM-dd’

Where:

  • 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.

Automated Software License Expiration Notifications Using Microsoft Flow

@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.

Automated Software License Expiration Notifications Using Microsoft Flow

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:

Automated Software License Expiration Notifications Using Microsoft Flow

Since the condition is complex, we have to switch to Advanced mode and enter the conditional expression below:

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

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

In the screenshot below, you can find steps on how to review expression visually.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Automated Software License Expiration Notifications Using Microsoft Flow

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).

Automated Software License Expiration Notifications Using Microsoft Flow

To run the Flow manually, open the Expiration Notification Flow and click on Run Now.

Automated Software License Expiration Notifications Using Microsoft Flow

After running the Expiration Notification Flow manually, let’s check the mailbox.

Automated Software License Expiration Notifications Using Microsoft Flow

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.

Photo by Sonja Langford on Unsplash

About the Author

Daler Sayfiddinov

Daler is an MCSE specialising in Office 365. He builds business applications using SharePoint, PowerApps, Flow and PowerBI. Daler is passionate about automating day to day business processes to save customers time and increase revenue (automateiq.rocks). Connect with Daler on LinkedIn.

Comments

  1. Ole

    The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@less(item()?[‘Expiration Date’], addDays(utcNow(), -17, ‘yyyy-MM-dd’))’ failed: ‘The template language function ‘less’ expects two parameter of matching types. The function was invoked with values of type ‘Null’ and ‘String’ that do not match.’.

  2. Lina

    Mine run successfully but I don’t receive any email :

  3. Lavanya Reddy

    Hi,

    i rebuilt the flow same which you have explained but i am able to get the notification only html headers like title and expiration dates there are the headings ..

    i want to notify that particular software expiry details,please help me out

  4. joshua Kurzawa

    Hi,

    I’m having trouble with the conditional part as there is no advanced option.
    Don’t suppose anyone would be able to export their working flow so that I can import it and amend from there, please?

  5. BGD

    Hello,

    I want to know how to add table borders. how we can add in a border around the table and cells

  6. Raghu

    Hello,

    I run my Flow and receive an email I can see only column names but does not contains any values.

    also In send email action, i don’t see inHTML option in advanced.

    Any ideas or help would be greatly appreciated.

  7. Shawn Fox

    Great Article! Just what I needed.

    I do need some help on an issue where I have people picker fields in the SharePoint List and I need it to send an email to the “Creator”, “Supervisor”, and “Manager, but when I add them in the “To: field” it nests them in an Apply to each for the body(‘Get_items’)?[‘value’] and sends 1 email for everything listed in the SP list. For example, if there are 10 items in the SP list, it sends 10 of the same email. How do I stop that and only send 1 email? Any help would be great. Thanks!

  8. Ali

    “We provide tourist e-visa,, we need a strong follow up to the tourist who enter the destination country with our company sponsored visa”

    we need follow up with customers or clients to inform them to left the country or renew their visa before expiry date, otherwise we will blacklist and charge the blacklist fees from them.

    Currently we are doing this all manually. for maintaining this we are spending too much time and effort.

    We are looking for an automated system to check the visa expiration date from the government website and send several follow up email automatically to the clients.

    If you have any idea please share with me..

    I will be really grateful to you

    Thanks

  9. Vandross

    I am getting an error when I try to add the calculated column.
    When I try to add the Days Left column I get this error message – “Sorry, something went wrong. The formula contains a syntax error or is not supported.”

    Here’s m formula :

    =IF((TODAY()>[Renewal or Expiry Date]),”0″,(DATEDIF(TODAY(),[Renewal or Expiry Date],”d”)))

    1. Daler

      One of your quotation mark is not right the one. Paste the formula to the notepad and make sure that all symbols are the same and then copy it back to SP

      1. Vandross

        Thank you very much. Everything is ok. You are the best.

        Cheers,

        1. Vandross

          Hello,

          1 – I wanna know how to Create HTML and formatting the table in an email.

          2 – How to make the reneal or Epiry dates column decrement every day

          1. Vandross

            How to automatically update a column with a type of calculated value in a sharepoint list

  10. Ahmed Hassan

    Very Nice..
    I have only one issue which is if the expiration date passed still the email notification includes these items.
    How I can remove them from the email since that I’m already using the output from the HTML Table.

    1. Daler

      I would just use where expiration date is equal to TODAY or you if you want to know a month prior then use where TODAY+30 days is equal Expiration date.

  11. Annie Wojcik

    Hello,
    FANTASTIC POST!!! This is exactly what I was looking to try to do for a discussion board that I created. I want to send an email that contains all the posts from the previous day.

    This is the syntax for the Filter Array
    @equals(item()?[‘Created’], addDays(utcNow(), -1, ‘yyyy-MM-ddTHH:mm:ssZ’))

    I run my Flow and receive an email but it contains no data. When I check the history for the flow it shows that it gathers all the data from the discussion board but there is nothing in the Outputs/Body.

    Any ideas or help would be greatly appreciated.

    1. Daler

      Try to use variables to check the content of the output. I am thinking something is wrong with HTML generator

  12. Faye

    Any ideas on how to due this now that you can’t use Edit in Advance Mode (sorry kind of new anything beyond the most basic Flows)?

    1. Chet

      Would love to know the same thing

  13. Nathan

    Any idea how to convert the ExpirationDate format “2018-11-15T16:00:00Z” that i get in the email to something like below.

    dd/MM/yyyy

    Thanks

  14. Graham

    This is fantastic and works great with my test data – however I’m trying to adapt it to return a greater than or equals to value. but keep getting a failed to run. In other words my scenario is..

    We have documents that after 7 years old need to be destroyed. I’m working from a date column which contains the date they were created/added to the system. The idea was that Flow would generate an email when a date reaches 7 years old BUT would continue to be included in the email if ignored, hence the greater/less than or equals to.

    Message i get is
    ‘The template language function ‘greaterOrEquals’ expects two parameter of matching types. The function was invoked with values of type ‘Null’ and ‘String’ that do not match.’.

    Have tried this with a minus symbol and and less instead of greater. Sorry I’m new to this but would appreciate any help you can give.

    Many thanks

  15. Jonathan

    Got is working. For testing the the test data has to be exactly 7 days out. Otherwise great tutorial and it’s in use right now.

  16. Kevin C.

    This is great. Thank you for sharing this. I foresee this solution working nicely for certificate expiration tracking.

    1. Daler Sayfiddinov

      Agree, you can enhance this functionality for any other business needs.
      More articles are coming…

  17. Dilshod

    Very nice article and very easy to follow.
    Thank you!

  18. Steve

    Hi Daler

    Very nice article. I was wondering what the formula is which you used to calculate the days remaining. Would you mind sharing?

    1. Daler Sayfiddinov

      “Days left” is a calculated SharePoint column.

      Formula is this:
      ***Code starts here

      =IF((TODAY()>[Expiration Date]),”0″,(DATEDIF(TODAY(),[Expiration Date],”d”)))

      ***Code ends here

      You could do that without condition (IF statement) but you will get an error when Expiration Date<Today. That is why I set a condition where if Expiration is greater than today, i will calculate the difference otherwise show zero.

      1. Keith Phillpott

        When I use the formula I get an error but it doesn’t say what it is. Can the column name have spaces in it like Expiration Date?

        Do you set Expiration Date column to Today’s Date or None?

        1. Daler Sayfiddinov

          If you are using this formula exactly how it is here, you have to make sure that all columns are named the same like in the formula.

          Yes, you can have spaces in the column name.
          Let me know if you still have troubles.

          1. Jonathan

            I am getting an error when I try to add the calculated column.
            I have triple checked the column names are exactly like your example.
            When I try to add the Days Left column I get this error message – “Sorry, something went wrong. The formula contains a syntax error or is not supported.”

            Here’s what I am using –
            Column Type
            Title Single Line of text
            Quantity Single Line of text
            Expiration Date Date and Time
            Days Left Calculated =IF((TODAY()>[Expiration Date]),”0″,(DATEDIF(TODAY(),[Expiration Date],”d”)))
            Modified Date and Time
            Created Date and Time
            Created by Person or Group
            Modified by Person or Group
            Assigned to Person or Group

  19. Michael

    Hello
    I have a message: Model validation failed: “The” Filter_array “action (s) referenced by” entries “in the” Condition “action are not defined in the model. “.
    can you help me?

    1. Daler Sayfiddinov

      Sure, I will need to know which step are you in?
      Please specify more details.

      Thank you

Leave a Reply