Powershell replace csv object

Question

I need to make expiring certificates report, but have issue with replacing Certificate Expiration Date OID inside the csv exported from "certutil". ForEach-Object command don't recognize the column and replaces whole csv, though I can filter Template using Where-Object.

$currdate = Get-Date
$date = (Get-Date).AddYears(2)
$template = "RDP|IIS"
$path = "C:\Temp\"
    if(!(test-path $path))
{
        New-Item -ItemType Directory -Force -Path $path
}
certutil -view  -restrict Disposition=20 -out "Request.CommonName,NotAfter,CertificateTemplate" csv | Out-File $path\ExpiredCerts.csv 
Import-Csv $path\ExpiredCerts.csv | ForEach-Object {$_.'Certificate Template' -replace "^\d.* ",""} 
Import-Csv $path\ExpiredCerts.csv |Where-Object {$date -gt $_.'Certificate Expiration Date' -and $currdate -lt $_.'Certificate Expiration Date' -and $_.'Certificate Template' -match $template} | ConvertTo-Html -Head $Header | Out-File $path\ExpiredCerts.htm

CSV

"Request Common Name","Certificate Expiration Date","Certificate Template"
"*.piltover1.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS"
"*.piltover2.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS"
"*.piltover3.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS"

Final Result

$currdate = Get-Date
$date = (Get-Date).AddDays(30)
$template = "RDP|IIS"
$path = "C:\Temp\"
    if(!(test-path $path))
{
        New-Item -ItemType Directory -Force -Path $path
}
certutil -view  -restrict Disposition=20 -out "Request.CommonName,NotAfter,CertificateTemplate" csv | Out-File $path\ExpiredCerts.csv
$data = Import-csv $path\ExpiredCerts.csv
foreach ($item in $data) {
    $item.'Certificate Template' = ($item.'Certificate Template' -split ' ')[-1]
}
$data | Where-Object {$date -gt $_.'Certificate Expiration Date' -and $currdate -lt $_.'Certificate Expiration Date' -and $_.'Certificate Template' -match $template} |
        ConvertTo-Html -Head $Header | Out-File ExpiredCerts.htm

Could you add an example of how the exported file looks, as plain text (just a few lines for testing) — Santiago Squarzon
– Santiago Squarzon, Commented Dec 20, 2021 at 13:49
@SantiagoSquarzon "Request Common Name","Certificate Expiration Date","Certificate Template" ".piltover.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS" ".piltover.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS" "*.piltover.com","11/06/2022 13:08","1.3.6.1.4.1.311.21.8.9809061.13872499.9847428.7216726.9936658.242.11024705.6775621 IIS" — Qwant
– Qwant, Commented Dec 20, 2021 at 14:05
Please edit your question and put the wanted extra info in there as formatted text. In a comment it becomes unreadable — Theo
– Theo, Commented Dec 20, 2021 at 14:08
@SantiagoSquarzon i've already made some changes, thank you. — Qwant
– Qwant, Commented Dec 20, 2021 at 14:34

Theo · Accepted Answer · 2021-12-20 15:23:04Z

1

You could do this to remove the OIDs from column 'Certificate Template' and without the need for writing that data to file and re-importing it, create a HTML file from it.

This assumes you already have the raw data from certutil in a CSV file:

$data = Import-Csv -Path (Join-Path -Path $path -ChildPath 'ExpiredCerts.csv')
# remove OIDs in column 'Certificate Template'
foreach ($item in $data) {
    $item.'Certificate Template' = ($item.'Certificate Template' -split ' ')[-1]
}
# filter the data you need based on expiration date and template
$data | Where-Object {$date -gt (Get-Date $_.'Certificate Expiration Date') -and 
                      $currdate -lt (Get-Date $_.'Certificate Expiration Date') -and 
                      $_.'Certificate Template' -match $template} | 
        ConvertTo-Html -Head $Header | Set-Content (Join-Path -Path $path -ChildPath 'ExpiredCerts.htm')

P.S. I'm using Set-Content in favor if Out-File because we don't know what version of PowerShell you are using. In PS version 7.x, the default encoding for Out-File is utf8NoBOM, while in PS version 5.1 the default file encoding is unicode (= UTF16-LE) which is probably not what you want.

answered Dec 20, 2021 at 15:23

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Qwant Over a year ago

Thank you for help, it does it's job! I've made some changes to fit my code. Added final result to question.

Collectives™ on Stack Overflow

Powershell replace csv object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related