Further to this...
Whilst I'm investigating the Powershell route, there was an urgency to have our expiry dates monitored. To provide this assurance I have implemented a rudimentary way of monitoring these dates. We hold all our expiry dates in a spreadsheet and I currently have logmon reading the "date" column. This date is then ran through a LUA script to ensure that the date is no more, or no less than 10 days away from today.
This way, whenever the spreadsheet is updated and a date in the date column is 10 days away from today - exactly we get alerted.
Here's the script:
a = alarm.get()
message = "Certificate Expiry Alert - Issue with data"
SUPPKEY = "Certificate_Expiry"
SUBSYS = "1.1"
SOURCE = "192.168.***.*"
-- Settings
local allowed_age = 20160 -- In minutes
-- Input line (for testing only)
--local inputstr = "DOMAIN\\USERNAME,Web Server (WebServer),13/01/2017 09:13,13/01/2019,COMPANY_NAME,HOSTNAME_FQDN,SITE"
-- Separate line into 7 variables by token ","
local path, server, time, date, company_name, hostname, site = string.match(a.message, "([^,]+),([^,]+),([^,]+),([^,]+),([^,]+),([^,]+),([^,]+)")
-- Note, some additional checks could be here (eg. regex to match DD/MM/YYYY format)
--if date == nil then
--nimbus.alarm (1, message , SUPPKEY , SUBSYS , SOURCE)
--print("Error reading line: "..a.message)
--end
-- Get current time minus 9000 minutes (in format DD/MM/YYYY)
local target_date = os.date("%d/%m/%Y", os.time() + allowed_age * 60)
-- Printing what we got (for testing)
print("Target date: "..target_date..", Input date: "..date)
-- Testing the match
if target_date == date then
nimbus.alarm (2, a.message , SUPPKEY , SUBSYS , SOURCE)
-- print("Dates are matched!")
else
print("Dates are not matched!")
end
This would need amending to suit your particular needs, the above script takes the entire line from the spreadsheet and obviously vary for others with different needs.
There's also room to add extra error checking such as a date been input as an invalid format.
You could also get it to alert every day, once the threshold has been breached. In our case we only want one alert - but if you think there's a potential for this to be missed you could easily edit the script to realert on a daily basis until the Cert is renewed.
Hope that helps someone out there!