Use PowerShell to Generate Report of Certificates Issued by your Root CA

Some of you may love using certutil.exe, most of you probably don’t. I personally prefer to do things in PowerShell as the data is much easier to manipulate and read. Before getting started I’ll be honest. Yes, this still relies on certutil, but it takes that data and makes it actually useable.

First things first: certutil is a real jerk. You’d think you could simply filter by the names of the various templates to see what certificates were issued, but no. They want you to filter by the templates’ Object Identifier which is hidden away in the Extensions tab under the Certificate Template Information extension.

You can use certutil to dump this information with the following command

certutil.exe -v -template

It will appear in the output as “TemplatePropOID” as seen here

Unfortunately you’ll probably notice that this value starts off with a return character, a few spaces, and sometimes words at the end as well. The only portion of this we can actually use is the numerical part. I’ve solved this with a bit of PowerShell trickery.

$templateDump = certutil.exe -v -template
$i = 0
$templates = @(ForEach($line in $templateDump){
     If($line -like "*TemplatePropOID =*"){(($templateDump[$i + 1]) -split " ")[4]}
     $i++
})

With the command above, you will store all the Object Identifiers for your templates as the array $templates. I’m not great with regular expressions so I’m sure there’s probably a better way to accomplish this. Anyway, essentially what I’m doing is taking the output of certutil.exe -v -template and going through it line by line looking for the phrase “TemplatePropOID =“. When it finds a line containing this, it splits that line into multiple lines based on the whitespace characters.

In command line example above, the multiple line split would equate to





1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.11486880.6766769
Web
client
and
server

Notice the 4 blank lines at the start? that’s 0 – 3 of the array. The 4th item in the array is the Object Identifier, and then the rest we simply don’t care about. That’s why you see the [4] in the PowerShell command above, I’m dropping everything except that single line.

In my environment when I break it down this way, the numerical value for the template is always the 4th item in the array that’s generated. Clear as mud? perfect. Try running it on your CA and see how it looks. It’s possible yours may be different, I can’t be sure.

ProTip: If you only care about a specific template and you already know what the Object Identifier is, you can easily simplify this by storing it as a variable instead of worrying about all the stuff I just posted above. It’s less dynamic but at the same time there’s less headache. Here’s an example

$templates = @(
     '1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.11486880.6766769'
)

Alright so now that you (hopefully) have the Object Identifiers, you should be able to have some more fun with PowerShell and certutil. Let’s get every certificate that’s been issued by each template and store it as an array named $certs

$certs = $null
ForEach($template in $templates){     
     $certs += certutil -view -restrict "certificate template=$template,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate"
}

So, here I’m looping through the $templates array and returning all the successfully issued certificates based on each template. (disposition 20 refers to issued certs, there are different codes for different statuses like revoked, failed, etc.)

Please note, in the example above I’m searching through ALL certificate templates. This can take a very long time if you never clean up your CA. I’d recommend excluding certain certificate templates that you know you don’t care about by using an If statement. For example

$certs = $null
ForEach($template in $templates){
     If($template -ne "1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.1638972.6366950"){
          $certs += certutil -view -restrict "certificate template=$template,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate"
     }
}

I’m returning the values I think are important. Common Name, Effective (Issue) Date, Expiration Date, and the Template. From here, we can parse through the $certs array and get something that’s actually useable in PowerShell

$i = 0
$output = @(
     ForEach($line in $certs){
          If($line -like "*Issued Common Name: *"){
               $asdf = New-Object -TypeName psobject
               $asdf | Add-Member -membertype noteproperty -name 'Common Name' -value (($certs[$i] -replace "Issued Common Name: ","") -replace '"','').trim()
               $asdf | Add-Member -membertype NoteProperty -name 'Effective Date' -value (($certs[$i+1] -replace "Certificate Effective Date: ","") -replace '\d+\:\d+\s+\w+','').trim()
               $asdf | Add-Member -membertype NoteProperty -name 'Expiration Date' -value (($certs[$i+2] -replace "Certificate Expiration Date: ","") -replace '\d+\:\d+\s+\w+','').trim()
               $asdf | Add-Member -membertype NoteProperty -name 'Template' -value (($certs[$i+3] -replace "Certificate Template: ","") -replace '"','').trim()
               $asdf
          }
          $i++
     }
)

Do yourself a favor and paste this into your PowerShell ISE so you can actually read it. The logic here is similar to how I got the Template Object Identifiers. I’m looping through the $certs array line by line looking for the phrase “*Issued Common Name: *”. When I find that phrase, I logically know that this line and the next 3 after it have the information I’m looking for. I’m storing this information in a new PowerShell object called $asdf (lol this is what I use when I can’t think of a good name for a variable). I’m also removing the extra info like whitespaces and timestamps so the output will be clean and easily readable (that’s what the .replace and .trim() are doing). I then drop this into the $output array.

I can then output $output to the screen and…

As you can see in the example output above, the data is now actually useable. You can sort it, export it to CSV, filter it easily, etc.

This got me what I needed, but was this helpful for you? Or am I a moron? Please feel free to comment or offer suggestions. I’m not pretending to know everything and I’d love to see your thoughts on this.