Wednesday, January 31, 2018

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 comments:

  1. 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?

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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()

    ReplyDelete
  5. 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

    ReplyDelete
  6. As per error you meed to load the assembly first, do a google search on how to load oracle libs..

    ReplyDelete
  7. […] Go to Article Posted: 14th November 2018 12:05 pm Author: Vikas Sukhija […]

    ReplyDelete
  8. 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.

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

    ReplyDelete
  10. Any idea how to do this now that you can't use Edit in Advanced Mode?

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

    ReplyDelete
  12. 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. ¯\_(?)_/¯

    ReplyDelete
  13. 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.

    ReplyDelete