SOLVED
@Harm_Veenstra Hi, as you said here that script might not be formatted correctly, reading which I performed below mentioned steps:
- This time I copied the script from the blog rather downloading the attachment from it like I did earlier (assuming there could be some formatting difference
) - I executed it on my ADCS server adding all the required details. Also, this time in the template name filter, I added the actual template name and not its display name (also suggested by the author of the script in the comments there). On running, script executed successfully but nothing happened - no output, no email message, no error either
- Then I thought of executing it using a scheduled task (as scheduler only will run it daily) using "SYSTEM" named service account with "higher privileges". Ran the task manually first time, but the task is just running and nothing's happening again :\
Please suggest what else can be done here as now the errors are gone too ? I am also attaching the raw script here (without any parameters/settings) to show formatting like the way I am now using on my server. ~ Thanks!
@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
}
}
1 best response
Accepted Solutions
@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
}
}