The Certification Authority database stores much of the information about a Certification Authority's activities. Among other things, it contains information about:
- Certificates issued
- Revoked certificates
- Published blacklists
- Pending certificate requirements
- Rejected certificate requests
- Failed certificate requests
Viewing the contents of the certification authority database is usually done via the certification authority's management console (certsrv.msc), but the possibilities for evaluation and especially for machine processing are very limited.
If you want to get a deeper insight, you have to use command line tools. The program certutil offers the -view switch for this purpose.
CertUtil [Options] -view [Queue | Log | LogFail | Revoked | Ext | Attrib | CRL] [csv]
The -view Schater can be combined with the following arguments, among others:
| Argument | Description |
|---|---|
| -restrict | Restricts the search to certain criteria. |
| -out | Determines the fields of the database to be output. |
| csv | If this switch is set (at the end and without hyphen) the output of Certutil is output as CSV content, so that it can be further processed by machine. |
For example, to get a report on all issued, unrevoked and unexpired certificates, you can create a corresponding query, as described below.
For the sake of simplicity and clarity, the example uses Windows PowerShell to get advanced options for filtering.
With the variable $Now the date is output as text according to the region settings of the logged-in user, so that it can be used as a filter criterion in the further process.
$Now = $((Get-Date).ToString($(Get-culture).DateTimeFormat.ShortDatePattern))
The variable $Out contains a list of database fields to be output, in this case:
- RequestID (the unique request ID of the certificate).
- SerialNumber (the serial number of the certificate)
- RequesterName (the Active Directory identity of the requester)
- CommonName (the common name in the certificate)
- CertificateTemplate (the certificate template from which the certificate was created)
- NotBefore (the date from which the certificate is valid)
- NotAfter (the date when the certificate will expire)
$Out = "RequestID,SerialNumber,RequesterName,CommonName,CertificateTemplate,NotBefore,NotAfter"
The variable $Restrict defines the search criteria. These are in this example:
- Only issued, not revoked certificates (Dispisition=20)
- Only certificates that are still valid, i.e. have not yet expired (NotAfter>=$Now)
$Restrict = "Disposition=20,NotAfter>=$Now"
The variable $file determines the file name in which the output of the command is to be stored for further processing
1TP4File = "$($env:COMPUTERNAME)_Certificates.csv"
The command can now be assembled and executed
certutil -view -restrict $Restrict -out $Out csv > $File
The file that is now output can be loaded into Excel, for example. To do this, open a new file and select in the Data tab that these are to be read in from a CSV file.
The imported data should have been correctly imported directly.
Now you can use the filter functions of Excel to get a deeper insight into the issued certificates. For example, you can filter by the CertificateTemplate field to see all issued certificates from a specific certificate template.
English 
Deutsch
5 thoughts on “Erweiterte Abfragen gegen die Zertifizierungsstellen-Datenbank”
Comments are closed.