Direct Audit Information To MySQL

Install and configure MySQL.

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

This step sets up an audit database with six tables that correspond to the various audit events. You can view the tables in the audit database as follows:

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 5.1 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 (conf/provisioner.openicf-auditdb.json) to match the host and port of your MySQL instance. For example:

"url" : "jdbc:mysql://localhost:3306/audit?serverTimezone=UTC",

Prepare IDM as described in "Prepare IDM", then start the server with the configuration for this sample:

/path/to/openidm/startup.sh -p samples/audit-jdbc

Reconcile the two data sources, either over the command-line or by using the Admin UI.

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, 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.01 sec)
mysql> select * from auditrecon;

| id | objectid | transactionid | activitydate | eventname | userid        | ... | mapping...
|  1 | a3664... | a3664c26-b... | 2017-02-1... | recon     | openidm-admin | ... | systemCsvfile... 

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 reconciliation 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/recon?_queryFilter=true"
{
  "result": [
    {
      "_id": "a3664c26-bf82-4100-b411-19edc248c306-16",
      "action": null,
      "trackingIds": null,
      "sourceObjectId": null,
      "timestamp": "2017-02-14T10:19:30.688Z",
      "ambiguousTargetObjectIds": null,
      "reconId": "a3664c26-bf82-4100-b411-19edc248c306-7",
      "status": null,
      "eventName": "recon",
      "mapping": "systemCsvfileAccounts_managedUser",
      "targetObjectId": null,
      "situation": null,
      "userId": "openidm-admin",
      "transactionId": "a3664c26-bf82-4100-b411-19edc248c306-7",
      "message": "Reconciliation initiated by openidm-admin",
      "reconAction": "recon",
      "messageDetail": null,
      "entryType": "start",
      "linkQualifier": null,
      "reconciling": null,
      "exception": null
    },
    ...
}

You can query the other audit logs in the same way.