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 certificate 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 certificate expiration from an internal issued Certificate 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 scripts 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.

 

 

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.

 

#variables

#filter template list

$filterlist ="Copy of User","EFS"

#setup duration

$duration = 30

 

 

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

Pre-requisite:

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

Installation:

• Connect on your PKI CA server (issuing CA) using RDP or Local Logon
• Download and install the PKI PowerShell module
• Save this in a PKI scripts 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 = @'

<style>body{font-family:`"Calibri`",`"sans-serif`"; font-size: 14px;}

@font-face

       {font-family:`"Cambria Math`";

       panose-1:2 4 5 3 5 4 6 3 2 4;}

@font-face

       {font-family:Calibri;

       panose-1:2 15 5 2 2 2 4 3 2 4;}

@font-face

       {font-family:Tahoma;

       panose-1:2 11 6 4 3 5 4 4 2 4;}

       table{border: 1px solid black; border-collapse:collapse; mso-table-lspace:0pt; mso-table-rspace:0pt;}

       th{border: 1px solid black; background: #dddddd; padding: 5px; }

       td{border: 1px solid black; padding: 5px; }

       .crtsn{font-weight: bold; color: blue; }

       .crtexp{font-weight: bold; color: red; }

       .crtcn{font-weight: bold; color: orange; }

       </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 += '<html><head><meta http-equiv=Content-Type content="text/html; charset=utf-8">' + $style + '</head><body>'

$mailbody += "The certificate expiry details:<br />"

 

#collect cultureinfo for short date and time pattern

$cultureinfo = Get-Culture

$formatdata = "$($cultureinfo.DateTimeFormat.ShortDatePattern) $($cultureinfo.DateTimeFormat.ShortTimePattern)"

 

#mail body template

$mailbody += '<p>'

$mailbody += 'Hello Reader, '+"<br />"

$mailbody += 'Please find below the list of certificaes Expiring in next ' + $duration + ' days' + "</span><br />"

$mailbody += '</p>'

 

#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 += '<p><table>'

$mailbody += '<th>Request ID</th><th>Serial Number</th><th>Requester Name</th><th>Requested CN</th><th>Certificate Template</th><th>Expiration date</th>'

 

#run through each row

foreach($row in $table)

    {

        #create necessary row information

        $mailbody += "<tr><td>" + $row.'Request ID' + "</td><td>" + $row.'Serial Number' + "</td><td>" + $row.'Requester Name' + "</td><td>" + $row.'Request Common Name' + "</td><td>" + $row.'Certificate Template' + "</td><td>" + $row.'Certificate Expiration Date' + "</td></tr>"

    }

#closing html tags

$mailbody += '</table></p>'

$mailbody += '</body>'

$mailbody += '</html>'

 

#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,<br>

                            <br>

                            The certificate requested by you is about to expire :

 

                            <br>

                            Details <br>

                            -------------------- <br>

                            $row

                            <Br>

                            <br>

                            Thanks"

        #send mail

        Send-Certificatemail

    }

}

 

 

Hope this helps,

Zoheb & Eddy