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

 

Advertisements

10 thoughts on “Microsoft Flow to Monitor the items based on Date

      • 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

      • 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()

      • 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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s