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