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 List
Third 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
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?
Let me know whats the issue ?
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
Hi Vikas would you please help
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()
Any error ??
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
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.
Hi! I fixed it. Flow does not like an empty cell.
Any idea how to do this now that you can’t use Edit in Advanced Mode?
I am able to use edit in advanced mode, if this is disabled for you ??
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.