Microsoft Flow to Monitor the items based on Date

There is a requirement to Monitor certificates expiry date, as these are used in different tools or hardware appliances and are difficult to track.

We came up with an idea of SharePoint List and Microsoft flow to do this Job. (Earlier we were performing the same with Powershell).

But with SharePoint list we created it as a form so that any one in IT can use it to monitor their set of certificates.

Create a SharePoint List with below columns:

Cert Name (Single line text type)
Expiry (Date type)
AlertEmail (Single line text type but add validation so that only email address can be added)
Type (Choice menu type –> Values can be Self signed, Internal CA, External CA)
Where Used (Single line text field)

Ones this is created it will look like below form:

Now we will ADD flow on the top of this list.

First Step is create a schedule for running the flow, for example every week on Sunday at 10:00 AM.

Second step is to get items from SharePoint ListThird step is apply to each action (This is logically similar to for each loop) and adding condition to compare expiry date with current date , If 60 days are left than alert is generated.

You can see the Send Email activity under If Yes condition.

Main thing here is how to compare the date, It has taken me some time & after going thru some documentation /research I figured it out.

Go to Edit in Advanced Mode.and add the below formula, change the days as per you desire.

@lessOrEquals(items(‘Apply_to_each’)?[‘Expiry’], adddays(utcnow(), 60))

Here is the complete flow that will look like, that runs every week and will send send alert if 60 days are left for item to get expired.

After you have renewed the certificate juts update the list item with new expiry date, weekly alert email will auto stop generating.

Let me know if you have any queries on creating it, I am sure this simple TIP will assist many system administrators to create their custom alerts for other things as well.

I just shared what we are using it for.

Thanks for Reading

Sukhija Vikas

http://SysCloudPro.com

16 thoughts on “Microsoft Flow to Monitor the items based on Date”

Hi VIkas… Need your help on one bat file script, want to execute one Procedure in oracle using bat file.
Would you please help on that?

Vikas Sukhija says:

February 4, 2018 at 3:48 am

Let me know whats the issue ?

Reply
- keshavkalra says:
  
  February 7, 2018 at 11:01 am
  
  Hi Vikas… I am using a batch file to call a procedure, but getting error:
  ORA-12560: TNS=Protocol Adapter Error.
  I have checked DB is up & running fine. Below is batch file I am using, would you please help on this.
  @echo off
  
  set user_name=DSAGILE
  
  set password=mpno2015#02zwcr
  
  set net_SID=FRDSTT1D
  
  (
  
  echo @ProcedureToExecute.sql
  
  echo exit
  
  ) | sqlplus -s %user_name%/%password%@%net_SID%
  
  pause
- keshavkalra says:
  
  February 12, 2018 at 2:38 pm
  
  Hi Vikas would you please help
- keshavkalra says:
  
  February 12, 2018 at 2:41 pm
  
  I have made below PS script but not sucessfull.
  
  $connectionString =“User Id=DSAGILE;Password=mpno2015#02zwcr;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=vdcmld00333.ics.cloud.ge.com)(PORT=1525)))(CONNECT_DATA=(SID=FRDSTT1D)))”;
  $oracleConnection = new-object System.Data.OracleClient.OracleConnection($connectionString);
  $ProcName = “GSC_KPI_FILL”;
  $oracleConnection.Open();
  $cmd = new-object System.Data.OracleClient.OracleCommand;
  $cmd.Connection = $oracleConnection;
  $cmd.CommandText = $ProcName;
  $cmd.CommandType = [System.Data.CommandType]::StoredProcedure;
  $cmd.ExecuteNonQuery();
  $oracleConnection.Close()
- Vikas Sukhija says:
  
  February 12, 2018 at 2:44 pm
  
  Any error ??
- keshavkalra says:
  
  February 12, 2018 at 3:05 pm
  
  yes…
  
  New-Object : Cannot find type [System.Data.OracleClient.OracleConnection]: make sure the assembly containing this type
  is loaded.
  At D:\Users\703198536\desktop\test.ps1:2 char:31
  + $oracleConnection = new-object <<<< System.Data.OracleClient.OracleConnection($connectionString);
  + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
  + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
  
  You cannot call a method on a null-valued expression.
  At D:\Users\703198536\desktop\test.ps1:4 char:23
  + $oracleConnection.Open <<<< ();
  + CategoryInfo : InvalidOperation: (Open:String) [], RuntimeException
  + FullyQualifiedErrorId : InvokeMethodOnNull
  
  New-Object : Cannot find type [System.Data.OracleClient.OracleCommand]: make sure the assembly containing this type is
  loaded.
  At D:\Users\703198536\desktop\test.ps1:5 char:19
  + $cmd = new-object <<<< System.Data.OracleClient.OracleCommand;
  + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
  + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
  
  Property 'Connection' cannot be found on this object; make sure it exists and is settable.
  At D:\Users\703198536\desktop\test.ps1:6 char:6
  + $cmd. <<<< Connection = $oracleConnection;
  + CategoryInfo : InvalidOperation: (Connection:String) [], RuntimeException
  + FullyQualifiedErrorId : PropertyNotFound
  
  Property 'CommandText' cannot be found on this object; make sure it exists and is settable.
  At D:\Users\703198536\desktop\test.ps1:7 char:6
  + $cmd. <<<< CommandText = $ProcName;
  + CategoryInfo : InvalidOperation: (CommandText:String) [], RuntimeException
  + FullyQualifiedErrorId : PropertyNotFound
  
  Property 'CommandType' cannot be found on this object; make sure it exists and is settable.
  At D:\Users\703198536\desktop\test.ps1:8 char:6
  + $cmd. <<<< CommandType = [System.Data.CommandType]::StoredProcedure;
  + CategoryInfo : InvalidOperation: (CommandType:String) [], RuntimeException
  + FullyQualifiedErrorId : PropertyNotFound
  
  You cannot call a method on a null-valued expression.
  At D:\Users\703198536\desktop\test.ps1:9 char:21
  + $cmd.ExecuteNonQuery <<<< ();
  + CategoryInfo : InvalidOperation: (ExecuteNonQuery:String) [], RuntimeException
  + FullyQualifiedErrorId : InvokeMethodOnNull
  
  You cannot call a method on a null-valued expression.
  At D:\Users\703198536\desktop\test.ps1:10 char:24
  + $oracleConnection.Close <<<< ()
  + CategoryInfo : InvalidOperation: (Close:String) [], RuntimeException
  + FullyQualifiedErrorId : InvokeMethodOnNull
- Vikas Sukhija says:
  
  February 12, 2018 at 3:14 pm
  
  As per error you meed to load the assembly first, do a google search on how to load oracle libs..

see below link, it has some interesting answers for the error you are receiving, I am not expert at oracle..
https://stackoverflow.com/questions/6894558/ora-12560-tnsprotocol-adaptor-error

Pingback: Microsoft Flow to Monitor the items based on Date | Cloud Computers Guide

Hi, I tried to replicate this but I get an error on the list items that are not expiring. It’s causing the flow to fail. Any idea how I can get flow to skip those? The error = ActionFailed. An action failed. No dependent actions succeeded.

Frank says:

November 30, 2018 at 10:12 am

Hi! I fixed it. Flow does not like an empty cell.

Reply

Any idea how to do this now that you can’t use Edit in Advanced Mode?

Vikas Sukhija says:

March 1, 2019 at 4:10 am

I am able to use edit in advanced mode, if this is disabled for you ??

Reply
- fayework says:
  
  March 1, 2019 at 12:49 pm
  
  It wasn’t there the 15 gazillion times I checked earlier this week, but when I looked to see if I could get a screen shot, sure enough, that option was there this time. ¯\_(?)_/¯

Sorry, I was looking in the wrong block. In the Condition block, I do not have the option to edit in Advanced mode, it is not there. I have the option for Add dynamic content, but not for Edit in advance mode.