PingAccess

Writing logs to databases

Enable database logging for the API, engine, and agent audit logs in conf/log4j2.db.properties.

About this task

PingAccess supports logging to Oracle, SQL Server, and PostgreSQL databases. Scripts are provided in conf/log4j/sql-scripts to create the necessary tables.

Steps

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

  2. After installing the driver, restart PingAccess.

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

    Database Configuration

    Oracle

    • For administrative application programming interface (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.

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

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

    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.

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

    Oracle

    Example:

    <!-- 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

    Example:

    <!-- 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

    Example:

    <!-- 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>

  6. Create the database tables.

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

    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.

    For PostgreSQL database scripts, use of the default public schema isn’t 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