Export of issued certificates from CA

Miloš Skoko 16

Hi guys, What is the best way (script) to pull out export (whole list or just a count) of all CAs issued certificates, same as that can be done with right-click on Issued Certs and export, from CA windows. Ive tried with certutil -view log to CSV file, but that exports issued, revoked, and failed requests together.

Gregory Hardt 0 Reputation points

2023-10-31T16:17:26.2466667+00:00
References to the PSPKI module are no longer as valid as they once were. When I installed and checked that module on 31/10/2023, it only had two commandlets in it, not the full set it once did.

I'll look into the COM object approach and will post a separate comment if I figure out a simple (or more useful) approach using it. [Entering just 'certutil -dump' at the cmd will give you the servername\CAName you need for using the COM Object... if you are pursuing those scripts. Look for the line "Config:".]

When executed on the CA, certutil will export a list of issued certificates but filtering it down can be a bit of a pain. The -restrict flag can help with this... but certutil on 2022 Server was throwing an error about receiving too many flags when I tried to use it in combination with -out and -view. Fortunately, PowerShell can help filter the list down a bit.

The following one-liner does the following:

Uses certutil to grab all issued certificates and some columns useful for sorting/reporting.

Certutil exports the list of certs in Csv format but to the console (not a file).

PowerShell converts the Csv command-output into an object for easier parsing.

PowerShell then filters out: CA certificates and non-issued requests, revoked certificates, CA Exchange certificates, and expired certificates.

PowerShell dumps the filtered list into a variable for easy export, display, or further processing.

$issuedCerts = (certutil -out "Request Disposition,Revocation Date,Certificate Expiration Date,Certificate Effective Date,Certificate Template,Issued Common Name,Issued Email Address" -view csv) | ConvertFrom-Csv | Where-Object {$_."Request Disposition" -eq "20 -- Issued" -and $_."Revocation Date" -eq "EMPTY" -and $_."Certificate Template" -ne "CAExchange"} | Where-Object {[DateTime]$_."Certificate Expiration Date" -gt (Get-Date)}

The $issuedCerts var can now be exported to file, piped to ConverTo-Html and emailed, or just output to console. e.g. $issuedCerts | fl

$issuedCerts.Count gives a total count of issued certificates. e.g. Write-Host "Total Issued Certs: $($issuedCerts.Count)"

Some notes:

The dates attributes are strings - so cast them to DateTime for date comparisons.
E.g. $issuedCerts | Sort-Object {[DateTime]$_."Certificate Expiration Date"}

Certificate Template attribute may contain the TemplateOID<space>TemplateName, so sorting or filtering by name may require an additional step (especially if the TemplateName contains spaces also).

CertUtil can return the SANs (DNS, IPv4Address, etc.)... but I didn't quickly find how to easily extract that as a column using -out.

5 answers

Daisy Zhou 24,981 Reputation points Microsoft Vendor

2021-03-12T02:11:15.35+00:00

Hello anonymous usersSkoko,

Thank you for posting here.

I have done a test in my lab.

I can export certificates in "Issued Certificates" containers by right clicking "Issued Certificates" and selecting "Export List".

Then I can see the contexts in the exported file as below.

I think this is what you want, but would you please tell us why you want to export them by using script?

And based on "I`ve tried with certutil -view log to CSV file, but that exports issued, revoked, and failed requests together.", what command have you tried?

Should you have any question or concern, please feel free to let us know.

Best Regards,
Daisy Zhou
Please sign in to rate this answer.

2 people found this answer helpful.
Miloš Skoko 16 Reputation points

2021-03-12T06:44:40.253+00:00

Hello @Daisy Zhou ,
Many thanks for your reply.
Yes, this is the exact way Im currently using. Since Im doing this kind of export manually every month, would like to automate it using some command/script in combination with the task scheduler. I don`t need details, a simple count of a total number of issued certificates is all I need in this case.

The command I`ve tried from PS:
certutil -view -log csv > C:\Temp\Issued.csv

Also, what I tried is :
certutil -view -out "RequestID,RequesterName,RequestType,NotAfter,CommonName,CertificateTemplate,SerialNumber" csv > C:\temp\Issued.csv

Besides this, currently having issue with export, and need to solve that first:
The handle is invalid. 0x80070006 (WIN32: 6 ERROR_INVALID_HANDLE)

Daisy Zhou 24,981 Reputation points Microsoft Vendor

2021-03-15T08:08:55.85+00:00

Hello anonymous usersSkoko,

Thank you for your update.

I am sorrry, I can not find one proper command to export the certs in Issued Certificates container.

Thank you for your understanding and support.

Best Regards,
Daisy Zhou

Rajput, Prashant 0 Reputation points

2024-08-22T13:52:28.91+00:00

Hello @Daisy Zhou ,

for reporting purpose need to export issuing certificate base on template wise.

so if you can guide us for PS script which

Generate file with contents of all issuing certificate ( include expire ) and then there function for Yes or Not for deletion and then dump the file before delete.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Laird Mark D. MacLachlan 11 Reputation points

2021-05-12T23:52:48.8+00:00

I have the same question as the OP and would just like to comment that asking "would you please tell us why you want to export them by using script?" tells me the person responding does not understand the purpose of automation. The reasons WHY they want to do that are irrelevant. The question was HOW.

I am trying to do the same task because management of certificate renewals is a nightmare. I have PowerShell code that remotely connects to my CA and does a dump. Then using the DNS Name we identify from our naming convention what group needs to manage the upgraded certificate and we send out an email alerting them of what they need to address. We don't want a person to have to click anything within the CA. The people responsible for the certificate renewals don't have access to the CA and we don't want the people that do to be tied down by what should be an automated process. All of this needs to be scheduled, we can't have unscheduled down time in production, so we cannot allow for auto renewal. Instead we have written script code in PowerShell that will perform the request, install it and then bind it within IIS, all without human intervention. We just schedule our jobs for the specific maintenance window approved for that application.

To the OP anonymous usersSkoko , I would recommend you do what I have done and run the following:

CertUtil -deleterow 04/01/2021 Cert
CertUtil -deleterow 04/01/2021 Request

The first will remove all Revoked and Expired Certificates. The second will remove all Failed Requests. The date you put will delete anything OLDER than the date given.
So there will at least be less extra and unneeded data exported. It would really be great if MS would release a comprehensive PowerShell module for the CA server software so we could be more granular. CertUtil SHOULD have the ability to specify what to export.
Please sign in to rate this answer.

2 people found this answer helpful.
Vadims Podāns 9,121 Reputation points MVP

2021-05-13T07:52:00.87+00:00

CertUtil -deleterow 04/01/2021 Cert
CertUtil -deleterow 04/01/2021 Request

these commands will REMOVE rows from CA database, not export as OP asks. If you delete them just to reduce export amount, then you maybe understand in automation, but do not understand in PKI and misuse it.

It would really be great if MS would release a comprehensive PowerShell module for the CA server software so we could be more granular.

I can't speak for Microsoft, but there is one decent community PowerShell PKI module that includes granular queries to CA database, for example: Get-IssuedRequest.

Vadims Podāns 9,121 Reputation points MVP

2021-05-13T07:57:02.477+00:00

CertUtil -deleterow 04/01/2021 Cert
CertUtil -deleterow 04/01/2021 Request

these commands will REMOVE rows from CA database, not export as OP asks. If you delete them just to reduce export amount, then you maybe understand in automation, but do not understand in PKI and misuse it.

It would really be great if MS would release a comprehensive PowerShell module for the CA server software so we could be more granular.

I don't think they have this in their plans for any near future. However, there is one decent community PowerShell PKI module that includes granular queries to CA database, for example: Get-IssuedRequest.

Aaron Martinas 11 Reputation points

2021-09-22T16:24:21.887+00:00

Thank you @Laird Mark D. MacLachlan

I hate when someone doesn't know the answer to a question and therefore

1) provides a workaround that doesn't answer the question
2) wastes time going down unnecessary rabbit holes

Just answer the question or don't. Those of us asking the question later have to wade through too much nonsense when people do this.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Mitch 6 Reputation points

2021-11-12T23:28:40.157+00:00

Hi Folks. Not sure if you've already resolved this. You can view the Certificate Authority store using the COM object called CertificateAuthority.View.
This gentleman has written out the powershell around it: https://www.sysadmins.lv/retired-msft-blogs/alejacma/how-to-export-issued-certificates-from-a-ca-programatically-powershell.aspx
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Miloš Skoko 16 Reputation points

2021-03-18T07:04:19.757+00:00

Hello @Daisy Zhou ,
Sorry for the late reply.

Many thanks for support on this topic.

Kr,
Milos
Please sign in to rate this answer.
Daisy Zhou 24,981 Reputation points Microsoft Vendor

2021-03-18T07:11:59.563+00:00

Hello anonymous usersSkoko,
Thank you for your update.
As always, if there is any question in future, we warmly welcome you to post in this forum again. We are happy to assist you!

Best Regards,
Daisy Zhou
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Sebastian Cerazy 316

The code exports binary certificates. No need for this (for me)
I just need a list with certificate subject, SAN & serial fields

Anybody has anything useful?
Script from here just errors out for me:

Exception calling "Substring" with "2" argument(s): "Length cannot be less than zero.
Parameter name: length"
At C:\PSScripts\getcerts.ps1:71 char:13
+             $CAs += $configString.SubString($configString.IndexOf("`` ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentOutOfRangeException

Andy Scull 6 Reputation points

2022-06-03T09:02:40.64+00:00

Hello! I wanted to do the same, so played around with certutil first
I think you cannot get SAN directly from CA database
If you run "certutil -schema" it will output CA's database schema, there is no SAN field in it, so you can get only subject (common name) and serial from the database itself, for other fields you'll need to somehow get each certificate individually and parse it

Trev Evans 0

Heres the first part of a script I was working on to revoke expired certs.

$certutilcsv = "certs-raw-dump-before-today.csv"
$Today = Get-Date -Format "dd/MM/yyyy"

#disposition 20 = issued 
certutil -restrict "Certificate Expiration Date < $today,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate,SerialNumber" -view csv >> $certutilcsv

$torevoke = import-csv $certutilcsv
$CertAuthority = "server\CAName"
#then your revoke bit- adjust paths as needed
$logfile = "D:\Scripts\Revoke-Expired-CA\CA_revoke_log.txt"
foreach($serial in $torevoke){
	$SerialNumber = $serial.'Serial Number'
	$ExpiryDate = $serial.'Expiration Date'
	$common = $serial.'Issued Common Name'
	write-host revoke $SerialNumber, $common

	certutil -config $CertAuthority -revoke $SerialNumber 6 | Out-File  D:\scripts\Revoke-Expired-CA\result.txt


	$result = Get-Content D:\Scripts\Revoke-Expired-CA\result.txt
	$logText = ""
	if($result -like "*ERROR*"){
    	$logText += "Revoking of $common Failed"
		}else{
    		$logText += "The Certificate for $common Expired on $ExpiryDate Serial: 			$SerialNumber was revoked on $Today : Reason Onhold"
			}

	Add-Content -Path $logfile -Value $result
	Add-Content -Path $logfile -Value $logText
	Add-Content -Path $logfile -Value " "
								}

Share via

Export of issued certificates from CA

5 answers

Your answer