ADCS expiring certificates email alerting script

rtushar1400 Ok... Changed the script a little bit:

- Removed the write-host lines with the certificates found that would expire, date-time issue in formatting

- Changed the amount of days to 365 for testing

- Changed "$Certexpirydate = [datetime](Get-date $importall[$i].'Certificate Expiration Date' -Format $formatdata)" to "$Certexpirydate = [datetime](Get-date $importall[$i].'Certificate Expiration Date')" . Tried different formats for $formatdata but errors.

- $Mailbody gave me this HTML output, don't have a mailserver on my test VM but it gave me the output that I wanted so I guess you just need to test mailing now

The adjusted script, just change the mail and template variables and a correct amount of days (365 now) and test 🙂 

#functions

 

Function Send-CertificateList

{

    #initialize email pre-reqs

    $FromAddress = 'Email address removed'

    $ToAddress  = 'Email address removed'

    $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 = 'Email address removed'

    $CCAddress  = 'Email address removed'

    $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","DomainController"

#setup duration

$duration = 365

#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')

     #perform comparison

     If(($Certexpirydate -gt $now) -and ($Certexpirydate -le $then))

 

        {

            #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

    }

}