Forum Discussion
rtushar1400
Mar 16, 2022Copper Contributor
ADCS expiring certificates email alerting script
Hi Can someone please help me with a script that sends out email alerts when a security certificate is about to expire in ADCS ? I found a couple on googling but none seems to work properly. The...
- Mar 21, 2022
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 } }
Mar 23, 2022
No problem, glad to help!
long_lanh
Jul 13, 2022Copper Contributor
- Jul 13, 2022Not sure, there's only one line in the script with this cmdlet and it's "foreach ($OID in (get-catemplate).Oid)". Looks like it can't find the CA ? You're on a machine that is connected / can reach the CA?