PingDirectory

Managing the JDBC Access Log Publishers

You can configure the PingDirectory server to implement a centralized logging system with different databases by connecting to a database.

The PingDirectory server supports the Java Database Connectivity (JDBC) API, which allows access to SQL datastores by means of its JDBC drivers. The JDBC 4.0 API, part of the Java SDK, provides a seamless method to interface with various database types in heterogeneous environments.

Centralized logging simplifies log correlation and analysis tasks and provides security by storing data in a single repository. However, data flow asymmetries might complicate synchronization or network provisioning and could unduly burden the central repository with heavy loads.

Before you begin

Before configuring the Java Database Connectivity (JDBC) Access Log Publisher, you must carry out two essential steps to set up the database.

To set up the database:

  • Install the database drivers in the PingDirectory server lib directory.

  • Define the log mapping tables needed to map access log elements to the database column data.

    Only those elements in the log mapping table get logged by the JDBC log publisher.

Configuring the JDBC drivers

About this task

The PingDirectory server supports several JDBC drivers available in the market. You should use the JDBC 4 drivers supported in the Java platform. For example, for Oracle databases, you must use the ojdbc.jar driver for Java and any associated JAR files (National Language Support .jars and others) required to connect with the particular database. The following databases are supported:

  • DB2

  • MySQL

  • Oracle Call Interface (OCI)

  • Oracle Thin

  • PostgreSQL

  • SQL Server

Steps

  • Obtain the .jar file or files for your particular database and copy them into the <server-root>/lib directory.

Configuring the log field mapping tables

About this task

The log field mapping table associates access log fields with the database column names. Configure the log field mapping table using the dsconfig tool, which generates a DDL file that you can import into your database. The DDL file is generated when you create the JDBC Log Publisher.

To uniquely identify a log record, you should map the following fields:

  • timestamp

  • startupid

  • message-type

  • connection-id

  • operation-type

  • instance-name

The table name is not part of this mapping.

The PingDirectory server also provides these options that you can select for creating a log field mapping table:

Complete JDBC Access Log Field Mappings

Maps all 52 object properties.

Complete JDBC Error Log Field Mappings

Maps all 8 object properties.

Simple JDBC Access Log Field Mappings

Maps a common set of object properties.

Custom JDBC Access Log Field Mappings

Create a custom set of JDBC log field mappings.

Custom JDBC Error Log Field Mappings

Create a custom set of JDBC error log field mappings.

Steps

  1. Use dsconfig to create a log field mapping table.

  2. In the main menu, enter o to change to the Standard Object menu, and enter the number corresponding to Log Field Mapping.

  3. In theLog Field Mapping management menu, enter the option to create a new Log Field Mapping.

  4. In theLog Field Mapping template menu, enter the option to select a complete JDBC Access Log Field mapping to use as a template for your new field mapping.

  5. Enter a name for the new field mapping.

    For this example, enter my-jdbc-test.

  6. In the Access Log Field Mapping Properties menu, select a property for which you want to change the value.

    Any property that is undefined is not logged by the JDBC Access Log Publisher.

  7. Enter f to save and apply the changes.

  8. In the Log Field Mapping Management menu, enter q to exit the menu.

  9. View the existing Log Mappings on the system.

    Example:

    $ bin/dsconfig list-log-field-mappings

    Result:

    Log Field Mapping	                   : Type
    ----------------------------------------:-------
    Complete JDBC Access Log Field Mappings : access
    Complete JDBC Error Log Field Mappings  : error
    my-jdbc-test                            : access
    Simple JDBC Access Log Field Mappings   : access

Configuring the JDBC Access Log Publisher using dsconfig interactive mode

About this task

After setting up the drivers and the log mapping table, use the dsconfig tool to configure the JDBC Access Log Publisher on the PingDirectory server. The following example uses dsconfig interactive mode to illustrate the steps required to configure the log publisher and the external database server.

Steps

  1. Copy the database .JAR files to the <server-root>/lib directory, and then restart the PingDirectory server.

  2. Launch the dsconfig tool in interactive command-line mode.

    Example:

    $ bin/dsconfig
  3. Enter the connection parameters to bind to the PingDirectory server.

    Enter the host name or IP address, type of LDAP connection (LDAP, SSL, or StartTLS) that you are using on the PingDirectory server, the LDAP listener port number, the user bind DN, and the bind DN password.

  4. In the main menu, enter the number corresponding to Log Publisher.

  5. In the Log Publisher management menu, enter the option to create a new log publisher.

  6. In the Log Publisher template menu, enter n to create a new Log Publisher.

  7. In the Log Publisher Type menu, enter the option to create a new JDBC-Based Access Log Publisher.

  8. Enter a name for the JDBC Access Log Publisher.

  9. In the Enabled Property menu, enter the option to enable the log publisher.

  10. In the Server Property menu, enter the option to create a new JDBC External Server.

  11. Enter the name for the JDBC External Server.

    This is a symbolic name used to represent the database management system (DBMS).

  12. In the JDBC Driver Type Property menu, enter the number corresponding to the type of JDBC database driver type.

  13. Enter a name for the database-name property.

    This is the DBMS database name. The database name must contain the table referred to in the generated DDL.

  14. Enter the host name or IP address (server-host-name) of the external server.

  15. Enter the server listener port.

    For this example, enter 1541.

  16. Review the properties for the external server, and then enter f to apply the changes.

  17. If you need to supply your own JDBC URL, enter a for advanced properties to open the jdbc-driver-url property and supply the appropriate URL.

    Example:

    The example below shows how to access an Oracle Thin Client connection using a SID instead of a Service.

    >>>> Configure the properties of the JDBC External Server
    
    Property	Value(s)
    -----------------------------------------------------
    1) description	-
    2) jdbc-driver-type oraclethin
    3) jdbc-driver-url jdbc:oracle:thin@myhost:1541:my_SID
    4) database-name	jdbc-test
    5) server-host-name localhost
    6) server-port	1541
    7) user-name	-
    8) password	-
    
    ?) help
    f) finish - create the new JDBC External Server
    a) hide advanced properties of the JDBC External Server
    d) display the equivalent dsconfig arguments to create this object
    b) back
    q) quit
    
    Enter choice [b]: f

    Result:

    When the JDBC Log Publisher is created, the PingDirectory server automatically generates a DDL file of the Log Field Mappings in the <server-root>/logs/ddls/<name-of-logger>.sql file, and you receive the following message.

    JDBC External Server was created successfully
  18. Import the DDL file to your database.

Configuring the JDBC Access Log Publisher using dsconfig non-interactive mode

About this task

The following example uses dsconfig non-interactive mode to illustrate the steps to configure the log publisher and the external database server presented in the previous section.

Steps

  1. Use dsconfig with the --no-prompt option to create the JDBC external server.

    Example:

    $ bin/dsconfig --no-prompt create-external-server \
      --server-name jdbc-external \ --type jdbc \
      --set jdbc-driver-type:oraclethin \
      --set database-name:ubid_access_log \
      --set server-host-name:localhost --set server-port:1541
  2. Use dsconfig to create the log publisher.

    Example:

    $ bin/dsconfig --no-prompt create-log-publisher \
      --publisher-name jdbc-test \
      --type jdbc-based-access \
      --set enabled:true \
      --set server:jdbc-external \
      --set "log-field-mapping:Simple JDBC Access Log Field Mappings"

    Result:

    When the JDBC Log Publisher is created, the PingDirectory server automatically generates a DDL file of the Log Field Mappings in the <server-root>/logs/ddls/<name-of-logger>.sql file.

  3. Import the DDL file to your database.

    The procedure to configure the JDBC-Based Error Log Publisher is similar to creating a JDBC-Based Access Log Publisher. You can run the previous dsconfig command with the --type jdbc-based-error as follows.

    $ bin/dsconfig --no-prompt create-log-publisher \
    --publisher-name jdbc-error-test \
    --type jdbc-based-error \
    --set enabled:true \
    --set server:jdbc-external \
    --set "log-field-mapping:Simple JDBC Access Log Field Mappings"