I got entrusted with the wonderful job of doing an audit/cleanup for both our certificate authorities, its a very interesting task but I learned that documentation on the certutil tool is very limited or non existent…so I decided to write my own.
This post describes how to identify which certificate templates are being used and audit all requests/certificates generated with them so its easier to decide which of them are obsolete and can be unpublished or deleted, remove all expired certificates and requests and finally defrag the CA database…I got one of our databases down to 60MB from 1.7GB.
First step is the analysis of the certificate templates that are published on the CA. Before cleaning up all obsolete certs and requests you need to make sure that all the published templates are actually the templates you use. I deleted unused templates that had been created and not used anymore and unpublished unused default templates (not a good idea to delete default templates). Here’s a list of all default templates http://technet.microsoft.com/en-us/library/cc755033.aspx
To see which templates are being used, we need to see the requests/issuance of certs for all templates. It is pretty straight forward query (after getting it right the first time). This is the query to get all requests for the User template (one of the default templates)
certutil -view -restrict “certificate template=user” –out [covered later]
The –restrict works as a filter and you just paste the name of template there, BUT this will only work for the default templates. To query user created templates (non-defaults) you need to query using the OID of the template like so
certutil -view -restrict “certificate template=1.3.6.1.4.1.311.21.8.2819805.2707949.10374545.1112108.15908497.246.12570639.12467311” -out [covered later]
To get the OID of all published certificate templates issue this from PSH
Certutil –catemplates –v | select-string displayname,msPKI-Cert-Template-OID
Now we are ready to get all the requests/issued certs/failed requests/denied requests/revoked certs for all the published certificate templates, the last thing to mention are the disposition values that are used to filter, below are their descriptions.
- 20 certificate was issued
- 21 certificate is revoked
- 30 certificate request failed
- 31 certificate request is denied
Last part of the query is the –out field, this enables you to get only the certification information that you require i.e. cert issue date, expiry date etc. To get all the available fields, you need to do a certificate query and omit the –out field, this will give you all the fields that you can choose from, for simplicity’s sake below are all the fields that you can query for.
Request.RequestID Request ID
Request.RawRequest Request
Request.RawArchivedKey Archived Key
Request.KeyRecoveryHashes Key Recovery Agent Hashes
Request.RawOldCertificate Old Certificate
Request.RequestAttributes Request Attributes
Request.RequestType Request Type
Request.RequestFlags Request Flags
Request.StatusCode Request Status Code
Request.Disposition Request Disposition
Request.DispositionMessage Request Disposition Message
Request.SubmittedWhen Request Submission Date
Request.ResolvedWhen Request Resolution Date
Request.RevokedWhen Revocation Date
Request.RevokedEffectiveWhen Effective Revocation Date
Request.RevokedReason Revocation Reason
Request.RequesterName Requester Name
Request.CallerName Caller Name
Request.SignerPolicies Signer Policies
Request.SignerApplicationPolicies Signer Application Policies
Request.Officer Officer
Request.DistinguishedName Request Distinguished Name
Request.RawName Request Name
Request.Country Request Country/Region
Request.Organization Request Organization
Request.OrgUnit Request Organization Unit
Request.CommonName Request Common Name
Request.Locality Request City
Request.State Request State
Request.Title Request Title
Request.GivenName Request First Name
Request.Initials Request Initials
Request.SurName Request Last Name
Request.DomainComponent Request Domain Component
Request.EMail Request Email Address
Request.StreetAddress Request Street Address
Request.UnstructuredName Request Unstructured Name
Request.UnstructuredAddress Request Unstructured Address
Request.DeviceSerialNumber Request Device Serial Number
RequestID Issued Request ID
RawCertificate Certificate
CertificateHash Certificate Hash
CertificateTemplate Certificate Template
EnrollmentFlags Template Enrollment Flags
GeneralFlags Template General Flags
SerialNumber Serial Number
IssuerNameID Issuer Name ID
NotBefore Certificate Effective Date
NotAfter Certificate Expiration Date
SubjectKeyIdentifier Issued Subject Key Identifier
RawPublicKey Public Key
PublicKeyLength Public Key Length
PublicKeyAlgorithm Public Key Algorithm
RawPublicKeyAlgorithmParameters Public Key Algorithm
PublishExpiredCertInCRL PublishExpiredCertInCRL
UPN User Principal Name
DistinguishedName Issued Distinguished Name
RawName Issued Name
Country Issued Country/Region
Organization Issued Organization
OrgUnit Issued Organization Unit
CommonName Issued Common Name
Locality Issued City
State Issued State
Title Issued Title
GivenName Issued First Name
Initials Issued Initials
SurName Issued Last Name
DomainComponent Issued Domain Component
EMail Issued Email Address
StreetAddress Issued Street Address
UnstructuredName Issued Unstructured Name
UnstructuredAddress Issued Unstructured Address
DeviceSerialNumber Issued Device Serial Number
To get all certificate requests for a non-default template use the below, I use these fields in the following query for the –out, but you can use any of the above.
certutil -view -restrict “certificate template=1.3.6.1.4.1.311.21.8.2819805.2707949.10374545.1112108.15908497.246.7506132.8196480” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template1-Requests.txt
For default templates use the name instead of the OID like so
certutil -view -restrict “certificate template=user” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template2-Requests.txt
You will have this output
Row 1:
Request Submission Date: 9/26/2005 11:58 AM
Requester Name: “DOMAIN\ab154777”
Caller Name: “DOMAIN\ab154777”
User Principal Name: “ab154777@domain.local”
Issued Common Name: “Amy Bott”
Certificate Expiration Date: 9/26/2006 11:48 AM
Request Disposition: 0x14 (20) – Issued
To filter by issued, revoked etc. you just need to add the disposition you want in the certutil query (see table above), so if you want to what certificates where issued for the User certificate template use the below. Note that if you do not filter by disposition you get all the requests for that certificate template.
certutil -view -restrict “certificate template=user, disposition=20” -out request.submittedwhen,Request.RequesterName,Request.CallerName,UPN,CommonName,NotAfter,Request.Disposition > c:\Template2-Requests.txt
You should now have a clear idea of what certificate templates are being used and which can be unpublished or deleted. Before deleting any certificate templates I suggest that you back up the CA and also keep a dump of all templates using certutil –catemplates –v > c:\templatedump.txt.
The time to clear the CA database from the thousands of expired certificates and requests has arrived, backup the CA database before starting this. Actually this is the simplest step; you just decide the appropriate date and start deleting using the certutil –deleterow command with either the ‘Request’ or ‘Cert’ value, see table below
| Name | Description | Type of data |
| Request | Failed and pending requests | Submission date |
| Cert | Expired and revoked certificates | Expiration date |
If you need to delete all failed and pending requests up to 15th March 2012 use
Certutil –deleterow 3/15/2012 Request
To delete ‘all’ certificates expired by 15th March 2012 use
Certutil –deleterow 3/15/2012 Cert
The problem with this is that the command will just delete around 2500 entries and return an error (the deletions are successful; it just won’t go on deleting all entries. Luckily Mr Pork Chop Express wrapped the command in this batch file which works wonders, just replace the date and replace Request with Cert if you need to delete certificates.
@echo off
:Top
Certutil -deleterow 8/31/2010 Request
If %ERRORLEVEL% EQU -939523027 goto Top
In my maintenance I noted that most (if not all) of the user certificates were not deleted using this approach, I’m not sure why but maybe it’s got something to do with private key archiving being enabled in the template. However I managed to get rid of them using the RequestID field of the expired certificates with the certutil –deleterow i.e. certutil –deleterow 305 (where 305 is the RequestId), this has to be done a row at a time so PSH is best used.
In order to get all expired certificates before 1/1/10 open PSH and issue
certutil –view –restrict “notafter<=1/1/2010” –out request.requestid | Select-string –SimpleMatch “(“
The output will be like this ‘Request ID: 0xc8cb (51403)’, you only need the 51403 so choose all the bracketed numbers (the RequestIDs) and paste them in a notepad, then replace the white space and brackets to end up with only the numbers then save the file (this is parsing the hard way, use any other method you find comfortable).
Load the RequestIDs from the text file into an array $rqst = get-content c:\RequestIDs.txt. Now just use the array to delete all the expired certificates (rows) in the CA database with this command
$rqst | Foreach-Object { $_ ; certutil –deleterow $_ }
Now with a clean CA and no errors (you should have fixed any issues with autoenrollment and bad templates by now) we are ready to defragment the CA database and bring it to an almost new condition. A backup is again very called for here, so backup the database first.
In order for a successful defrag you need to have free space on the partition at least equal to the DB size, this because the esentutl will copy the database to another location on the same partition. Take note of where the CA database is located, you can easily check by going into the properties of the CA (from the GUI) and checking the Storage tab or running the certutil -databaselocations.
Stop the ‘Certificate Services’ service and disable it (Active Directory Certificate services from services.msc) then run the following command esentutl /d c:\windows\system32\CertLog\YourCA.edb.
This should be the last step in your Certificate Authority and (hopefully) all should have gone well, let me know if you found this useful.
The only useful link I found and used http://blogs.technet.com/b/askds/archive/2010/08/31/the-case-of-the-enormous-ca-database.aspx
My Blog 