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

Install and configure MySQL.
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
```

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)

Download MySQL 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/
```

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

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.

Prepare IDM as described in Prepare IDM, then start the server with the configuration for this sample:
```
cd /path/to/openidm/
./startup.sh -p samples/audit-jdbc
```

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.

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.

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:

"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 in the same way.

PingIDM 7.5.0

Direct audit information to MySQL

About the configuration files

Configure the MySQL database

Run the sample