edit

Detailed Logging

Detailed logging (aka "logging") allows you to maintain a full history of all changes made to everything in CiviCRM. It's a powerful feature which is turned off be default — because it's a bit overkill for most organizations, and it comes with some downsides too.

Screenshot of report showing field differences within one update

How logging works

  • Logging adds many new tables to your database — one new table for most existing CiviCRM tables in the database, each with a log_ prefix (cache tables are ignored).

  • Optionally (and recommended), you can choose to store the log tables in a separate MySQL database.

  • The log tables have some extra columns log_user_id, log_conn_id, log_action, to describe which user performed the action, the MySQL connection ID, and the action that was taken.

  • The log tables will begin by storing the initial values of your CiviCRM tables. Then each time a row in a CiviCRM table is added, deleted, or changed, the logging system will add a new row the appropriate log table to fully describe the change.

  • The logging system adds 3 triggers on every civicrm_ table to handle updating records, inserting records, and deleting records.

  • Logging will not destroy or change your existing data.

System requirements

  • MySQL Storage Engines - By Default CiviCRM uses the Archive storage engine to store the logging data. The Archive engine may not automatically be installed on your MySQL system. Depending on your installation you maybe able to install the Archive Engine. However if you cannot install the Archive engine, the best solution would be to look to install the nz.co.fuzion.innodbtriggers Extension.

  • MySQL database permissions - Logging requires the usage of MySQL Triggers, so your MySQL database user will need to have the correct permissions to use them. In most cases, you will need to grant the Trigger Privilege. However, depending on your system or if you have enabled binary logging, you may need the Super Privilege.

Note

Previously, logging was not compatible with multilingual installations. As of 4.7, it should be compatible, but it's still experimental.

Turning on logging

  1. Make a complete backup of your database, just in case anything goes wrong.

  2. If you wish to store your log tables in a separate MySQL database, then:

    1. Create the new database.

    2. Edit your civicrm.settings.php file to add information for the new logging database there, under CIVICRM_LOGGING_DSN. If you create a new database called mydatabase_civicrm_logging the line in civicrm.settings.php might look similar to this:

      define(
         'CIVICRM_LOGGING_DSN' ,
         'mysql://user:pass@localhost/db_name?new_link=true'
      );
      
  3. Go to Administer › System Settings - Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.)

  4. Set Logging to Yes.

Turning off logging

  1. Go to Administer › System Settings - Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.)

  2. Set Logging to No.

If you want to turn on logging again, after it's been off for some time, you can do so without losing any of the previous log data. New log data will simply be appended to the log tables.

Inspecting small changes

  1. View a contact record that has been altered after logging was enabled.

  2. Go to the Change Log tab to view a table of changes.

  3. For one update, click Update to view the fields that were updated.

Reporting on multiple changes

Reports in CiviCRM Core

  1. Go to Administer > CiviReport > Create New Report From Template.

  2. With logging turned on, two new report templates are available:

    • Contact Logging Report (Summary)
    • Contact Logging Report (Detail)
  3. Use these report templates to create reports, as needed. See CiviReport for more info.

Extensions

  • The Extended Logging Report offers some additional features over the built in reports which are designed to find out about individual transactions and don't cope with Batch or long running transactions. The extended logging report allows you to view all email address changes made this month (for example) or to see the changes made by an import job.