--- title: Direct audit information to MySQL description: The sample includes an external CSV file and a mapping between objects in that file and the managed user repository. The reconciliations across this mapping generate the audit records that will be directed to the MySQL database. The connection to the MySQL database is through a ScriptedSQL implementation of the Groovy Connector Toolkit. component: pingidm version: 8.1 page_id: pingidm:samples-guide:audit-jdbc canonical_url: https://docs.pingidentity.com/pingidm/8.1/samples-guide/audit-jdbc.html keywords: ["Samples", "MySQL", "Audit"] section_ids: audit-config-files: About the configuration files external-audit-mysql-sample: Configure the MySQL database run-audit-jdbc: Run the sample --- # Direct audit information to MySQL The sample includes an external CSV file and a mapping between objects in that file and the managed user repository. The reconciliations across this mapping generate the audit records that will be directed to the MySQL database. The connection to the MySQL database is through a ScriptedSQL implementation of the Groovy Connector Toolkit. ## About the configuration files The files that demonstrate the functionality of this sample are located under `/path/to/openidm/samples/audit-jdbc/`, in the `conf/` and `data/` directories. The following files play important roles in this sample: * `conf/provisioner.openicf-auditdb.json` This file provides the configuration for the Scripted SQL implementation of the Groovy Connector. The file specifies, among other things, the connection details to the MySQL database, the connector version information, and the object types that are supported for this connection. For more information, refer to [Groovy Connector Toolkit](https://docs.pingidentity.com/openicf/connector-reference/groovy.html). * `conf/provisioner.openicf-csvfile.json` This file provides the configuration for this instance of the CSV connector. It includes, among other things, the location of the CSV file resource. * `conf/sync.json` Provides the mapping between managed users and the data set in the CSV file. * `conf/audit.json` This file configures the router as the [audit event handler](../audit-guide/audit.html#configuring-topic-handlers), and routes audit logs to a remote system, identified as `auditdb`. * `data/csvConnectorData.csv` This file contains the sample data set that will be reconciled to the managed user repository. * `data/sample_audit_db.mysql` This file sets up the schema for the MySQL database that will contain the audit logs. * `tools/*.groovy` The Groovy scripts in this directory allow the connector to perform operations on the MySQL database. ## Configure the MySQL database The sample assumes the following MySQL configuration: * The database is available on the local host. * The database listens on the standard MySQL port, 3306. * You can connect to the MySQL database, as user `root` with password `password`. Before you start this sample, MySQL must be installed and running, and must include the database required for the sample. In addition, IDM must include the connector JAR required to connect to the MySQL database. 1. Install and configure MySQL. 2. This step sets up an `audit` database with tables that correspond to the various audit events. When MySQL is up and running, import the database schema to set up the database required for the sample: ``` mysql -u root -p < /path/to/openidm/samples/audit-jdbc/data/sample_audit_db.mysql Enter password:password ``` 3. To view the tables in the audit database, use the following command: ``` mysql -u root -p Enter password:password mysql> use audit Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables_in_audit | +---------------------+ | auditaccess | | auditactivity | | auditauthentication | | auditconfig | | auditrecon | | auditsync | +---------------------+ 6 rows in set (0.00 sec) ``` 4. Download [MySQL Connector/J](https://dev.mysql.com/downloads/connector/j/), version 8.0 or later from the MySQL website. Unpack the delivery, and copy the .jar into the `openidm/bundle` directory: ``` cp mysql-connector-java-version-bin.jar /path/to/openidm/bundle/ ``` 5. Edit the `url` property in the SQL connector configuration file (`openidm/samples/audit-jdbc/conf/provisioner.openicf-auditdb.json`) to match the host and port of your MySQL instance. The default configuration is as follows: ``` "url" : "jdbc:mysql://localhost:3306/audit?serverTimezone=UTC", ``` | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | The default configuration expects SSL, *which is strongly advised in a production environment*. If you are running this in a test environment, you can bypass the SSL requirement:- Add `&useSSL=false` to the end of the `url`. - If you are running MySQL 8.0.11+, add `&allowPublicKeyRetrieval=true` to the end of the `url`. | ## Run the sample | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | Starting with IDM 8.1, the [legacy admin UI is deprecated](../release-notes/deprecated-functionality.html#legacy-admin-ui-deprecated) and is no longer bundled with IDM. New deployments should use the [Platform admin UI](../setup-guide/platform-admin-ui.html), which is the replacement for the legacy admin UI.Both UIs are available as separate downloads from the [Backstage download site](https://backstage.forgerock.com/downloads):- To install the Platform admin UI, follow the steps in [Install the Platform admin UI for standalone IDM](../setup-guide/platform-admin-ui.html). - To continue using the legacy admin UI, follow the steps in [Install the legacy admin UI](../setup-guide/legacy-admin-ui.html). | In this section, you will start IDM, then run a reconciliation between the CSV file and the managed user repository. After the reconciliation, you should be able to read the audit logs in the `audit` database on your MySQL instance. 1. [Set up DS](start-here.html#ldap-server-config) without importing any LDIF file or [select another repository](../install-guide/chap-repository.html) for the sample. 2. Prepare IDM as described in [Prepare IDM](start-here.html#preparing-openidm), then start the server with the configuration for this sample: ``` cd /path/to/openidm/ ./startup.sh -p samples/audit-jdbc ``` 3. Reconcile the two data sources. * To run the reconciliation over REST, use the following command: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request POST \ "http://localhost:8080/openidm/recon?_action=recon&mapping=systemCsvfileAccounts_managedUser&waitForCompletion=true" { "_id": "a3664c26-bf82-4100-b411-19edc248c306-7", "state": "SUCCESS" } ``` * To run the reconciliation from the admin UI, select Configure > Mappings, select the `systemCsvfileAccounts_managedUser` mapping, and then select Reconcile. 4. Inspect the tables in the `audit` database to see how the logs have been routed to that location. The following example displays the reconciliation audit logs: ``` mysql -u root -p Enter password:password... mysql> use audit;... mysql> show tables; +---------------------+ +---------------------+ | Tables_in_audit | +---------------------+ | auditaccess | | auditactivity | | auditauthentication | | auditconfig | | auditrecon | | auditsync | +---------------------+ 6 rows in set (0.00 sec) mysql> select * from auditactivity; +----+-------------+--------------------------+-------------+----------------+---------------+...+ | id | objectid | activitydate | eventname | transactionid | userid |...| +----+-------------+--------------------------+-------------+----------------+---------------+...+ | 1 | 9927b8db* | 2021-01-25T12:53:00.800Z | activity | 9927b8db* | openidm-admin |...| ``` You can inspect the other audit logs in the same way. 5. By default, the audit configuration in this sample uses the router audit handler for queries, as indicated in the following line from the `conf/audit.json` file: ```json "handlerForQueries" : "router", ``` With this configuration, when you query the audit logs over REST, the audit data is returned from the router handler (in this case the MySQL database). The following example shows how to query the activity audit log: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "http://localhost:8080/openidm/audit/activity?_queryFilter=true" { "result": [ { "_id": "9927b8db-4537-467f-a077-dbe8cab2a4c8-1187", "timestamp": "2021-01-25T12:53:00.800Z", "userId": "openidm-admin", "operation": "CREATE", "changedFields": null, "objectId": "managed/user/47527af8-f7d5-4b4b-9d8e-af45169016d4", "eventName": "activity", "trackingIds": null, "transactionId": "9927b8db-4537-467f-a077-dbe8cab2a4c8-1109", "runAs": "openidm-admin", "passwordChanged": false, "message": "create", "status": "SUCCESS" }, ], ... } ``` You can query the other [audit logs](../audit-guide/querying-audit-over-rest.html) in the same way.