Feature Request - Certificate Reporting

Feb 25, 2014 at 3:24 PM
Edited Feb 25, 2014 at 3:29 PM
Hi Camelot,
Would it be possible to incorporate into PSPKI some kind of "easier capability" for generating reports on certificates? I might want to report on:
  • List of certificates issued based upon template x between y and z (simply providing common name and SAN)
  • All issued certificates (not revoked) about to expire in June 201x. (common name and expiry date)
  • All certificates revoked last year by Submitter X
  • Archived certificates and their corresponding KRA certificate(s)
These are a bunch of examples of reports which I often get asked to produce, and I can meet using various exercises with certutil -restrict, etc. However, I don't think it's a great tool and if it was possible to provide some better capability with PSPKI that'd be a fantastic enhancement IMHO.

I've no idea how easy or hard it would be to do - so if it's a no then it's easy for me to accept and continue to praise you for the great work you are doing.

Best regards, Chipeater
Coordinator
Feb 25, 2014 at 6:29 PM
Edited Feb 25, 2014 at 6:29 PM
I'm not sure what do you mean under "easier capability", but certain things definitely are possible. CA database reporting is very wide question a lot of people have different requirements, so there are no exact cmdlets to make specific reports. However, the module provides generic CA database query cmdlets which you can use for your own needs. For example:
  • List of certificates issued based upon template x between y and z (simply providing common name and SAN)
# prepare filters
$filters = "CertificateTemplate -eq WebServer",
"UPN -eq www.company.com",
"NotBefore -gt $((Get-Date).AddMonths(-1))",
"NotBefore -lt $(Get-Date)"
# query CA database
Get-CA ca01* | Get-IssuedRequest -Filter $filters
In this query CA database will return issued certificates that were issued during last month based on WebServer certificate template and which contains "www.company.com" name in the SAN extension.
  • All issued certificates (not revoked) about to expire in June 201x. (common name and expiry date)
# prepare filters:
$filters = "NotAfter -ge 12/01/2015 23:59:59"
"Notafter -le 12/31/2015 00:00:00"
# query CA databse
Get-CA ca01* | Get-IssuedRequest -Filter $filters
In this example CA database will return issued certificates that will expire in december 2015 (at any date between December 1 and December 31).
  • All certificates revoked last year by Submitter X
Get-CA ca01* | Get-RevokedRequest -Filter "Request.RequesterName -eq Contoso\chipeater"
Generally, you may need to perform manual research on what data and how it is stored in the CA database and then construct a desired query.
  • Archived certificates and their corresponding KRA certificate(s)
this report can be constructed, but it is a bit complicated (in certain cases it could be almost impossible). First, there is "Request.RawArchivedKey" column, however it is Binary type and CA database engine doesn't support very good queries on binary columns. Therefore, the only way to query is to dump entire CA database (only issued certificates) and use PowerShell (Where-Object cmdlet) to filter out required rows.
Second, CA do not store KRA certificate information in clear way. You need to extract archived key (which is PKCS#7 blob) and only then you will be able to get KRA certificate that was used to encrypt the key.
Coordinator
Feb 25, 2014 at 6:37 PM
I checked it again, apparently there is a workaround to retrieve only archived rows:
Get-CA ca01* | Get-IssuedRequest -Filter "Request.KeyRecoveryHashes -ge  "
Request.KeyRecoveryHashes column will contain hashes of KRA certificates (no more).
Feb 26, 2014 at 9:53 AM
Hi Camelot,
Thanks for all the feedback.

Why can't you just create an IOS / Android App which will use voice input for me to specify a set of complex custom criteria for a report, deliver the output direct to my work computer and automatically stream Champions League footage to my tablet?

Is that too much to ask for? :-)

Cheers, Chipeater