question

MilosSkoko avatar image
1 Vote"
MilosSkoko asked AaronMartinas-2074 commented

Export of issued certificates from CA

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

windows-server-security
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DaisyZhou-MSFT avatar image
0 Votes"
DaisyZhou-MSFT answered DaisyZhou-MSFT commented

Hello @MilosSkoko,

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".

76935-isu1.png

Then I can see the contexts in the exported file as below.
76946-issu2.png


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



isu1.png (50.5 KiB)
issu2.png (92.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @DaisyZhou-MSFT ,
Many thanks for your reply.
Yes, this is the exact way I`m currently using. Since I`m 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)

0 Votes 0 ·

Hello @MilosSkoko,

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

0 Votes 0 ·
MilosSkoko avatar image
0 Votes"
MilosSkoko answered DaisyZhou-MSFT commented

Hello @DaisyZhou-MSFT ,
Sorry for the late reply.

Many thanks for support on this topic.

Kr,
Milos

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @MilosSkoko,
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

0 Votes 0 ·
MacLachlanMarkD-4991 avatar image
1 Vote"
MacLachlanMarkD-4991 answered AaronMartinas-2074 commented

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 @MilosSkoko , 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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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][1] module that includes granular queries to CA database, for example: [Get-IssuedRequest][2].


[1]: https://www.powershellgallery.com/packages/PSPKI/
[2]: https://www.pkisolutions.com/tools/pspki/Get-IssuedRequest/
0 Votes 0 ·

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][1] module that includes granular queries to CA database, for example: [Get-IssuedRequest][2].


[1]: https://www.powershellgallery.com/packages/PSPKI
[2]: https://www.pkisolutions.com/tools/pspki/Get-IssuedRequest

0 Votes 0 ·

Thank you @MacLachlanMarkD-4991

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.

0 Votes 0 ·