Get-AdcsDatabaseRow

Synopsis

Gets CA database row from a specified table.

Syntax

Get-AdcsDatabaseRow [-CertificationAuthority] <CertificateAuthority[]> [[-Table] <AdcsDbViewTableName>] [[-RowID] <Int32[]>] [[-Page] <Int32>] [[-PageSize] <Int32>] [[-Property] <String[]>] [[-Filter] <String[]>] [<CommonParameters>]

Description

Gets CA database row from a specified table.

This command is a generic function to access any CA database row. This command allows to access all CA database tables. Although, this command can access any database row, for 'Request' table the use of predefined Get-RevokedRequest, Get-IssuedRequest, Get-PendingRequest and Get-FailedRequest is recommended over this command. Use this caommand to access non-Request tables.

Parameters

-CertificationAuthority <CertificateAuthority[]>

Specifies the Certification Authority to process. This object can be retrieved by running Get-CertificationAuthority command.

-RowID <Int32[]>

Specifies the database row ID or IDs to retrieve.

-Property <String[]>

By default, the command returns only common certificate request properties (database columns). Use this parameter to show additional properties if necessary. List of possible properties depends on CA server operating system version. To retrieve valid property list run Get-CertificationAuthorityDbSchema command.

In order to display all properties for output objects set this parameter to asterisk '*'. However, all property retrieval may affect Certification Authority's performance.

-Filter <String[]>

Specifies the query filter to restrict output objects to ones that matches query filter rule. Query filter rule consist of three components: <RequestProperty>, <comparison operator> and <value>. Query filter is composed in the following format: "<RequestProperty> <comparison operator> <value>" where:
<RequestProperty> – is a certificate request property name. To retrieve valid property list run Get-CertificationAuthorityDbSchema command.
<comparison operator> – specifies the logical operator of the data-query qualifier for the column.
<value> – specifies the data query qualifier applied to the certificate request property.

Possible operators are:
-eq (equal to) – the value in the <value> field equals to a value stored in the certificate request property.
-le (less or equal to) – the value in the <value> field is less or equal to a value stored in the certificate request property. See below about operator behavior with string qualifiers.
-lt (less than) – the value in the <value> field is less then a value stored in the certificate request property. See below about operator behavior with string qualifiers.
-ge (greater or equal to) – the value in the <value> field is greater or equal to a value stored in the certificate request property. See below about operator behavior with string qualifiers.
-gt (greater than) – the value in the <value> field is greater than a value stored in the certificate request property. See below about operator behavior with string qualifiers.

There are special rules when processing the following operators: '-ge', '-gt', '-le' and '-lt' with string qualifiers. In this case, CA server performs binary comparison between strings (column value and qualifier value). For example, "A" is less than "B" ("A" is placed before "B", therefore "B" is greater than "A"), "AC" is greater than "AB", "ABC" is less than "BRC".
If column value length is larger than qualifier string, a wild card is virtually added to the query qualifier value. For example, column value is "a large string" and qualifier value is "a large", then column value is greater than qualifier value. In other words, "AA" > "A" and "A" < "AA".

An example of the filter: Request.RequesterName -eq domain\username
this filter returnes requests that were requested by 'domain\username' user account. See examples section for more filter examples.

You can specify multiple filters. All filters are applied to requests with logical AND operator. This means that output requests must match all filters.

Note: wildcard characters are not supported.

Note: if 'RequestID' parameter is specified, all filters are ignored.

-Table <AdcsDbViewTableName>

Specifies the CA database view table to query. The following view tables are supported:

–Request – queries entire request table.
–Revoked – queries revoked certificates table.
–Issued – queries issued certificates table.
–Pending – queries pending request table.
–Failed – queries failed and denied request table.
–Extension – queries extensions table associated with issued certificates.
–Attribute – queries attributes table associated with issued certificates.
–CRL – queries certificate revocation list (CRL) table.

-Page <Int32>

Specifies the page number to read from CA database. This parameter is part of CA database paging functionality and works in conjunction with 'PageSize' parameter.

-PageSize <Int32>

Specifies the page size to load from CA database. This parameter can limit the number of database rows returned by this command at once. When not specified, no limits are set and CA will return all rows associated with the query.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, InformationAction, InformationVariable,
WarningAction, WarningVariable, OutBuffer, PipelineVariable and OutVariable.
For more information, see about_CommonParameters (https://go.microsoft.com/fwlink/?LinkID=113216).

Inputs

PKI.CertificateServices.CertificateAuthority

Outputs

SysadminsLV.PKI.Management.CertificateServices.Database.AdcsDbRow

Notes

Examples

Example 1

PS C:\> Get-CA -Name "*company ca*" | Get-AdcsDatabaseRow -Table CRL -Filter "CRLNextUpdate -gt $(Get-Date)"

This command returns all non-expired Base and Delta CRLs from CA database.

Example 2

PS C:\> Get-CA ca01.company.com | Get-AdcsDatabaseRow -Table Extension -RowID 87

Retrieves certificate extensions associated with RequestID = 87.

Example 3

PS C:\> Get-CA ca01.company.com | Get-AdcsDatabaseRow -Table Attribute -RowID 87

Retrieves certificate request attributes associated with RequestID = 87.

Related links

Get-CertificationAuthority
Connect-CertificationAuthority
Remove-AdcsDatabaseRow

Minimum PowerShell version support

• Windows PowerShell 3.0

Operating System Support

• Windows 7
• Windows 8
• Windows 8.1
• Windows 10
• Windows 11
• Windows Server 2008 R2 all editions
• Windows Server 2012 all editions
• Windows Server 2012 R2 all editions
• Windows Server 2016 all editions
• Windows Server 2019 all editions
• Windows Server 2022 all editions