Writing logs to databases - PingAccess - 7.3

PingAccess

bundle
pingaccess-73
ft:publication_title
PingAccess
Product_Version_ce
PingAccess 7.3
category
Product
pa-73
pingaccess
ContentType_ce

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

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

  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.
    DatabaseConfiguration

    Oracle

    • For administrative APIapplication programming interface (API) A specification of interactions available for building software to access an application or service. 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 isn't accessible. Use the retryIntervalSeconds attribute to specify the number of seconds that must pass before retrying the primary Java database connectivity (JDBC)Java database connectivity (JDBC)JDBC A Java API that allows Java programs to interact with databases. 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.
    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.

  5. In the conf/log4j2.xml file, 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>
  6. 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 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