Powershell Script To Alert URL Cert Expiration
"####################################################################################################################################################
################################################ Powershell Script To Alert URL Cert Expiration ###################################################
#####################################################################################################################################################
# #
################################################################ Created By #######################################################################
##################################################### Sivasobh Parannattil ##############################################################
#####################################################################################################################################################
# #
############################################################################################################################
######### Pre Requirements ###########
############################################################################################################################
######### * Powershell Version 3 and above
######### * Microsoft office 2010
######### * .net framework 4
######### * Connection to SMTP server on port 25
######### * Whitlisting of source IP and from Email Address on SMTP Server
######### * Data Spreadsheet containing HTTPS URLs,Email Addresses and AppGroup as three columns. Row 1 being headings
#############################################################################################################################
# #"
#Inbuilt Variable Initiation
#ErrorActionpreference is an inbuilt variable which actions on error. Default is 2 or "Continue"
$ErrorActionPreference = "SilentlyContinue"
#[System.Net.ServicePointManager]::DefaultConnectionLimit = 1024
#Global Variables Initiation
#$MailTo="sivasobh.parannattil@nasdaq.com"
$MailTo="ALLWebPlatformSupport-AMG@nasdaqomx.com"
$ccmail="ALLWebPlatformSupport-AMG@nasdaqomx.com"
$ccmail2="John.Xanthopoulos@nasdaq.com"
$MailFrom="CertRequest@nasdaq.com"
$SmtpServer="10.112.22.88"
$smtpport="25"
#Variables below for reading data in spreadsheet and threashold value for days to expire for alerting
$timeoutMilliseconds = 20000
$Threshold = 90
$Exceptionarray=@(“ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexoperationxhasxtimedxoutx”,"ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexunderlyingxconnectionxwasxclosedxxAnxunexpectedxerrorxoccurredxonxaxsendxx","ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxUnablextoxconnectxtoxthexremotexserverx")
#commenting below 3 exception variables for using above exception array
<#$Exceptionarray1 =“ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexoperationxhasxtimedxoutx”
$Exceptionarray2 ="ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexunderlyingxconnectionxwasxclosedxxAnxunexpectedxerrorxoccurredxonxaxsendxx"
$Exceptionarray3 ="ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxUnablextoxconnectxtoxthexremotexserverx"#>
#Start of Script
################
#Section 1:Reading Data Input Excel File
#****************************************
#Below Line will get all open Excel process ids to $existExcelproc
$existExcelproc=Get-Process -name "*Excel*" | % { $_.Id }
#Creating a new Excel Object and Assigning to Variable.Making Object non visible. Assigning URL Data Excel file(URL,DL,AppGroup column) path to variable
$Excelobj=New-Object -ComObject "Excel.Application"
$Excelobj.Visible=$false
$Sourcefile = "C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\URLLlist.xlsx"
#Getting all Excel process IDs which will also include the new object initiated into a variable $curexcelproc
$curexcelProc = Get-Process -name "*Excel*" | % { $_.Id }
#Get The Process ID of new Excel Object by Comparing the Prvious opened Excel ids and current opened excel ids.
$postExcelProcesses = Compare-Object $curexcelProc $existExcelproc | ForEach-Object { $_.InputObject }
#Assigning Excel Workbook(Excel file) to Variable $workbook and Active Worksheet or a Named Worksheet to Variable $Worksheet
$workbook=$Excelobj.Workbooks.Open($Sourcefile)
$worksheet=$workbook.Worksheets.Item('URLs')
#Counting the maximum rows of $worksheet
$rowMax = ($worksheet.UsedRange.Rows).count
#Section 1: End
#***************
#Section 2: Creating 2 new Excel files to be used as reporting files[Output Files]
#Adding new Second workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook2
#This Workbook will be used to store and be used as a Report of shortly expiring Certificate data of URLs
$workbook2 = $Excelobj.Workbooks.Add()
$objExcel.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet2= $workbook2.Worksheets.Item(1)
$Worksheet2.Name = 'Expire Data'
#Keying in Headings into First row of sheet
$Worksheet2.Cells.Item(1,1) = "Application URL"
$Worksheet2.Cells.Item(1,2) = "Email Address"
$Worksheet2.Cells.Item(1,3) = "Product Category"
$Worksheet2.Cells.Item(1,4) = "Expiry Date"
$Worksheet2.Cells.Item(1,5) = "Expire In(Days)"
$Worksheet2.Cells.Item(1,6) = "SerialNumber"
$Worksheet2.Cells.Item(1,7) = "Common Name"
$Worksheet2.Cells.Item(1,8) = "Issuier"
$Worksheet2.Cells.Item(1,9) = "Subject Alternative Names"
#Creating a counter variable 'j' used while inputing data to $worksheet2 which is part of second new Workbook
$j=1
#Adding new Third workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook3
#This Workbook will be used to store and be used as a Report of Remaining non-expiring Certificate data of URLs
$workbook3 = $Excelobj.Workbooks.Add()
$Excelobj.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet3= $workbook3.Worksheets.Item(1)
$Worksheet3.Name = 'Not Expiring'
#Keying in Headings into First row of sheet
$Worksheet3.Cells.Item(1,1) = "Domain Name"
$Worksheet3.Cells.Item(1,2) = "Email Address"
$Worksheet3.Cells.Item(1,3) = "Product Category"
$Worksheet3.Cells.Item(1,4) = "Expiry Date"
$Worksheet3.Cells.Item(1,5) = "Expire In(Days)"
$Worksheet3.Cells.Item(1,6) = "SerialNumber"
$Worksheet3.Cells.Item(1,7) = "Common Name"
$Worksheet3.Cells.Item(1,8) = "Issuier"
$Worksheet3.Cells.Item(1,9) = "Subject Alternative Names"
#Creating a counter variable 'k' used while inputing data to $worksheet3 which is part of Third new Workbook
$k=1
#Adding new fourth workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook4
#This Workbook will be used to store and be used as a Report of Remaining Exception data of URLs
$workbook4 = $Excelobj.Workbooks.Add()
$Excelobj.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet4= $workbook4.Worksheets.Item(1)
$Worksheet4.Name = 'Exception'
#Keying in Headings into First row of sheet
$Worksheet4.Cells.Item(1,1) = "Domain Name"
$Worksheet4.Cells.Item(1,2) = "Email Address"
$Worksheet4.Cells.Item(1,3) = "Product Category"
$Worksheet4.Cells.Item(1,4) = "Exception"
#Creating a counter variable 'l' used while inputing data to $worksheet3 which is part of Third new Workbook
$l=1
#Section 2: End
#***************
#Main Script:
#Reads data files and validates
#Declare the starting positions of Rows and Columns of the input data files
$rowURLName,$colURLName = 1,1
$rowEmail,$colEmail = 1,2
$rowApplication,$colApplication = 1,3
$rowExpirydate,$colExpirydate = 1,4
$rowExpirein,$colExpirein = 1,5
$rowSiNo,$colSiNo = 1,6
$rowCN,$ColCN = 1,7
$rowCA,$ColCA = 1,8
$rowAltname,$colAltname = 1,9
$rowexception,$colException=1,4
#Row Number 1 contains headings in the input file.
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
#Loop for reading the records
For ($i=1;$i -le $rowMax-1; $i++)
{
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
#As row number 1 is heading, we add 1 to the row number as i has been set to 1 in the loop. Else we should start the loop with i=2
# Assigning data to variables
$URL = $Worksheet.Cells.Item($rowURLName+$i,$colURLName).text
$arranyaddress = $worksheet.Cells.Item($rowEmail+$i,$colEmail).text
$Email=@($arranyaddress.Split(",").Trim())
$Application = $worksheet.Cells.Item($rowApplication+$i,$colApplication).text
#Connecting to URLs and Getting certificate details
$req = [Net.HttpWebRequest]::Create($URL)
#Start-Sleep -m 6000
$req.Timeout = $timeoutMilliseconds
try {$req.GetResponse() |Out-Null} catch {Write-Host Exception while checking URL $URL`: $_ -f Red}
#####################################################################################
$Exception=$null
try {$req.GetResponse() |Out-Null} catch {$Exception = $_}
#Replacing special characters in the exception with 'x'
$Exception=$Exception -replace '[^\p{L}\p{Nd}]',"x"
#Contition to check each exceptions matching with Array of exceptions($ExceptionArray)
if($Exceptionarray -contains $Exception)
{
#Displaying data on screen as white for days to expire not less than threshold
Write-Host "URL: $URL`n" -ForegroundColor DarkGreen
#Write-Host "Cert CName: $certName`n" -ForegroundColor DarkGreen
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor DarkGreen
#Write-Host "`nStatus: good" -ForegroundColor DarkGreen
#Write-Host "CertExpiration: $expiration`n" -ForegroundColor DarkGreen
#Writing into the rows and columns of Spreadsheet3(Report of non-expiring certificates)
$worksheet4.Cells.Item($rowexception+$l,$colException) = $Exception
#Keying in Additional data
$l=[int]$l+1
$Worksheet4.Cells.Item($l,1) = $URL
$Worksheet4.Cells.Item($l,2) = $arranyaddress
$Worksheet4.Cells.Item($l,3) = $Application
$Worksheet4.cells.Item($l,4) = $Exception
$Exception=$null
}
else
{
####################################################################################
[datetime]$expiration = $req.ServicePoint.Certificate.GetExpirationDateString()
[int]$certExpiresIn = ($expiration - $(get-date)).Days
[string]$certName = $req.ServicePoint.Certificate.GetName()
############################# Checking the Alternative names ################################################
#downloading the Certificate to location C:\testcert
$certDirectory = "C:\testcert\"
$cert = $req.ServicePoint.Certificate
$bytes = $cert.Export([Security.Cryptography.X509Certificates.X509ContentType]::Cert)
set-content -value $bytes -encoding byte -path "C:\testcert\certicate.cer"
$i
#Gettiing the Alternative name from the downloaded certificate
$certList = Get-ChildItem $certDirectory
foreach ($certificate in $certList)
{
$certificate = new-object security.cryptography.x509certificates.x509certificate2 C:\testcert\$certificate
$sanExt=$certificate.Extensions | Where-Object {$_.Oid.FriendlyName -match "subject alternative name"}
$sanObjs = new-object -ComObject X509Enrollment.CX509ExtensionAlternativeNames
$altNamesStr=[System.Convert]::ToBase64String($sanExt.RawData)
$sanObjs.InitializeDecode(1, $altNamesStr)
[String]$subalternames=Foreach ($SAN in $sanObjs.AlternativeNames) {$SAN.strValue +';'}
#This is to add a semicollen in between alternative names
#$subalternames= $subalternames+';'
#Counting the Alternativenames slots
$slots=$subalternames.Count
}
#removing the current certificate located in C:\testcert folder
Remove-Item -Path "C:\testcert\*" -Force
###############################################################################################################
#Getting only value after 'CN=' from the string variable $certName
$CN=($certName-split'CN=')[1]
$certPublicKeyString = $req.ServicePoint.Certificate.GetPublicKeyString()
$certSerialNumber = $req.ServicePoint.Certificate.GetSerialNumberString()
$certThumbprint = $req.ServicePoint.Certificate.GetCertHashString()
[datetime]$certEffectiveDate = $req.ServicePoint.Certificate.GetEffectiveDateString()
[string]$certIssuer = $req.ServicePoint.Certificate.GetIssuerName()
#Getting only value after 'CN=' from the string variable $certIssuer
$CA=($certIssuer-split'CN=')[1]
# Email Subject and HTML Emailbody.
$MailSubject="Website Certificate Monitoring Alert: SSL cert currently deployed on $URL will expire in $certExpiresIn days"
$MailBody=@"
<html><span style='font-family: Tahoma; font-size: 12px;' >Hi,<br />
<br />
The SSL certificate currently installed on website "$URL" will expire in $certExpiresIn days. You should consider updating with renewed certificate.<br />
<br /><B>Note:</b> Please respond to this Email if you are not the owner for this certificate or if there is any ownership change.<br />
<br />
----------------------------------------------------------------------------</span><br />
<span style='font-family: Tahoma; font-size: 10px;' >This is an automatically generated email.<br /> <br /></span></html>
"@
#End Of Email Body
#If condition to check if days to expire is less than the threashold set ($threashold in the begining of the script)
if ($certExpiresIn -lt $Threshold)
{
#Displaying data on screen as yellow for days to expire less than threashold
Write-Host "URL: $URL`n" -ForegroundColor Yellow
#Write-Host "Cert CName: $certName`n" -ForegroundColor Yellow
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor Yellow
#Write-Host "`nStatus: Warning (Expires in $certExpiresIn days)" -ForegroundColor Yellow
Write-Host "CertExpiration: $expiration`n" -ForegroundColor Yellow
#Send email to corresponding Email addresses for the expiring certs sites
#Send-MailMessage -To $Email -Subject $MailSubject -From $MailFrom -Cc $ccmail -SmtpServer $SmtpServer -Port $smtpport -Priority High -BodyAsHtml $MailBody
#Writing into the rows and columns of Spreadsheet2(Report of expiring certificates)
$worksheet2.Cells.Item($rowExpirydate+$j,$colExpirydate) =$expiration
$worksheet2.Cells.Item($rowExpirein+$j,$colExpirein) = $certExpiresIn
$worksheet2.Cells.Item($rowSiNo+$j,$colSiNo) = $certSerialNumber
$worksheet2.Cells.Item($rowCN+$j,$ColCN) = $CN
$Worksheet2.Cells.Item($rowCA+$j,$ColCA) = $CA
$Worksheet2.Cells.Item($rowAltname+$j,$colAltname) = $subalternames
#Keying in Additional data
$j=[int]$j+1
$Worksheet2.Cells.Item($j,1) = $URL
$Worksheet2.Cells.Item($j,2) = $arranyaddress
$Worksheet2.Cells.Item($j,3) = $Application
$Worksheet2.cells.Item($j,4) = $expiration
$Worksheet2.cells.Item($j,5) = $certExpiresIn
$Worksheet2.cells.Item($j,6) = $certSerialNumber
$Worksheet2.cells.Item($j,7) = $CN
$Worksheet2.cells.Item($j,8) = $CA
$Worksheet2.cells.Item($j,9) = $subalternames
rv subalternames
rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
}
else
{
#Displaying data on screen as Green for days to expire not less than threshold
Write-Host "URL: $URL`n" -ForegroundColor DarkGreen
#Write-Host "Cert CName: $certName`n" -ForegroundColor DarkGreen
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor DarkGreen
#Write-Host "`nStatus: good" -ForegroundColor DarkGreen
Write-Host "CertExpiration: $expiration`n" -ForegroundColor DarkGreen
#Writing into the rows and columns of Spreadsheet3(Report of non-expiring certificates)
$worksheet3.Cells.Item($rowExpirydate+$k,$colExpirydate) =$expiration
$worksheet3.Cells.Item($rowExpirein+$k,$colExpirein) = $certExpiresIn
$worksheet3.Cells.Item($rowSiNo+$k,$colSiNo) = $certSerialNumber
$worksheet3.Cells.Item($rowCN+$k,$ColCN) = $CN
$Worksheet3.Cells.Item($rowCA+$k,$ColCA) = $CA
$Worksheet3.Cells.Item($rowAltname+$k,$colAltname) = $subalternames
#Keying in Additional data
$k=[int]$k+1
$Worksheet3.Cells.Item($k,1) = $URL
$Worksheet3.Cells.Item($k,2) = $arranyaddress
$Worksheet3.Cells.Item($k,3) = $Application
$Worksheet3.cells.Item($k,4) = $expiration
$Worksheet3.cells.Item($k,5) = $certExpiresIn
$Worksheet3.cells.Item($k,6) = $certSerialNumber
$Worksheet3.cells.Item($k,7) = $CN
$Worksheet3.cells.Item($k,8) = $CA
$Worksheet3.cells.Item($k,9) = $subalternames
rv subalternames
rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
}
#End of IF-ELSE-Loop
#Removing variables for re-use in for loop
<#rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
#rv subalternames#>
}
}
#End of FOR-LOOP
#Auto sizing newly created excel sheets with its content
$objRange1 = $Worksheet2.UsedRange
[void] $objRange1.EntireColumn.Autofit()
$objRange2 = $Worksheet3.UsedRange
[void] $objRange2.EntireColumn.Autofit()
$objRange2 = $Worksheet4.UsedRange
[void] $objRange2.EntireColumn.Autofit()
#Saving the $Workbook2 and $workbook3 data to a file in the given Location with filename concatinated with current date
$date=(Get-Date).ToString('MM_dd_yyyy_hh_mm_ss')
$workBook2.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Exp_$date.xlsx")
$file1 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Exp_$date.xlsx"
$workBook2.Close()
$workBook3.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_OK_$date.xlsx")
$file2 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_OK_$date.xlsx"
$workBook3.Close()
$workBook4.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Error_$date.xlsx")
$file3 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Error_$date.xlsx"
$workBook4.Close()
#close of data workbook
$workbook.close()
#$Excelobj.Quit()
#Below Comented line will close all excel hence commenting for now.
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
#Stop-Process -Name EXCEL -Force
# otherwise allow stop-process to clean up
#Closing Excel Object invoked
$postExcelProcesses |%{ Stop-Process -Id $_ }
#Attaching and Emailing report files.
#if you have outlook you can create an outlook object else we can use sendmail
#$attachment = new-object System.Net.Mail.Attachment $file1,$file2
#Send Email to Cert DL with Script created Excels
$MailSubject2="SSL certificate Validation files"
$MailBody2=@"
<html><span style='font-family: Tahoma; font-size: 12px;' >Hi,<br />
<br />
Please find the attached SSL certificate Validation files.<br />
<br />
----------------------------------------------------------------------------</span><br />
<span style='font-family: Tahoma; font-size: 10px;' >This is an automatically generated email, please do not reply.<br /> <br /></span></html>
"@
#Send-MailMessage -To $MailTo -Subject $MailSubject2 -From $MailFrom -Cc $ccmail2 -SmtpServer $SmtpServer -Port $smtpport -Priority High -BodyAsHtml $MailBody2 -Attachments $file1,$file2,$file3
#############################################################################################
#End of Code
#############################################################################################
"####################################################################################################################################################
################################################ Powershell Script To Alert URL Cert Expiration ###################################################
#####################################################################################################################################################
# #
################################################################ Created By #######################################################################
##################################################### Sivasobh Parannattil ##############################################################
#####################################################################################################################################################
# #
############################################################################################################################
######### Pre Requirements ###########
############################################################################################################################
######### * Powershell Version 3 and above
######### * Microsoft office 2010
######### * .net framework 4
######### * Connection to SMTP server on port 25
######### * Whitlisting of source IP and from Email Address on SMTP Server
######### * Data Spreadsheet containing HTTPS URLs,Email Addresses and AppGroup as three columns. Row 1 being headings
#############################################################################################################################
# #"
#Inbuilt Variable Initiation
#ErrorActionpreference is an inbuilt variable which actions on error. Default is 2 or "Continue"
$ErrorActionPreference = "SilentlyContinue"
#[System.Net.ServicePointManager]::DefaultConnectionLimit = 1024
#Global Variables Initiation
#$MailTo="sivasobh.parannattil@nasdaq.com"
$MailTo="ALLWebPlatformSupport-AMG@nasdaqomx.com"
$ccmail="ALLWebPlatformSupport-AMG@nasdaqomx.com"
$ccmail2="John.Xanthopoulos@nasdaq.com"
$MailFrom="CertRequest@nasdaq.com"
$SmtpServer="10.112.22.88"
$smtpport="25"
#Variables below for reading data in spreadsheet and threashold value for days to expire for alerting
$timeoutMilliseconds = 20000
$Threshold = 90
$Exceptionarray=@(“ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexoperationxhasxtimedxoutx”,"ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexunderlyingxconnectionxwasxclosedxxAnxunexpectedxerrorxoccurredxonxaxsendxx","ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxUnablextoxconnectxtoxthexremotexserverx")
#commenting below 3 exception variables for using above exception array
<#$Exceptionarray1 =“ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexoperationxhasxtimedxoutx”
$Exceptionarray2 ="ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxThexunderlyingxconnectionxwasxclosedxxAnxunexpectedxerrorxoccurredxonxaxsendxx"
$Exceptionarray3 ="ExceptionxcallingxxGetResponsexxwithxx0xxargumentxsxxxxUnablextoxconnectxtoxthexremotexserverx"#>
#Start of Script
################
#Section 1:Reading Data Input Excel File
#****************************************
#Below Line will get all open Excel process ids to $existExcelproc
$existExcelproc=Get-Process -name "*Excel*" | % { $_.Id }
#Creating a new Excel Object and Assigning to Variable.Making Object non visible. Assigning URL Data Excel file(URL,DL,AppGroup column) path to variable
$Excelobj=New-Object -ComObject "Excel.Application"
$Excelobj.Visible=$false
$Sourcefile = "C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\URLLlist.xlsx"
#Getting all Excel process IDs which will also include the new object initiated into a variable $curexcelproc
$curexcelProc = Get-Process -name "*Excel*" | % { $_.Id }
#Get The Process ID of new Excel Object by Comparing the Prvious opened Excel ids and current opened excel ids.
$postExcelProcesses = Compare-Object $curexcelProc $existExcelproc | ForEach-Object { $_.InputObject }
#Assigning Excel Workbook(Excel file) to Variable $workbook and Active Worksheet or a Named Worksheet to Variable $Worksheet
$workbook=$Excelobj.Workbooks.Open($Sourcefile)
$worksheet=$workbook.Worksheets.Item('URLs')
#Counting the maximum rows of $worksheet
$rowMax = ($worksheet.UsedRange.Rows).count
#Section 1: End
#***************
#Section 2: Creating 2 new Excel files to be used as reporting files[Output Files]
#Adding new Second workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook2
#This Workbook will be used to store and be used as a Report of shortly expiring Certificate data of URLs
$workbook2 = $Excelobj.Workbooks.Add()
$objExcel.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet2= $workbook2.Worksheets.Item(1)
$Worksheet2.Name = 'Expire Data'
#Keying in Headings into First row of sheet
$Worksheet2.Cells.Item(1,1) = "Application URL"
$Worksheet2.Cells.Item(1,2) = "Email Address"
$Worksheet2.Cells.Item(1,3) = "Product Category"
$Worksheet2.Cells.Item(1,4) = "Expiry Date"
$Worksheet2.Cells.Item(1,5) = "Expire In(Days)"
$Worksheet2.Cells.Item(1,6) = "SerialNumber"
$Worksheet2.Cells.Item(1,7) = "Common Name"
$Worksheet2.Cells.Item(1,8) = "Issuier"
$Worksheet2.Cells.Item(1,9) = "Subject Alternative Names"
#Creating a counter variable 'j' used while inputing data to $worksheet2 which is part of second new Workbook
$j=1
#Adding new Third workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook3
#This Workbook will be used to store and be used as a Report of Remaining non-expiring Certificate data of URLs
$workbook3 = $Excelobj.Workbooks.Add()
$Excelobj.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet3= $workbook3.Worksheets.Item(1)
$Worksheet3.Name = 'Not Expiring'
#Keying in Headings into First row of sheet
$Worksheet3.Cells.Item(1,1) = "Domain Name"
$Worksheet3.Cells.Item(1,2) = "Email Address"
$Worksheet3.Cells.Item(1,3) = "Product Category"
$Worksheet3.Cells.Item(1,4) = "Expiry Date"
$Worksheet3.Cells.Item(1,5) = "Expire In(Days)"
$Worksheet3.Cells.Item(1,6) = "SerialNumber"
$Worksheet3.Cells.Item(1,7) = "Common Name"
$Worksheet3.Cells.Item(1,8) = "Issuier"
$Worksheet3.Cells.Item(1,9) = "Subject Alternative Names"
#Creating a counter variable 'k' used while inputing data to $worksheet3 which is part of Third new Workbook
$k=1
#Adding new fourth workbook(Excel file) into Existing Excel object($ExcelObj) and assigning to variable $workbook4
#This Workbook will be used to store and be used as a Report of Remaining Exception data of URLs
$workbook4 = $Excelobj.Workbooks.Add()
$Excelobj.Visible=$false
#Creating Worksheet in the Workbook and giving it a Name
$Worksheet4= $workbook4.Worksheets.Item(1)
$Worksheet4.Name = 'Exception'
#Keying in Headings into First row of sheet
$Worksheet4.Cells.Item(1,1) = "Domain Name"
$Worksheet4.Cells.Item(1,2) = "Email Address"
$Worksheet4.Cells.Item(1,3) = "Product Category"
$Worksheet4.Cells.Item(1,4) = "Exception"
#Creating a counter variable 'l' used while inputing data to $worksheet3 which is part of Third new Workbook
$l=1
#Section 2: End
#***************
#Main Script:
#Reads data files and validates
#Declare the starting positions of Rows and Columns of the input data files
$rowURLName,$colURLName = 1,1
$rowEmail,$colEmail = 1,2
$rowApplication,$colApplication = 1,3
$rowExpirydate,$colExpirydate = 1,4
$rowExpirein,$colExpirein = 1,5
$rowSiNo,$colSiNo = 1,6
$rowCN,$ColCN = 1,7
$rowCA,$ColCA = 1,8
$rowAltname,$colAltname = 1,9
$rowexception,$colException=1,4
#Row Number 1 contains headings in the input file.
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
#Loop for reading the records
For ($i=1;$i -le $rowMax-1; $i++)
{
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
#As row number 1 is heading, we add 1 to the row number as i has been set to 1 in the loop. Else we should start the loop with i=2
# Assigning data to variables
$URL = $Worksheet.Cells.Item($rowURLName+$i,$colURLName).text
$arranyaddress = $worksheet.Cells.Item($rowEmail+$i,$colEmail).text
$Email=@($arranyaddress.Split(",").Trim())
$Application = $worksheet.Cells.Item($rowApplication+$i,$colApplication).text
#Connecting to URLs and Getting certificate details
$req = [Net.HttpWebRequest]::Create($URL)
#Start-Sleep -m 6000
$req.Timeout = $timeoutMilliseconds
try {$req.GetResponse() |Out-Null} catch {Write-Host Exception while checking URL $URL`: $_ -f Red}
#####################################################################################
$Exception=$null
try {$req.GetResponse() |Out-Null} catch {$Exception = $_}
#Replacing special characters in the exception with 'x'
$Exception=$Exception -replace '[^\p{L}\p{Nd}]',"x"
#Contition to check each exceptions matching with Array of exceptions($ExceptionArray)
if($Exceptionarray -contains $Exception)
{
#Displaying data on screen as white for days to expire not less than threshold
Write-Host "URL: $URL`n" -ForegroundColor DarkGreen
#Write-Host "Cert CName: $certName`n" -ForegroundColor DarkGreen
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor DarkGreen
#Write-Host "`nStatus: good" -ForegroundColor DarkGreen
#Write-Host "CertExpiration: $expiration`n" -ForegroundColor DarkGreen
#Writing into the rows and columns of Spreadsheet3(Report of non-expiring certificates)
$worksheet4.Cells.Item($rowexception+$l,$colException) = $Exception
#Keying in Additional data
$l=[int]$l+1
$Worksheet4.Cells.Item($l,1) = $URL
$Worksheet4.Cells.Item($l,2) = $arranyaddress
$Worksheet4.Cells.Item($l,3) = $Application
$Worksheet4.cells.Item($l,4) = $Exception
$Exception=$null
}
else
{
####################################################################################
[datetime]$expiration = $req.ServicePoint.Certificate.GetExpirationDateString()
[int]$certExpiresIn = ($expiration - $(get-date)).Days
[string]$certName = $req.ServicePoint.Certificate.GetName()
############################# Checking the Alternative names ################################################
#downloading the Certificate to location C:\testcert
$certDirectory = "C:\testcert\"
$cert = $req.ServicePoint.Certificate
$bytes = $cert.Export([Security.Cryptography.X509Certificates.X509ContentType]::Cert)
set-content -value $bytes -encoding byte -path "C:\testcert\certicate.cer"
$i
#Gettiing the Alternative name from the downloaded certificate
$certList = Get-ChildItem $certDirectory
foreach ($certificate in $certList)
{
$certificate = new-object security.cryptography.x509certificates.x509certificate2 C:\testcert\$certificate
$sanExt=$certificate.Extensions | Where-Object {$_.Oid.FriendlyName -match "subject alternative name"}
$sanObjs = new-object -ComObject X509Enrollment.CX509ExtensionAlternativeNames
$altNamesStr=[System.Convert]::ToBase64String($sanExt.RawData)
$sanObjs.InitializeDecode(1, $altNamesStr)
[String]$subalternames=Foreach ($SAN in $sanObjs.AlternativeNames) {$SAN.strValue +';'}
#This is to add a semicollen in between alternative names
#$subalternames= $subalternames+';'
#Counting the Alternativenames slots
$slots=$subalternames.Count
}
#removing the current certificate located in C:\testcert folder
Remove-Item -Path "C:\testcert\*" -Force
###############################################################################################################
#Getting only value after 'CN=' from the string variable $certName
$CN=($certName-split'CN=')[1]
$certPublicKeyString = $req.ServicePoint.Certificate.GetPublicKeyString()
$certSerialNumber = $req.ServicePoint.Certificate.GetSerialNumberString()
$certThumbprint = $req.ServicePoint.Certificate.GetCertHashString()
[datetime]$certEffectiveDate = $req.ServicePoint.Certificate.GetEffectiveDateString()
[string]$certIssuer = $req.ServicePoint.Certificate.GetIssuerName()
#Getting only value after 'CN=' from the string variable $certIssuer
$CA=($certIssuer-split'CN=')[1]
# Email Subject and HTML Emailbody.
$MailSubject="Website Certificate Monitoring Alert: SSL cert currently deployed on $URL will expire in $certExpiresIn days"
$MailBody=@"
<html><span style='font-family: Tahoma; font-size: 12px;' >Hi,<br />
<br />
The SSL certificate currently installed on website "$URL" will expire in $certExpiresIn days. You should consider updating with renewed certificate.<br />
<br /><B>Note:</b> Please respond to this Email if you are not the owner for this certificate or if there is any ownership change.<br />
<br />
----------------------------------------------------------------------------</span><br />
<span style='font-family: Tahoma; font-size: 10px;' >This is an automatically generated email.<br /> <br /></span></html>
"@
#End Of Email Body
#If condition to check if days to expire is less than the threashold set ($threashold in the begining of the script)
if ($certExpiresIn -lt $Threshold)
{
#Displaying data on screen as yellow for days to expire less than threashold
Write-Host "URL: $URL`n" -ForegroundColor Yellow
#Write-Host "Cert CName: $certName`n" -ForegroundColor Yellow
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor Yellow
#Write-Host "`nStatus: Warning (Expires in $certExpiresIn days)" -ForegroundColor Yellow
Write-Host "CertExpiration: $expiration`n" -ForegroundColor Yellow
#Send email to corresponding Email addresses for the expiring certs sites
#Send-MailMessage -To $Email -Subject $MailSubject -From $MailFrom -Cc $ccmail -SmtpServer $SmtpServer -Port $smtpport -Priority High -BodyAsHtml $MailBody
#Writing into the rows and columns of Spreadsheet2(Report of expiring certificates)
$worksheet2.Cells.Item($rowExpirydate+$j,$colExpirydate) =$expiration
$worksheet2.Cells.Item($rowExpirein+$j,$colExpirein) = $certExpiresIn
$worksheet2.Cells.Item($rowSiNo+$j,$colSiNo) = $certSerialNumber
$worksheet2.Cells.Item($rowCN+$j,$ColCN) = $CN
$Worksheet2.Cells.Item($rowCA+$j,$ColCA) = $CA
$Worksheet2.Cells.Item($rowAltname+$j,$colAltname) = $subalternames
#Keying in Additional data
$j=[int]$j+1
$Worksheet2.Cells.Item($j,1) = $URL
$Worksheet2.Cells.Item($j,2) = $arranyaddress
$Worksheet2.Cells.Item($j,3) = $Application
$Worksheet2.cells.Item($j,4) = $expiration
$Worksheet2.cells.Item($j,5) = $certExpiresIn
$Worksheet2.cells.Item($j,6) = $certSerialNumber
$Worksheet2.cells.Item($j,7) = $CN
$Worksheet2.cells.Item($j,8) = $CA
$Worksheet2.cells.Item($j,9) = $subalternames
rv subalternames
rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
}
else
{
#Displaying data on screen as Green for days to expire not less than threshold
Write-Host "URL: $URL`n" -ForegroundColor DarkGreen
#Write-Host "Cert CName: $certName`n" -ForegroundColor DarkGreen
#Write-Host "Cert Issuer: $certIssuer`n" -ForegroundColor DarkGreen
#Write-Host "`nStatus: good" -ForegroundColor DarkGreen
Write-Host "CertExpiration: $expiration`n" -ForegroundColor DarkGreen
#Writing into the rows and columns of Spreadsheet3(Report of non-expiring certificates)
$worksheet3.Cells.Item($rowExpirydate+$k,$colExpirydate) =$expiration
$worksheet3.Cells.Item($rowExpirein+$k,$colExpirein) = $certExpiresIn
$worksheet3.Cells.Item($rowSiNo+$k,$colSiNo) = $certSerialNumber
$worksheet3.Cells.Item($rowCN+$k,$ColCN) = $CN
$Worksheet3.Cells.Item($rowCA+$k,$ColCA) = $CA
$Worksheet3.Cells.Item($rowAltname+$k,$colAltname) = $subalternames
#Keying in Additional data
$k=[int]$k+1
$Worksheet3.Cells.Item($k,1) = $URL
$Worksheet3.Cells.Item($k,2) = $arranyaddress
$Worksheet3.Cells.Item($k,3) = $Application
$Worksheet3.cells.Item($k,4) = $expiration
$Worksheet3.cells.Item($k,5) = $certExpiresIn
$Worksheet3.cells.Item($k,6) = $certSerialNumber
$Worksheet3.cells.Item($k,7) = $CN
$Worksheet3.cells.Item($k,8) = $CA
$Worksheet3.cells.Item($k,9) = $subalternames
rv subalternames
rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
$certIssuer=$sanObjs=$Email=$arranyaddress=$URL=$expiration=$req=$certName=$certExpiresIn=$certDirectory=$SAN=$sanExt=$CN=$CA=$subalternames=$cert=$certificate=$certSerialNumber=$certThumbprint=$altNamesStr=$certDirectory=$sanObjs=$null
}
#End of IF-ELSE-Loop
#Removing variables for re-use in for loop
<#rv req
rv certExpiresIn
rv certSerialNumber
rv CN
rv CA
#rv subalternames#>
}
}
#End of FOR-LOOP
#Auto sizing newly created excel sheets with its content
$objRange1 = $Worksheet2.UsedRange
[void] $objRange1.EntireColumn.Autofit()
$objRange2 = $Worksheet3.UsedRange
[void] $objRange2.EntireColumn.Autofit()
$objRange2 = $Worksheet4.UsedRange
[void] $objRange2.EntireColumn.Autofit()
#Saving the $Workbook2 and $workbook3 data to a file in the given Location with filename concatinated with current date
$date=(Get-Date).ToString('MM_dd_yyyy_hh_mm_ss')
$workBook2.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Exp_$date.xlsx")
$file1 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Exp_$date.xlsx"
$workBook2.Close()
$workBook3.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_OK_$date.xlsx")
$file2 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_OK_$date.xlsx"
$workBook3.Close()
$workBook4.SaveAs("C:\scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Error_$date.xlsx")
$file3 = "C:\Scripts\CertcheckNew_V3_overWeb\CertcheckNew\Report_Cert_URL_Error_$date.xlsx"
$workBook4.Close()
#close of data workbook
$workbook.close()
#$Excelobj.Quit()
#Below Comented line will close all excel hence commenting for now.
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
#Stop-Process -Name EXCEL -Force
# otherwise allow stop-process to clean up
#Closing Excel Object invoked
$postExcelProcesses |%{ Stop-Process -Id $_ }
#Attaching and Emailing report files.
#if you have outlook you can create an outlook object else we can use sendmail
#$attachment = new-object System.Net.Mail.Attachment $file1,$file2
#Send Email to Cert DL with Script created Excels
$MailSubject2="SSL certificate Validation files"
$MailBody2=@"
<html><span style='font-family: Tahoma; font-size: 12px;' >Hi,<br />
<br />
Please find the attached SSL certificate Validation files.<br />
<br />
----------------------------------------------------------------------------</span><br />
<span style='font-family: Tahoma; font-size: 10px;' >This is an automatically generated email, please do not reply.<br /> <br /></span></html>
"@
#Send-MailMessage -To $MailTo -Subject $MailSubject2 -From $MailFrom -Cc $ccmail2 -SmtpServer $SmtpServer -Port $smtpport -Priority High -BodyAsHtml $MailBody2 -Attachments $file1,$file2,$file3
#############################################################################################
#End of Code
#############################################################################################
Comments
Post a Comment