Writing logs to databases - PingAccess

Ensure that your database driver JAR file is installed in the <PA_HOME>/deploy directory.

Restart PingAccess after installing the driver.

In conf/log4j2.xml, uncomment one or more of the preset appender configurations listed in the following table.

Database	Configuration
Oracle	For administrative API audit logging, uncomment the `<JDBC>` element with the `name="ApiAuditLog-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For engine audit logging, uncomment the `<JDBC>` element with the `name="EngineAuditLog-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For agent audit logging, uncomment the `<JDBC>` element with the `name="AgentAuditLog-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband client audit logging, uncomment the `<JDBC>` element with the `name="SidebandClientAuditLog-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband end-user audit logging, uncomment the `<JDBC>` element with the `name="SidebandAuditLog-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements.
SQL Server	For administrative API audit logging, uncomment the `<JDBC>` element with the `name="ApiAuditLog-SQLServer-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For engine audit logging, uncomment the `<JDBC>` element with the `name="EngineAuditLog-SQLServer-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For agent audit logging, uncomment the `<JDBC>` element with the `name="AgentAuditLog-SQLServer-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband client audit logging, uncomment the `<JDBC>` element with the `name="SidebandClientAuditLog-SQLServer-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband end-user audit logging, uncomment the `<JDBC>` element with the `name="SidebandAuditLog-SQLServer-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements.
PostgreSQL	For administrative API audit logging, uncomment the `<JDBC>` element with the `name="ApiAuditLog-PostgreSQL-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For engine audit logging, uncomment the `<JDBC>` element with the `name="EngineAuditLog-PostgreSQL-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For agent audit logging, uncomment the `<JDBC>` element with the `name="AgentAuditLog-PostgreSQL-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband client audit logging, uncomment the `<JDBC>` element with the `name="SidebandClientAuditLog-PostgreSQL-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements. For sideband end-user audit logging, uncomment the `<JDBC>` element with the `name="SidebandAuditLog-PostgreSQL-Database"` attribute specified, along with the following `<RollingFile>` and `<PingAccessFailover>` elements.

Note:

The <PingAccessFailover> element is used to define how PingAccess logging fails over if a connection to the primary database is not accessible. Use the retryIntervalSeconds attribute to specify the number of seconds that must pass before retrying the primary JDBC appender.

In conf/log4j2.db.properties, replace the placeholder parameter values for each enabled appender with valid values to provide access to the database.

Info:

You can obfuscate the password used to access the database by running either obfuscate.sh or obfuscate.bat, located in <PA_HOME>/bin. Use the database password as an argument, then copy the output into the password configuration property for the appender in <PA_HOME>/conf/log4j2.db.properties.

In conf/log4j2.xml, uncomment the <AppenderRef> elements in each respective <Logger> section as shown in the following examples.

Oracle

<!-- Audit Log Configuration-->
<Logger name="apiaudit" level="INFO" additivity="false">
    <AppenderRef ref="APIAuditLog-File"/>
    <AppenderRef ref="ApiAuditLog-Database-Failover"/>
    <!--<AppenderRef ref="ApiAuditLog-SQLServer-Database-Failover"/>-->
    <!--<AppenderRef ref="ApiAuditLog-PostgreSQL"/>-->                  
    <!--<AppenderRef ref="ApiAudit2Splunk"/>-->
    <!--<AppenderRef ref="ApiAuditLog-HarFile"/>-->
</Logger>
<Logger name="engineaudit" level="INFO" additivity="false">
    <AppenderRef ref="EngineAuditLog-File"/>
    <AppenderRef ref="EngineAuditLog-Database-Failover"/>
    <!--<AppenderRef ref="EngineAuditLog-SQLServer-Database-Failover"/>-->
    <!--<AppenderRef ref="EngineAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="EngineAudit2Splunk"/>-->
    <!--<AppenderRef ref="EngineAuditLog-HarFile"/>-->
</Logger>
<Logger name="agentaudit" level="INFO" additivity="false">
    <AppenderRef ref="AgentAuditLog-File"/>
    <AppenderRef ref="AgentAuditLog-Database-Failover"/>
    <!--<AppenderRef ref="AgentAuditLog-SQLServer-Database-Failover"/>-->
    <!--<AppenderRef ref="AgentAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="AgentAudit2Splunk"/>-->
    <!--<AppenderRef ref="AgentAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandclientaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandClientAuditLog-File"/>
    <AppenderRef ref="SidebandClientAuditLog-Database-Failover"/>
    <!--<AppenderRef ref="SidebandClientAuditLog-SQLServer-Database-Failover"/>-->
    <!--<AppenderRef ref="SidebandClientAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="SidebandClientAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandClientAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandAuditLog-File"/>
    <AppenderRef ref="SidebandAuditLog-Database-Failover"/>
    <!--<AppenderRef ref="SidebandAuditLog-SQLServer-Database-Failover"/>-->
    <!--<AppenderRef ref="SidebandAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="SidebandAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandAuditLog-HarFile"/>-->
</Logger>

SQL Server

<!-- Audit Log Configuration-->
<Logger name="apiaudit" level="INFO" additivity="false">
    <AppenderRef ref="APIAuditLog-File"/>
    <!--<AppenderRef ref="ApiAuditLog-Database-Failover"/>-->
    <AppenderRef ref="ApiAuditLog-SQLServer-Database-Failover"/>
    <!--<AppenderRef ref="ApiAuditLog-PostgreSQL"/>-->                  
    <!--<AppenderRef ref="ApiAudit2Splunk"/>-->
    <!--<AppenderRef ref="ApiAuditLog-HarFile"/>-->
</Logger>
<Logger name="engineaudit" level="INFO" additivity="false">
    <AppenderRef ref="EngineAuditLog-File"/>
    <!--<AppenderRef ref="EngineAuditLog-Database-Failover"/>-->
    <AppenderRef ref="EngineAuditLog-SQLServer-Database-Failover"/>
    <!--<AppenderRef ref="EngineAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="EngineAudit2Splunk"/>-->
    <!--<AppenderRef ref="EngineAuditLog-HarFile"/>-->
</Logger>
<Logger name="agentaudit" level="INFO" additivity="false">
    <AppenderRef ref="AgentAuditLog-File"/>
    <!--<AppenderRef ref="AgentAuditLog-Database-Failover"/>-->
    <AppenderRef ref="AgentAuditLog-SQLServer-Database-Failover"/>
    <!--<AppenderRef ref="AgentAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="AgentAudit2Splunk"/>-->
    <!--<AppenderRef ref="AgentAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandclientaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandClientAuditLog-File"/>
    <!--<AppenderRef ref="SidebandClientAuditLog-Database-Failover"/>-->
    <AppenderRef ref="SidebandClientAuditLog-SQLServer-Database-Failover"/>
    <!--<AppenderRef ref="SidebandClientAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="SidebandClientAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandClientAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandAuditLog-File"/>
    <!--<AppenderRef ref="SidebandAuditLog-Database-Failover"/>-->
    <AppenderRef ref="SidebandAuditLog-SQLServer-Database-Failover"/>
    <!--<AppenderRef ref="SidebandAuditLog-PostgreSQL"/>-->
    <!--<AppenderRef ref="SidebandAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandAuditLog-HarFile"/>-->
</Logger>

PostgreSQL

<!-- Audit Log Configuration-->
<Logger name="apiaudit" level="INFO" additivity="false">
    <AppenderRef ref="APIAuditLog-File"/>
    <!--<AppenderRef ref="ApiAuditLog-Database-Failover"/>-->
    <!--<AppenderRef ref="ApiAuditLog-SQLServer-Database-Failover"/>-->
    <AppenderRef ref="ApiAuditLog-PostgreSQL"/>                  
    <!--<AppenderRef ref="ApiAudit2Splunk"/>-->
    <!--<AppenderRef ref="ApiAuditLog-HarFile"/>-->
</Logger>
<Logger name="engineaudit" level="INFO" additivity="false">
    <AppenderRef ref="EngineAuditLog-File"/>
    <!--<AppenderRef ref="EngineAuditLog-Database-Failover"/>-->
    <!--<AppenderRef ref="EngineAuditLog-SQLServer-Database-Failover"/>-->
    <AppenderRef ref="EngineAuditLog-PostgreSQL"/>
    <!--<AppenderRef ref="EngineAudit2Splunk"/>-->
    <!--<AppenderRef ref="EngineAuditLog-HarFile"/>-->
</Logger>
<Logger name="agentaudit" level="INFO" additivity="false">
    <AppenderRef ref="AgentAuditLog-File"/>
    <!--<AppenderRef ref="AgentAuditLog-Database-Failover"/>-->
    <!--<AppenderRef ref="AgentAuditLog-SQLServer-Database-Failover"/>-->
    <AppenderRef ref="AgentAuditLog-PostgreSQL"/>
    <!--<AppenderRef ref="AgentAudit2Splunk"/>-->
    <!--<AppenderRef ref="AgentAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandclientaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandClientAuditLog-File"/>
    <!--<AppenderRef ref="SidebandClientAuditLog-Database-Failover"/>-->
    <!--<AppenderRef ref="SidebandClientAuditLog-SQLServer-Database-Failover"/>-->
    <AppenderRef ref="SidebandClientAuditLog-PostgreSQL"/>
    <!--<AppenderRef ref="SidebandClientAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandClientAuditLog-HarFile"/>-->
</Logger>
<Logger name="sidebandaudit" level="INFO" additivity="false">
    <AppenderRef ref="SidebandAuditLog-File"/>
    <!--<AppenderRef ref="SidebandAuditLog-Database-Failover"/>-->
    <!--<AppenderRef ref="SidebandAuditLog-SQLServer-Database-Failover"/>-->
    <AppenderRef ref="SidebandAuditLog-PostgreSQL"/>
    <!--<AppenderRef ref="SidebandAudit2Splunk"/>-->
    <!--<AppenderRef ref="SidebandAuditLog-HarFile"/>-->
</Logger>

Create the database tables. Scripts to create database tables are located in conf/log4j/sql-scripts.

Note:

The scripts are written to handle the default list of elements for the relevant database log appender. Any changes to the list require corresponding changes to the SQL table creation script, or to the table itself if it already exists. For more information on working with these scripts, see the Oracle, PostgreSQL, or MS SQL Server documentation.

Important:

For PostgreSQL database scripts, use of the default public schema is not recommended. To run the scripts against a different schema, choose one of the following options:

Prepend the schema before the table name. For example, api_audit_log would become my_schema.api_audit_log
Run the script using psql and specify an options parameter to define the schema. For example,
```
psql postgresql://<user>@<db_hostname>:5432/<db_name>?options=--search_path=<schema> -f api-audit-log-postgresql.sql
```