Script to send Email alerts on Expiring certificates for Important Certificate Templates

Hi all! Zoheb Shaikh here again, and this time I will be sharing an interesting script to alert on Expiring certificates.

A special thank you goes out to Eddy Ng Seng Eu for help in development of this Script. Eddy Ng is a PowerShell champion based out of Malaysia whom I always reach out to when I need help.

As a part of Mission Critical team, we always go above and beyond to help our SMC customers. SMC is part of Microsoft's family of Premier Support offerings which delivers personalized support coverage through designated support professionals who understand a customer's unique solution configuration and deployment environment, facilitating faster response time and more effective problem resolution. To know more about SMC, reach out to your Microsoft Technical Account Manager.

Coming back to the purpose of this post I want to share something interesting that I came across recently where one of our SMC customers had an important internal Expired and no one had a clue until the users started shouting that application is no longer working.

So the application stopped working because of expiration from an internal issued Authority, had there been a mechanism to alert on Certificate expiration this could have been avoided, my customer was looking for a quick fix around this which would have below capabilities :-

  1. We discussed on enabling Certificate expiry notification for certificates expiring in the next 30 Days.
  2. These notifications need to be sent over email to the certificate Owner and a common DL
  3. Owners of the certificate to be Emailed if Email Address attribute is published
  4. Expiry notifications needs to be sent only for specific/Important templates because there are millions of certificates issued and 100s expiring every day

We had above things to be considered in preparing something as a quick fix to the problem they experienced and there is a plan to make this solution better with time (I will share this in time to come).

There were a couple of we saw on gallery.technet which helped us get closer to the below script.

https://gallery.technet.microsoft.com/scriptcenter/Certificate-expiry-Alert-2f63c2d5

https://gallery.technet.microsoft.com/scriptcenter/Monitor-certificate-9d7a2141

With the assistance of Eddy Ng, the script has been modified to produce an output like below in the email.

image001.jpg

Below is filter applied in the Script to choose only the important Certificate Templates you want to be alerted and If needed you could also modify the duration for Certificate expiry from 30 days to a duration of your choice.

Please find the script below in text and as attachment also at the end of the blog.

#variables

#filter template list
$filterlist ="Copy of User","EFS"

#setup duration
$duration = 30

Pre-requisite:

  • Public Key Infrastructure PowerShell module
  • Email server connectivity
  • Tested with 2016

Installation:

  • Connect on your PKI CA server (issuing CA) using or Local Logon
  • Download and install the PKI PowerShell module
  • Save this in a PKI folder

Create a script file with the following source code:

<#Sample scripts provided are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.#>
#functions
Function Send-CertificateList
{
    #initialize email pre-reqs
    $FromAddress = 'emailaddress@domainname.com'
    $ToAddress = 'emailaddress@domainname.com'
    $MessageSubject = "Certificate expiration reminder from $env:COMPUTERNAME.$env:USERDNSDOMAIN"
    $SendingServer = 'smtp.office365.com'
    $SmtpServerPort = "Port Number"
    #Test if SMTP server is responding
    if(Test-Connection -Cn $SendingServer -BufferSize 16 -Count 1 -ea 0 -quiet){
            #Send email
            Send-MailMessage -From $FromAddress -To $ToAddress -Subject $MessageSubject -Body $mailbody -BodyAsHtml -SmtpServer $SendingServer -Port $SmtpServerPort
    }else{
            #Error Handling
            write-host -object 'No connection to SMTP server. Failed to send email!'
    }
}
Function Send-Certificatemail
{
    #initialize email pre-reqs
    $FromAddress = 'emailaddress@domainname.com'
    $CCAddress = 'emailaddress@domainname.com'
    $MessageSubject = "Certificate expiration reminder from $env:COMPUTERNAME.$env:USERDNSDOMAIN"
    $SendingServer = 'smtp.office365.com'
    $SmtpServerPort = "Port Number"
    #Test if SMTP server is responding
    if(Test-Connection -Cn $SendingServer -BufferSize 16 -Count 1 -ea 0 -quiet){
            #Send email
            Send-MailMessage -From $FromAddress -To $ToAddress -Cc $CCAddress -Subject $MessageSubject -Body $Emailbody -BodyAsHtml -SmtpServer $SendingServer -Port $SmtpServerPort
    }else{
            #Error Handling
            write-host -object 'No connection to SMTP server. Failed to send email!'
    }
}
# --------------------------------------------------
#HTML Style
$style = @'

'@
# --------------------------------------------------
#variables
#filter template list
$filterlist ="Copy of User","EFS"
#setup duration
$duration = 30
#setup strDate with yyyyMMdd-HHmmss
$strDate = get-date -format yyyyMMdd-HHmmss
#create unique export file name
$exportFileName = "certificates_" + $strDate + ".csv"
#date of Now
$now = (Get-Date)
#date of Then
$Then = (Get-Date).AddDays($duration)
#empty mailbody
$mailbody = ""
#empty array initialization
$table = @()
# --------------------------------------------------
#variables
#export certificates to CSV
certutil.exe -view csv > $exportFileName
#Import certificate info where Serial Number is not "empty" with various properties
$importall = Import-Csv $exportFileName | Where-Object {$_.'Serial Number' -notcontains 'EMPTY'} | Select-Object -Property 'Request ID','Serial Number','Requester Name','Certificate Expiration Date','Certificate Template','Request Common Name','Request Disposition' -ErrorAction SilentlyContinue
#Run through each ObjectID to get the Certificate Template Name
foreach ($OID in (get-catemplate).Oid)
{
    #populate the field "Certificate Template"
    $importall | where-object "certificate template" -match $OID | foreach-object {
        #ensure whitespaces removed
        $_.'Certificate Template' = ($_.'Certificate Template').replace($OID+" ","")
    }
}
#filter only required certificates based on $filterlist
$importall = $importall | where-object "certificate template" -in $filterlist
#build email body
$mailbody += '' + $style + ''
$mailbody += "The certificate expiry details:
" #collect cultureinfo for short date and time pattern $cultureinfo = Get-Culture $formatdata = "$($cultureinfo.DateTimeFormat.ShortDatePattern) $($cultureinfo.DateTimeFormat.ShortTimePattern)" #mail body template $mailbody += '

' $mailbody += 'Hello Reader, '+"
" $mailbody += 'Please find below the list of certificaes Expiring in next ' + $duration + ' days' + "
" $mailbody += '

' #cycle through array and search for matching cetificates for($i=0;$i -lt $importall.Count;$i++) {      #for each object, get the "certificate expirate date" and convert to [datetime]      $Certexpirydate = [datetime](Get-date $importall[$i].'Certificate Expiration Date' -Format $formatdata)      #perform comparison      If(($Certexpirydate -gt $now) -and ($Certexpirydate -le $then))         {             #write to console             write-host -object 'Certificate ID:' $importall[$i].'Request ID' 'with Serial Number:' $importall[$i].'Serial Number' 'will expire in ' -NoNewline; write-host -object ([datetime]($importall[$i].'Certificate Expiration Date') - (get-date)) ' Days!'-ForegroundColor Red             write-host -object 'This certificate has DN: ' -NoNewline; write-host -object $importall[$i].'Request Distinguished Name' -ForegroundColor DarkYellow             write-host -object 'Please don`t forget to renew this certificate before expiration date: ' -NoNewline; write-host -object $importall[$i].'Certificate Expiration Date' -ForegroundColor Red "`n"            #save info in table array             $table += $importall[$i] | Sort-Object 'Certificate Expiration Date' | Select-Object -Property 'Request ID','Serial Number','Requester Name','Certificate Template','Certificate Expiration Date','Request Common Name','Issued Email Address'         } } #mailbody html formatting $mailbody += '

' $mailbody += '' #run through each row foreach($row in $table)     {         #create necessary row information         $mailbody += ""     } #closing html tags $mailbody += '
Request IDSerial NumberRequester NameRequested CNCertificate TemplateExpiration date
" + $row.'Request ID' + "" + $row.'Serial Number' + "" + $row.'Requester Name' + "" + $row.'Request Common Name' + "" + $row.'Certificate Template' + "" + $row.'Certificate Expiration Date' + "
' $mailbody += '' $mailbody += '' #if there are matching certificates found send email if($table.Count -gt '0')     {         #send email         Send-CertificateList     } #run through each row foreach($row in $table) {     #if email address exist     if($($row.'Issued Email Address') -like "*@*")     {         #populate to address and email body         $ToAddress = $row.'Issued Email Address'         $emailbody = "Hello Reader,
                           
                            The certificate requested by you is about to expire :                            
                            Details
                            --------------------
                            $row                             
                           
                            Thanks"         #send mail         Send-Certificatemail     } }

Hope this helps,

Zoheb & Eddy

 

This article was originally published by Microsoft's Data Center Security Blog. You can find the original article here.